6

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.

RLF
  • 14,035
  • 2
  • 34
  • 47
Info23
  • 163
  • 1
  • 2
  • 9

2 Answers2

8

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:

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!

Blaž Dakskobler
  • 1,075
  • 9
  • 15
6

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)

Community
  • 1
Alf47
  • 981
  • 1
  • 9
  • 22