Showing posts with label huge. Show all posts
Showing posts with label huge. Show all posts

Sunday, March 25, 2012

Database Size Blowout - I mean like HUGE!

Has anybody come across a problem where their database
file becomes enormous for no particular reason?
I have checked all user and system tables, and they are
correct. It seems that in 3 days the database has blown
out from 400MB to 18500MB.
Any ideas/suggestions would be greatly appreciated.
Thank youIs it the database or log? run:
dbcc sqlperf('logspace')
and see if it is your log file, if it is, back it up and shrink the file or
see if you have any open transactions "dbcc opentran"
HTH
Ray Higdon MCSE, MCDBA, CCNA
--
"Nathan Day" <nathand@.stanthorpe.qld.gov.au> wrote in message
news:d94c01c3f03b$e1ef7810$a101280a@.phx.gbl...
> Has anybody come across a problem where their database
> file becomes enormous for no particular reason?
> I have checked all user and system tables, and they are
> correct. It seems that in 3 days the database has blown
> out from 400MB to 18500MB.
> Any ideas/suggestions would be greatly appreciated.
> Thank you|||somebody might have pumped in huge data and db might be in full recovery
mode .. check log size, truncate log ,shrink db u shall gain yur db size
again.
i agree with ray
run dbcc sqlperf('logspace')
u'll know abt the log size and do the above mentioned steps.
backup file will be huge, if u have enough space back it up first to be on a
safer side.
Regards,
Mayur
"Nathan Day" <nathand@.stanthorpe.qld.gov.au> wrote in message
news:d94c01c3f03b$e1ef7810$a101280a@.phx.gbl...
> Has anybody come across a problem where their database
> file becomes enormous for no particular reason?
> I have checked all user and system tables, and they are
> correct. It seems that in 3 days the database has blown
> out from 400MB to 18500MB.
> Any ideas/suggestions would be greatly appreciated.
> Thank you|||I've already checked that. The log is currently using
50MB, whilst the PRIMARY database file is using 18329MB.
I've checked all the tables, and the row counts are what
they should be, back when the db was about 400MB.
Bizarre

>--Original Message--
>Is it the database or log? run:
>dbcc sqlperf('logspace')
>and see if it is your log file, if it is, back it up and
shrink the file or
>see if you have any open transactions "dbcc opentran"
>HTH
>--
>Ray Higdon MCSE, MCDBA, CCNA
>--
>"Nathan Day" <nathand@.stanthorpe.qld.gov.au> wrote in
message
>news:d94c01c3f03b$e1ef7810$a101280a@.phx.gbl...
blown
>
>.
>|||Indexes?
http://vyaskn.tripod.com/code/sp_show_huge_tables.txt
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
"Nathan Day" <nathand@.stanthorpe.qld.gov.au> wrote in message
news:f0be01c3f0f4$c4533620$a501280a@.phx.gbl...
> I've already checked that. The log is currently using
> 50MB, whilst the PRIMARY database file is using 18329MB.
> I've checked all the tables, and the row counts are what
> they should be, back when the db was about 400MB.
> Bizarre
>
> shrink the file or
> message
> blown

Database Size Blowout - I mean like HUGE!

