Sql-server – Reclaiming data file free space after deleting a large amount of data

sql-server-2008-r2

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.

Best Answer

I recommend you use DBCC SHRINKFILE(). You can check the available space available by file using the query in the answer at this link How to determine used/free space within SQL database files?

If you only have one data file and one log file it'll probably just be file #1 and file #2. You have to pass the file number to the SHRINKFILE command. It's not necessary, but I recommend you do it in small batches (maybe 1 or 10 GB at a time). So if you have a data file that is 250 GB but the data is only 100 GB, do the following

DBCC SHRINKFILE(1, 240000)
GO
DBCC SHRINKFILE(1, 230000)
GO
DBCC SHRINKFILE(1, 220000)
GO
...

I would leave a certain amount of space free so don't go all the way down to 100 GB. Leave maybe 10 or 20 GB free for work operations within the file.

Shrink file should not block. It usually gets suspended if other operations are working on the data file if you are shrinking a large chunk the batch may take a very long time to complete.

If part of the unused space is from the log file (file #2 typically) then you can just shrink it down in one shot after it's been backed up (if you're in FULL recovery mode). Use DBCC SQLPERF(logspace) to check the log space used and the % used. If the % used is low then you can shrink it with no problem. Just use DBCC SHRINKFILE(2, 500) to shrink the log back down to a 500 MB file (pick whatever size you think is best for regular operational use)

Related Question