Link to home
Start Free TrialLog in
Avatar of transitcenter
transitcenterFlag for United States of America

asked on

MS SQL 2005 DBCC SHRINKDATABASE

I deleted a massive amount of data (6GB) from the database last night due to someone backing up massive tables in  a production environment.  I noticed this from the size of the backup increasing by 6GB.  After I truncated and dropped the tables, I performed a DBCC SHRINKDATABASE.  The new backup is 13GB, which is the expected size without the unnecessary deleted tables.  

My question is, why has the size of the backup decreased, but the size of the MDF file is still 19GB?

I performed - DBCC SHRINKDATABASE ('DBNAME',TRUNCATEONLY)

Thanks in advance!

Rob
Avatar of Scott Pletcher
Scott Pletcher
Flag of United States of America image

You need to reorg / rebuild the table(s) involved (with a strong preference for rebuild if possible after such large delete(s)).  Otherwise, the freed space is still spread out in the table, and so the total size is not necessarily reduced.
Avatar of transitcenter

ASKER

But I dropped the tables involved.  How can I rebuild tables that are dropped?
D'OH, I overlooked that.

Run the shrink w/o specifying a parameter, allowing SQL to move the pages around to use less total space:

DBCC SHRINKDATABASE ( 'DBNAME' )
Thank you! But one last question...should I provide a percent that the database should save? Also, won't this take a long time on a 13GB database?
ASKER CERTIFIED SOLUTION
Avatar of Scott Pletcher
Scott Pletcher
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thank you!
Np.

Ironically you can hurt your overall db performance by doing that shrink.  If you have key tables and indexes, you should check them -- DBCC SHOWCONTIG() -- and rebuild them if needed.