transitcenter
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
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
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.
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' )
Run the shrink w/o specifying a parameter, allowing SQL to move the pages around to use less total space:
DBCC SHRINKDATABASE ( 'DBNAME' )
ASKER
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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.