Has anybody come across a problem where their database
file becomes enormous for no particular reason?
I have checked all user and system tables, and they are
correct. It seems that in 3 days the database has blown
out from 400MB to 18500MB.
Any ideas/suggestions would be greatly appreciated.
Thank youIs it the database or log? run:
dbcc sqlperf('logspace')
and see if it is your log file, if it is, back it up and shrink the file or
see if you have any open transactions "dbcc opentran"
HTH
--
Ray Higdon MCSE, MCDBA, CCNA
--
"Nathan Day" <nathand@.stanthorpe.qld.gov.au> wrote in message
news:d94c01c3f03b$e1ef7810$a101280a@.phx.gbl...
> Has anybody come across a problem where their database
> file becomes enormous for no particular reason?
> I have checked all user and system tables, and they are
> correct. It seems that in 3 days the database has blown
> out from 400MB to 18500MB.
> Any ideas/suggestions would be greatly appreciated.
> Thank you|||somebody might have pumped in huge data and db might be in full recovery
mode .. check log size, truncate log ,shrink db u shall gain yur db size
again.
i agree with ray
run dbcc sqlperf('logspace')
u'll know abt the log size and do the above mentioned steps.
backup file will be huge, if u have enough space back it up first to be on a
safer side.
Regards,
Mayur
"Nathan Day" <nathand@.stanthorpe.qld.gov.au> wrote in message
news:d94c01c3f03b$e1ef7810$a101280a@.phx.gbl...
> Has anybody come across a problem where their database
> file becomes enormous for no particular reason?
> I have checked all user and system tables, and they are
> correct. It seems that in 3 days the database has blown
> out from 400MB to 18500MB.
> Any ideas/suggestions would be greatly appreciated.
> Thank you|||I've already checked that. The log is currently using
50MB, whilst the PRIMARY database file is using 18329MB.
I've checked all the tables, and the row counts are what
they should be, back when the db was about 400MB.
Bizarre
>--Original Message--
>Is it the database or log? run:
>dbcc sqlperf('logspace')
>and see if it is your log file, if it is, back it up and
shrink the file or
>see if you have any open transactions "dbcc opentran"
>HTH
>--
>Ray Higdon MCSE, MCDBA, CCNA
>--
>"Nathan Day" <nathand@.stanthorpe.qld.gov.au> wrote in
message
>news:d94c01c3f03b$e1ef7810$a101280a@.phx.gbl...
>> Has anybody come across a problem where their database
>> file becomes enormous for no particular reason?
>> I have checked all user and system tables, and they are
>> correct. It seems that in 3 days the database has
blown
>> out from 400MB to 18500MB.
>> Any ideas/suggestions would be greatly appreciated.
>> Thank you
>
>.
>|||Indexes?
http://vyaskn.tripod.com/code/sp_show_huge_tables.txt
--
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
"Nathan Day" <nathand@.stanthorpe.qld.gov.au> wrote in message
news:f0be01c3f0f4$c4533620$a501280a@.phx.gbl...
> I've already checked that. The log is currently using
> 50MB, whilst the PRIMARY database file is using 18329MB.
> I've checked all the tables, and the row counts are what
> they should be, back when the db was about 400MB.
> Bizarre
>
> >--Original Message--
> >Is it the database or log? run:
> >
> >dbcc sqlperf('logspace')
> >
> >and see if it is your log file, if it is, back it up and
> shrink the file or
> >see if you have any open transactions "dbcc opentran"
> >
> >HTH
> >
> >--
> >Ray Higdon MCSE, MCDBA, CCNA
> >--
> >"Nathan Day" <nathand@.stanthorpe.qld.gov.au> wrote in
> message
> >news:d94c01c3f03b$e1ef7810$a101280a@.phx.gbl...
> >> Has anybody come across a problem where their database
> >> file becomes enormous for no particular reason?
> >>
> >> I have checked all user and system tables, and they are
> >> correct. It seems that in 3 days the database has
> blown
> >> out from 400MB to 18500MB.
> >>
> >> Any ideas/suggestions would be greatly appreciated.
> >>
> >> Thank you
> >
> >
> >.
> >

Thursday, March 8, 2012

database restore takes ages....

I have a huge database and it takes ages to restore. If
it restores on top of an existing database is it faster
than no database? Is there any mileage in creating a big
blank database before restoring?
TIA,
JBIf the database doesn't exists, then SQL Server basically performs CREATE DATABASE, then RESTORE.
For the first part, SQL Server need to initialize the pages.
If the database exists, and have the same file structure as the one you took backup of (the last
part is very important), then SQL Server can skip the CREATE DATABASE part. It will just add back
the pages to the same location as they had when you performed the backup. (It is allowed for a file
to be bigger than it was when you performed the backup.)
If the database exists, but the file layout isn't the same, you'll get an error from RESTORE. This
is because SQL server does a check first, if the file layout isn't the same, then SQL Server will
not be able to put back all pages in the same file and page address as they had when the backup was
performed. In this case, you can use the REPLACE option, which essentially means that SQL Server
executes DROP DATABASE, CREATE DATABASE and then RESTORE.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"JB" <anonymous@.discussions.microsoft.com> wrote in message
news:297601c51359$c7b48200$a401280a@.phx.gbl...
>I have a huge database and it takes ages to restore. If
> it restores on top of an existing database is it faster
> than no database? Is there any mileage in creating a big
> blank database before restoring?
> TIA,
> JB

