I have a 9 GB MS SQL Server 2000 database that has about 1 GB of actual data in it. It is on a shared web host. I need to reduce the size to avoid paying extreme hosting charges.
I have tried using DBCC SHRINKDATABASE and DBCC SHRINKFILE. They both do not shrink the database. (And yes, I know that I shouldn't actually use them.)
DBCC SHRINKDATABASE(db_name) returns:
DbId FIleId CurrentSize MinimumSize UsedPages EstimatedPages 23 1 1114808 128 1113824 1113824 23 2 63 63 56 56
DBCC SHRINKFILE(1) returns:
DbId FIleId CurrentSize MinimumSize UsedPages EstimatedPages 23 1 1114808 128 1113824 1113824
In the general properties page, the size is listed as 8,709 MB. Space available is listed as 0 MB. In the files properties page, the main file is listed with an initial size of 8,652 MB.
DBCC SHOWCONTIG(bigTable) returns:
- Pages Scanned................................: 807 - Extents Scanned..............................: 103 - Extent Switches..............................: 102 - Avg. Pages per Extent........................: 7.8 - Scan Density [Best Count:Actual Count].......: 98.06% [101:103] - Logical Scan Fragmentation ..................: 0.37% - Extent Scan Fragmentation ...................: 1.94% - Avg. Bytes Free per Page.....................: 68.5 - Avg. Page Density (full).....................: 99.15%
Running sp_spaceused, I get this:
databasesize: 9131.94 MB
unallocated space: 377.95 MB
Running sp_spaceusused bigTable, I get this:
Rows: 56,095
Reserved: 6,419,736 KB
Data: 252,656 KB
Index_size: 4,640 KB
Unused: 6,162,440 KB
Running sp_spaceusused bigTable2, I get this:
Rows: 2,791
Reserved: 2,362,744 KB
Data: 114,232 KB
Index_size: 200 KB
Unused: 2,248,312 KB
All the rest of the tables are small.
I asked my host to back up the database, drop and recreate it with a lower initial size, and then restore it. They tried that, and this was their answer: "We followed the steps but it did not fix the issue, btw, SQL server had enlarged the database size and its current size is more than 9 GB." (It was 7GB before they attempted this.)
Is it possible my database needs the 8GB of unused space? Assuming that it doesn't, what other options do I have for shrinking it?
I was able to download a copy from my webhost to work with on my local computer. I installed the trial version of SQL Server 2008 and attached the database. I've run ALTER INDEX ALL ON BIGTABLE REORGANIZE, ALTER INDEX ALL ON BIGTABLE REBUILD, and DBCC SHRINKDATABASE. Nothing has changed. I am still getting the same basic responses from the various commands listed above.
Best Answer
What is the recovery mode of this database? Before running DBCC_SHRINKDATABASE you must backup your transaction logs. I am guessing the recovery mode is Full? Which is probably best in most cases.
How frequent are your database backups for this DB? and when was the last run transaction log backup.
you can also try DBCC SHRINKDATABASE (DatabaseName, TRUNCATEONLY);