question

sqlLearner 1 avatar image
sqlLearner 1 asked

Recover Space After Table Delete

It was discovered that a log table was logging too many records in a database and has since been corrected. I am being asked to recover the space from the millions of deleted rows. I do not want to do a shrinkdatabase, but how else can I go about this? The table will not grow as large as it was again anytime in the near future. Should I use shrinkfile?
tsqlshrink-databasesqlserver 2008
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

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

1 Answer

·
Grant Fritchey avatar image
Grant Fritchey answered
If it's a one-time event, shrinking the database, or shrinking the file, is no big deal. What happens is people get into a vicious circle of growing, shrinking, growing, shrinking which leads to massive fragmentation on the disk. Doing it one time because of a single event is fine.
2 comments
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

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

sqlLearner 1 avatar image sqlLearner 1 commented ·
Thank you yes it is a one time event. Do you recommend anything be run after the shrink?
0 Likes 0 ·
Grant Fritchey avatar image Grant Fritchey ♦♦ commented ·
Yes. Defragment the indexes by running an index rebuild. Other than that, nothing.
0 Likes 0 ·

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.