database restore takes ages....

I have a huge database and it takes ages to restore. If
it restores on top of an existing database is it faster
than no database? Is there any mileage in creating a big
blank database before restoring?
TIA,
JB
If the database doesn't exists, then SQL Server basically performs CREATE DATABASE, then RESTORE.
For the first part, SQL Server need to initialize the pages.
If the database exists, and have the same file structure as the one you took backup of (the last
part is very important), then SQL Server can skip the CREATE DATABASE part. It will just add back
the pages to the same location as they had when you performed the backup. (It is allowed for a file
to be bigger than it was when you performed the backup.)
If the database exists, but the file layout isn't the same, you'll get an error from RESTORE. This
is because SQL server does a check first, if the file layout isn't the same, then SQL Server will
not be able to put back all pages in the same file and page address as they had when the backup was
performed. In this case, you can use the REPLACE option, which essentially means that SQL Server
executes DROP DATABASE, CREATE DATABASE and then RESTORE.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"JB" <anonymous@.discussions.microsoft.com> wrote in message
news:297601c51359$c7b48200$a401280a@.phx.gbl...
>I have a huge database and it takes ages to restore. If
> it restores on top of an existing database is it faster
> than no database? Is there any mileage in creating a big
> blank database before restoring?
> TIA,
> JB

database restore takes ages....

I have a huge database and it takes ages to restore. If
it restores on top of an existing database is it faster
than no database? Is there any mileage in creating a big
blank database before restoring?
TIA,
JBIf the database doesn't exists, then SQL Server basically performs CREATE DA
TABASE, then RESTORE.
For the first part, SQL Server need to initialize the pages.
If the database exists, and have the same file structure as the one you took
backup of (the last
part is very important), then SQL Server can skip the CREATE DATABASE part.
It will just add back
the pages to the same location as they had when you performed the backup. (I
t is allowed for a file
to be bigger than it was when you performed the backup.)
If the database exists, but the file layout isn't the same, you'll get an er
ror from RESTORE. This
is because SQL server does a check first, if the file layout isn't the same,
then SQL Server will
not be able to put back all pages in the same file and page address as they
had when the backup was
performed. In this case, you can use the REPLACE option, which essentially m
eans that SQL Server
executes DROP DATABASE, CREATE DATABASE and then RESTORE.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"JB" <anonymous@.discussions.microsoft.com> wrote in message
news:297601c51359$c7b48200$a401280a@.phx.gbl...
>I have a huge database and it takes ages to restore. If
> it restores on top of an existing database is it faster
> than no database? Is there any mileage in creating a big
> blank database before restoring?
> TIA,
> JB

Sunday, February 19, 2012

Database Problem related Clusterd data

Hello sir
We have a very huge database its around 6 lakh records
are being stored in it.
records are not being a sorted order so we checked all
record field through clustering option in Sql server.when we used
clustering records are showing in sorted order but speed of database is
very slow .
i want to know with clustering is there any implecation in database
regading performance od data.Can any one give his views to solve this
problem?and increase the performance of our database.
thanks a lot
vibha wrote:
> Hello sir
> We have a very huge database its around 6 lakh records
^^^^^^
What kind of multiplier is this? --+

> are being stored in it.
> records are not being a sorted order so we checked all
> record field through clustering option in Sql server.when we used
> clustering records are showing in sorted order but speed of database
> is very slow .
> i want to know with clustering is there any implecation in database
> regading performance od data.Can any one give his views to solve this
> problem?and increase the performance of our database.
> thanks a lot
Using a clustered index just because you want to get sorted results is not
a good idea. Now, first you need to determine why it's slow. Then you
can take countermeasures. Have a look around
http://www.sql-server-performance.com/default.asp
and especially
http://www.sql-server-performance.co...ering_2000.asp
Kind regards
robert