question

sqlLearner 1 avatar image
sqlLearner 1 asked

Recover Disk Space After Delete

This is related to my question a few days ago "Recover Space After Table Delete". I have deleted millions of Records from a specific table and now am looking to reclaim the disk space. My plan was to run a dbcc ShrinkDatbase() command but should I be running something else instead? This particular table exists on multiple databases on this instance. Should I use shrinkfile instead to shrink the mdf and ldf?
tsqlshrink-databasesqlserver 2008shrink
3 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Can I ask you why you would want to shrink the MDF file? Please read Paul's blog post before you shrink the MDF file -- http://www.sqlskills.com/blogs/paul/why-you-should-not-shrink-your-data-files/
1 Like 1 ·
I need to shrink the mdf because these logging records were inserted by a bug in code. It's nearly 30 million records in some databases and there are multiple databases effected on the specific instance and want to reclaim the disk space so that we can add more databases to the instance.
0 Likes 0 ·
The same table was affected in all the databases in the instance, if that makes a difference.
0 Likes 0 ·

1 Answer

· Write an Answer
Grant Fritchey avatar image
Grant Fritchey answered
You know that shrinking files or databases can lead to serious fragmentation at the disk level. That said, assuming we're talking about a one time recovery from an emergency event, a single shrink operation is not the end of the world. I would, where possible, use the SHRINKFILE command instead of the SHRINKDATABASE command. It's more targeted and controlled so that you only shrink the things that need to be and not stuff that shouldn't be touched.
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.