Reclaiming data file free space after deleting a large amount of data

I am an accidental DBA. We have a sql cluster that hosts 10 databases. Suddenly the size of one of the database increased drastically from 100GB to 250GB. When we checked the datafile, the size had grown more than twice over the last few days. We identified the tables and truncated the data and deleted 130GB worth of data. The datafile is still showing 250GB. How do we reclaim the space?

Thanks a lot for all your help.

First, are you sure the data growth won't happen again? If there's a realistic chance it will and the empty space doesn't hurt you, leave it, do NOT shrink the database.

However, if you're positive you want to reduce the data file size, then you should be aware of the pitfalls of data file shrinking:

• It causes massive index fragmentation.

• Affects performance WHILE it's running and AFTER it's run (one exception - the WITH TRUNCATEONLY option, if you're just removing free space at the end of the file)

I refer to the advice of Paul Randal from his article "Why you should not shrink your data files" (read the whole article to get a clear picture what happens with index fragmentation):


The method I like to recommend is as follows:

• Create a new filegroup

• Move all affected tables and indexes into the

new filegroup using the CREATE INDEX … WITH (DROP_EXISTING = ON) ON
syntax, to move the tables and remove fragmentation from them at the
same time

• Drop the old filegroup that you were going to shrink anyway

(or shrink it way down if its the primary filegroup)



Basically you
need to provision some more space before you can shrink the old files,
but it’s a much cleaner mechanism.


If you absolutely have no choice and have to run a data file shrink
operation, be aware that you’re going to cause index fragmentation and
you should take steps to remove it afterwards if it’s going to cause
performance problems. The only way to remove index fragmentation
without causing data file growth again is to use DBCC INDEXDEFRAG or
ALTER INDEX … REORGANIZE. These commands only require a single 8KB
page of extra space, instead of needing to build a whole new index in
the case of an index rebuild operation.


Bottom line – try to avoid running data file shrink at all costs!

StackExchange Database Administrators Q#69043, answer score: 8