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...
>
|||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...
>
|||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!