Bloated SQL Server 2000 database

Hello -- I have a database that is 102GB in size. It has been growing
expotentially every month for the past couple of years. Since we have a
bunch of similar database, we did not understand why this database was so
much larger than the other databases, considering roughly the same
kind/amount of data is imported each day.
I've run various DBCC CHECKALLOC, DBCC CHECKDB, and DBCC CHECKCATALOG
commands and DBCC DBREINDEX commands to see if I can find any problems. No
errors are being reported and recreating all indexes did not do change size.
I have since created a new database and used DTS to export/import all
data/rows from old database to new database.
I then rebuilt indexes.
The new database size is now only 2.6GB!
I still have the old database and want to determine what is messed up within
it to cause the size to be so large and continue to grow each month.
Does anyone know of anything else I can check or do to determine my space
allocation issues?
Any help or commands would be appreciated.What is the recovery mode of your old database? Is it set to full? If so,
are you backing up the transaction logs? What kind of backups do you do on
this database?
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"dm4714" <spam@.spam.net> wrote in message
news:u64sW3EPFHA.1268@.TK2MSFTNGP14.phx.gbl...
Hello -- I have a database that is 102GB in size. It has been growing
expotentially every month for the past couple of years. Since we have a
bunch of similar database, we did not understand why this database was so
much larger than the other databases, considering roughly the same
kind/amount of data is imported each day.
I've run various DBCC CHECKALLOC, DBCC CHECKDB, and DBCC CHECKCATALOG
commands and DBCC DBREINDEX commands to see if I can find any problems. No
errors are being reported and recreating all indexes did not do change size.
I have since created a new database and used DTS to export/import all
data/rows from old database to new database.
I then rebuilt indexes.
The new database size is now only 2.6GB!
I still have the old database and want to determine what is messed up within
it to cause the size to be so large and continue to grow each month.
Does anyone know of anything else I can check or do to determine my space
allocation issues?
Any help or commands would be appreciated.|||Is the 102GB data or log? Are you doing log backups? If not, make sure
you are running the Simple Recovery model. Under Full Recovery your log
will grow until you back it up.
Check the output of EXEC sp_spaceused. If the extra space is unused you
can use DBCC SHRINKFILE to free the space on disk.
--
David Portas
SQL Server MVP
--|||Sorry - I forgot to include this. The log file is set to "Simple" and it is
only 2MB in size and nothing is allocated. The data portion of the database
is 102GB.
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
news:u8dM39EPFHA.3076@.tk2msftngp13.phx.gbl...
> What is the recovery mode of your old database? Is it set to full? If so,
> are you backing up the transaction logs? What kind of backups do you do on
> this database?
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "dm4714" <spam@.spam.net> wrote in message
> news:u64sW3EPFHA.1268@.TK2MSFTNGP14.phx.gbl...
> Hello -- I have a database that is 102GB in size. It has been growing
> expotentially every month for the past couple of years. Since we have a
> bunch of similar database, we did not understand why this database was so
> much larger than the other databases, considering roughly the same
> kind/amount of data is imported each day.
> I've run various DBCC CHECKALLOC, DBCC CHECKDB, and DBCC CHECKCATALOG
> commands and DBCC DBREINDEX commands to see if I can find any problems.
> No
> errors are being reported and recreating all indexes did not do change
> size.
> I have since created a new database and used DTS to export/import all
> data/rows from old database to new database.
> I then rebuilt indexes.
> The new database size is now only 2.6GB!
> I still have the old database and want to determine what is messed up
> within
> it to cause the size to be so large and continue to grow each month.
> Does anyone know of anything else I can check or do to determine my space
> allocation issues?
> Any help or commands would be appreciated.
>
>|||Is the size in the Transaction Log?
"dm4714" <spam@.spam.net> wrote in message
news:u64sW3EPFHA.1268@.TK2MSFTNGP14.phx.gbl...
> Hello -- I have a database that is 102GB in size. It has been growing
> expotentially every month for the past couple of years. Since we have a
> bunch of similar database, we did not understand why this database was so
> much larger than the other databases, considering roughly the same
> kind/amount of data is imported each day.
> I've run various DBCC CHECKALLOC, DBCC CHECKDB, and DBCC CHECKCATALOG
> commands and DBCC DBREINDEX commands to see if I can find any problems.
> No errors are being reported and recreating all indexes did not do change
> size.
> I have since created a new database and used DTS to export/import all
> data/rows from old database to new database.
> I then rebuilt indexes.
> The new database size is now only 2.6GB!
> I still have the old database and want to determine what is messed up
> within it to cause the size to be so large and continue to grow each
> month.
> Does anyone know of anything else I can check or do to determine my space
> allocation issues?
> Any help or commands would be appreciated.
>|||dm4714 wrote:
> Sorry - I forgot to include this. The log file is set to "Simple"
> and it is only 2MB in size and nothing is allocated. The data
> portion of the database is 102GB.
Probably, you had some large tables in the database at some point which
caused the data files to auto-grow to their current size. If you run
sp_spaceused from the database, you'll see the unallocated space in the
data file. If you see large amount of unallocated space, you can shrink
the data file (off-hours) using DBCC SHRINKFILE to recover the lost
space (but leave enough to avoid auto-grow ops if possible).
--
David Gugick
Imceda Software
www.imceda.com|||It's not the transaction log.
I ran a SHOWCONTIG and it looks like it is fragmented due to a few large
tables not having a clustered index.
I'm going to create a clustered index on table so it can reorganize the data
pages and see if my size problem shrinks.
"ChrisR" <noemail@.bla.com> wrote in message
news:u%23%23goIFPFHA.4064@.TK2MSFTNGP10.phx.gbl...
> Is the size in the Transaction Log?
>
> "dm4714" <spam@.spam.net> wrote in message
> news:u64sW3EPFHA.1268@.TK2MSFTNGP14.phx.gbl...
>> Hello -- I have a database that is 102GB in size. It has been growing
>> expotentially every month for the past couple of years. Since we have a
>> bunch of similar database, we did not understand why this database was so
>> much larger than the other databases, considering roughly the same
>> kind/amount of data is imported each day.
>> I've run various DBCC CHECKALLOC, DBCC CHECKDB, and DBCC CHECKCATALOG
>> commands and DBCC DBREINDEX commands to see if I can find any problems.
>> No errors are being reported and recreating all indexes did not do change
>> size.
>> I have since created a new database and used DTS to export/import all
>> data/rows from old database to new database.
>> I then rebuilt indexes.
>> The new database size is now only 2.6GB!
>> I still have the old database and want to determine what is messed up
>> within it to cause the size to be so large and continue to grow each
>> month.
>> Does anyone know of anything else I can check or do to determine my space
>> allocation issues?
>> Any help or commands would be appreciated.
>>
>|||Before today, I was the only other person I ever saw have this problem. I
encountered it a couple years ago and during larhe inserts. Once I added the
CI, the problem went away.
"dm4714" <spam@.spam.net> wrote in message
news:%23fqL92HPFHA.3076@.TK2MSFTNGP14.phx.gbl...
> It's not the transaction log.
> I ran a SHOWCONTIG and it looks like it is fragmented due to a few large
> tables not having a clustered index.
> I'm going to create a clustered index on table so it can reorganize the
> data pages and see if my size problem shrinks.
>
> "ChrisR" <noemail@.bla.com> wrote in message
> news:u%23%23goIFPFHA.4064@.TK2MSFTNGP10.phx.gbl...
>> Is the size in the Transaction Log?
>>
>> "dm4714" <spam@.spam.net> wrote in message
>> news:u64sW3EPFHA.1268@.TK2MSFTNGP14.phx.gbl...
>> Hello -- I have a database that is 102GB in size. It has been growing
>> expotentially every month for the past couple of years. Since we have a
>> bunch of similar database, we did not understand why this database was
>> so much larger than the other databases, considering roughly the same
>> kind/amount of data is imported each day.
>> I've run various DBCC CHECKALLOC, DBCC CHECKDB, and DBCC CHECKCATALOG
>> commands and DBCC DBREINDEX commands to see if I can find any problems.
>> No errors are being reported and recreating all indexes did not do
>> change size.
>> I have since created a new database and used DTS to export/import all
>> data/rows from old database to new database.
>> I then rebuilt indexes.
>> The new database size is now only 2.6GB!
>> I still have the old database and want to determine what is messed up
>> within it to cause the size to be so large and continue to grow each
>> month.
>> Does anyone know of anything else I can check or do to determine my
>> space allocation issues?
>> Any help or commands would be appreciated.
>>
>>
>|||I had a similar problem where a database was 5.4GB. I ran DBCC SHOWCONTIG and
found out that my databse was heavily fragmented. So I ran DBCC INDEXDEFRAG
on all of the tables with more than 10 pages, and then did a Shrink Database
through Enterprise Manager and now my database is 1.3GB.
I should note that all of the tables had Clustered Indexing on the primary
key, but not on the other keys (if any).
Hope this helps.
"dm4714" wrote:
> Hello -- I have a database that is 102GB in size. It has been growing
> expotentially every month for the past couple of years. Since we have a
> bunch of similar database, we did not understand why this database was so
> much larger than the other databases, considering roughly the same
> kind/amount of data is imported each day.
> I've run various DBCC CHECKALLOC, DBCC CHECKDB, and DBCC CHECKCATALOG
> commands and DBCC DBREINDEX commands to see if I can find any problems. No
> errors are being reported and recreating all indexes did not do change size.
> I have since created a new database and used DTS to export/import all
> data/rows from old database to new database.
> I then rebuilt indexes.
> The new database size is now only 2.6GB!
> I still have the old database and want to determine what is messed up within
> it to cause the size to be so large and continue to grow each month.
> Does anyone know of anything else I can check or do to determine my space
> allocation issues?
> Any help or commands would be appreciated.
>
>|||I just ran additional DBCC INDEXDEFRAG queries on tables I had missed and now
the database is less than 1GB.
I use Diskkeeper to defrag the hard drive on the server and I noteced that
the disk became heavily (58%) fragmented AFTER I ran INDEXDEFRAG and Shrink
Database.
Just thought I'd point that out in case you're concerned about disk
fragmentation.
Cheers!|||In article <1066BAB0-AFAD-4AC7-AE7F-CB9144D45C86@.microsoft.com>,
Svavar@.discussions.microsoft.com says...
> I just ran additional DBCC INDEXDEFRAG queries on tables I had missed and now
> the database is less than 1GB.
> I use Diskkeeper to defrag the hard drive on the server and I noteced that
> the disk became heavily (58%) fragmented AFTER I ran INDEXDEFRAG and Shrink
> Database.
> Just thought I'd point that out in case you're concerned about disk
> fragmentation.
Defragging a disk and a database are different things. The file used for
Data from the DB is "in-use" and the parts can't be defragged using
Diskeeper unless you stop the SQL service or detach the database.
After you defrag the database, shrink it, if you can, stop the SQL
service and then defrag the drive - if you can, do a reboot defrag with
consolidation of directories.
On our production servers where I can take them off-line I will disk-
defrag them once a month using a script to stop the sql service, defrag
the drive, then restart the sql service.
I also have stored proc's that will reindex the databases (about 20% of
the tables per week) once a week - this increases performance on heavily
updated/inserted/deleted database tables.
--
--
spam999free@.rrohio.com
remove 999 in order to email me|||There's no penalty for having free space in a database. No need to shrink regularly, which means
that you don't need to defrag the drives either:
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Leythos" <void@.nowhere.lan> wrote in message
news:MPG.1ce71ec68326289398968b@.news-server.columbus.rr.com...
> In article <1066BAB0-AFAD-4AC7-AE7F-CB9144D45C86@.microsoft.com>,
> Svavar@.discussions.microsoft.com says...
>> I just ran additional DBCC INDEXDEFRAG queries on tables I had missed and now
>> the database is less than 1GB.
>> I use Diskkeeper to defrag the hard drive on the server and I noteced that
>> the disk became heavily (58%) fragmented AFTER I ran INDEXDEFRAG and Shrink
>> Database.
>> Just thought I'd point that out in case you're concerned about disk
>> fragmentation.
> Defragging a disk and a database are different things. The file used for
> Data from the DB is "in-use" and the parts can't be defragged using
> Diskeeper unless you stop the SQL service or detach the database.
> After you defrag the database, shrink it, if you can, stop the SQL
> service and then defrag the drive - if you can, do a reboot defrag with
> consolidation of directories.
> On our production servers where I can take them off-line I will disk-
> defrag them once a month using a script to stop the sql service, defrag
> the drive, then restart the sql service.
> I also have stored proc's that will reindex the databases (about 20% of
> the tables per week) once a week - this increases performance on heavily
> updated/inserted/deleted database tables.
> --
> --
> spam999free@.rrohio.com
> remove 999 in order to email me|||In article <#LIR106UFHA.3436@.TK2MSFTNGP09.phx.gbl>,
tibor_please.no.email_karaszi@.hotmail.nomail.com says...
> There's no penalty for having free space in a database. No need to shrink regularly, which means
> that you don't need to defrag the drives either:
Actually, since drives often have more than one DB, and since head
movement is lost performance, defragging the DB file is a good idea
depending on the amount of db file fragmentation.
As for shrinking it, I don't do it as part of a maintenance plan.
As for reindexing, it's always a good idea to pay attention to your
clustered indexes and to rebuild them when the system has many changes
that impacts performance of clustered index lookups.
As for shrinking, I agree with that specific point - it doesn't help
much unless your data is spread out across a large DB with excessive
free space.
--
--
spam999free@.rrohio.com
remove 999 in order to email me|||> Actually, since drives often have more than one DB, and since head
> movement is lost performance, defragging the DB file is a good idea
> depending on the amount of db file fragmentation.
Perhaps I didn't make my standpoint clear: Yes, you want to avoid fragmentation at the file system
level, I agree with that. My point is that if you pre-allocate storage and don't autoshrink, you
will not get fragmented database files and you won't have need to do file level defrag.
> As for shrinking, I agree with that specific point - it doesn't help
> much unless your data is spread out across a large DB with excessive
> free space.
Perhaps I'm misreading you here. In what way would shrink help here? Excessive free space is no
problem at the page level. SQL Server will not read unused pages into cache. Shrinking will move
pages toward beginning of the database files. And, in fact, shrinking will negates reindexing inside
SQL server as the pages will no longer to the same level be stored physically in the same sequence
as the index (as the linked list). I.e., shrinking will increase database level fragmentation.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Leythos" <void@.nowhere.lan> wrote in message
news:MPG.1ce84447bd361ada98968e@.news-server.columbus.rr.com...
> In article <#LIR106UFHA.3436@.TK2MSFTNGP09.phx.gbl>,
> tibor_please.no.email_karaszi@.hotmail.nomail.com says...
>> There's no penalty for having free space in a database. No need to shrink regularly, which means
>> that you don't need to defrag the drives either:
> Actually, since drives often have more than one DB, and since head
> movement is lost performance, defragging the DB file is a good idea
> depending on the amount of db file fragmentation.
> As for shrinking it, I don't do it as part of a maintenance plan.
> As for reindexing, it's always a good idea to pay attention to your
> clustered indexes and to rebuild them when the system has many changes
> that impacts performance of clustered index lookups.
> As for shrinking, I agree with that specific point - it doesn't help
> much unless your data is spread out across a large DB with excessive
> free space.
> --
> --
> spam999free@.rrohio.com
> remove 999 in order to email me|||In article <eH$TpIHVFHA.3188@.TK2MSFTNGP09.phx.gbl>,
tibor_please.no.email_karaszi@.hotmail.nomail.com says...
> > Actually, since drives often have more than one DB, and since head
> > movement is lost performance, defragging the DB file is a good idea
> > depending on the amount of db file fragmentation.
> Perhaps I didn't make my standpoint clear: Yes, you want to avoid fragmentation at the file system
> level, I agree with that. My point is that if you pre-allocate storage and don't autoshrink, you
> will not get fragmented database files and you won't have need to do file level defrag.
If you allocate X+1 GB of storage space for a DB that is only X+0 GB in
size, as long as the db never grows, you should have one contiguous file
for the DB, if not it's best to defrag at least the file for optimal
performance. Many DB's have auto-grow enabled, and I've only seen a
small number of databases that had not been file level fragmented.
In most cases a DB will grow, at least all but two of them I've worked
with have grown over time. Many DBA's will shrink and other things,
which leads to it being fragmented.
I agree, if it's oversized on purpose, left alone as one file fragment,
then it's fine. I do not shrink DB's just because they use X+1 size.
> > As for shrinking, I agree with that specific point - it doesn't help
> > much unless your data is spread out across a large DB with excessive
> > free space.
> Perhaps I'm misreading you here. In what way would shrink help here? Excessive free space is no
> problem at the page level. SQL Server will not read unused pages into cache. Shrinking will move
> pages toward beginning of the database files. And, in fact, shrinking will negates reindexing inside
> SQL server as the pages will no longer to the same level be stored physically in the same sequence
> as the index (as the linked list). I.e., shrinking will increase database level fragmentation.
I agree that shrinking a DB can fragment the data inside the DB file,
but so can too little fill space for clustered indexes. Not performing
regular reindexing of the clustered indexes will also fragment the data.
I am not a proponent of shrinking the DB size unless you have a drive
space issue, I am a supporter of defragmenting the db FILE when it
fragments and reindexing the clustered indexes (as well as the non-
clustered) on a frequent basis.
I am under the impression, as far as large databases, if I set the fill
a non-optimized number based on my DB setup/use, that my data will not
be psuedo-sequential once the 'fill' space is used for a C/I area, this
means the data is pushed farther away from it's brothers, and means you
have a head-seek operation, which is a delay. While MS sets up a default
fill, it's not always optimal, and many Databases are designed by
application developers that don't know about the merits of fill or
reindexing a database.
--
--
spam999free@.rrohio.com
remove 999 in order to email me|||Seems we are in agreement, with mainly nuance differences :-). I understand that in reality, you
find database files "out there" that has too many fragments, hence defragging the files can be a
good thing.
Lets see if we can sort out some questionmarks:
> I agree that shrinking a DB can fragment the data inside the DB file,
> but so can too little fill space for clustered indexes.
I agree with the first part. As for the second part, I agree if you refer to fillfactor. Having a
large fillfactor (say 100% for instance) will cause fragmentation, depending on the modification and
insert pattern for the index in question. However, most users don't change fillfactor depending on
the file sizes they have. I.e., fillfactor doesn't adapt dynamically depending on free space in the
database. Fillfactor is something you make a conscious decision about and it will in turn determine
the amount of space you need.
> I am not a proponent of shrinking the DB size unless you have a drive
> space issue, I am a supporter of defragmenting the db FILE when it
> fragments and reindexing the clustered indexes (as well as the non-
> clustered) on a frequent basis.
No problems here.
> I am under the impression, as far as large databases, if I set the fill
> a non-optimized number based on my DB setup/use, that my data will not
> be psuedo-sequential once the 'fill' space is used for a C/I area, this
> means the data is pushed farther away from it's brothers, and means you
> have a head-seek operation, which is a delay. While MS sets up a default
> fill, it's not always optimal, and many Databases are designed by
> application developers that don't know about the merits of fill or
> reindexing a database.
Agree. My point, I guess, is that fillfactor doesn't change just because you happen to have more or
less free space in a database file.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Leythos" <void@.nowhere.lan> wrote in message
news:MPG.1ce90ef22203c4ec98969e@.news-server.columbus.rr.com...
> In article <eH$TpIHVFHA.3188@.TK2MSFTNGP09.phx.gbl>,
> tibor_please.no.email_karaszi@.hotmail.nomail.com says...
>> > Actually, since drives often have more than one DB, and since head
>> > movement is lost performance, defragging the DB file is a good idea
>> > depending on the amount of db file fragmentation.
>> Perhaps I didn't make my standpoint clear: Yes, you want to avoid fragmentation at the file
>> system
>> level, I agree with that. My point is that if you pre-allocate storage and don't autoshrink, you
>> will not get fragmented database files and you won't have need to do file level defrag.
> If you allocate X+1 GB of storage space for a DB that is only X+0 GB in
> size, as long as the db never grows, you should have one contiguous file
> for the DB, if not it's best to defrag at least the file for optimal
> performance. Many DB's have auto-grow enabled, and I've only seen a
> small number of databases that had not been file level fragmented.
> In most cases a DB will grow, at least all but two of them I've worked
> with have grown over time. Many DBA's will shrink and other things,
> which leads to it being fragmented.
> I agree, if it's oversized on purpose, left alone as one file fragment,
> then it's fine. I do not shrink DB's just because they use X+1 size.
>> > As for shrinking, I agree with that specific point - it doesn't help
>> > much unless your data is spread out across a large DB with excessive
>> > free space.
>> Perhaps I'm misreading you here. In what way would shrink help here? Excessive free space is no
>> problem at the page level. SQL Server will not read unused pages into cache. Shrinking will move
>> pages toward beginning of the database files. And, in fact, shrinking will negates reindexing
>> inside
>> SQL server as the pages will no longer to the same level be stored physically in the same
>> sequence
>> as the index (as the linked list). I.e., shrinking will increase database level fragmentation.
> I agree that shrinking a DB can fragment the data inside the DB file,
> but so can too little fill space for clustered indexes. Not performing
> regular reindexing of the clustered indexes will also fragment the data.
> I am not a proponent of shrinking the DB size unless you have a drive
> space issue, I am a supporter of defragmenting the db FILE when it
> fragments and reindexing the clustered indexes (as well as the non-
> clustered) on a frequent basis.
> I am under the impression, as far as large databases, if I set the fill
> a non-optimized number based on my DB setup/use, that my data will not
> be psuedo-sequential once the 'fill' space is used for a C/I area, this
> means the data is pushed farther away from it's brothers, and means you
> have a head-seek operation, which is a delay. While MS sets up a default
> fill, it's not always optimal, and many Databases are designed by
> application developers that don't know about the merits of fill or
> reindexing a database.
> --
> --
> spam999free@.rrohio.com
> remove 999 in order to email me