Sql-server – Shrink SQL Server data file, but not all at once

database-administrationshrinksql serversql-server-2005

I have a database file that's currently 150GB, but only 75GB is being used – it's because I moved all the indexes (the other 75GB) to a new data file. I'd like to reclaim at least part of the space from this data file, but when I attempt to shrink the file, it "Executes" indefinitely, eventually being cancelled because of a network interruption or something else out of my control (after a day of running). Even using the "shrink to specific size" feature and specifying that it just trim off 10MB never seems to return – it just sits until the process is interrupted.

Is there another way that I can reclaim this space, even a little at a time?

EDIT: Somebody posted a link explaining why I shouldn't shrink my database. I understand, and I want to shrink it anyways. Disk space is at a premium on this server, and the database will not expand again into this unused space for a very long time – as I stated earlier, I migrated indexes out of the data file to free up this space, so now it's wasted.

Best Answer

No, using DBCC SHRINKFILE ('filename', target_size) is the right way to do it.

If you want to do it in "chunks", you can either set progressively smaller target sizes, or just let it run as long as you can before it gets cancelled.

A few comments:

  • Put a reasonable target size, with some margin of allowed free space. Maybe 90GB total for 75GB of data?
  • While the shrink is running, check the activity monitor to see if the SPID is being blocked. If there is an open transaction on a page at the very end of the file, then shrink won't be able to move it until that transaction commits or is rolled back.
  • Is the spid actually making progress? (The CPU and IO numbers are changing)
  • Shrink can sometimes take a very, very long time, but it should save its progress (meaning it moves 1 page at a time, and when it gets cancelled, all the completed page moves have already been done)
  • After cancelling the shrink, try doing a DBCC SHRINKFILE ('filename', TRUNCATEONLY). It should recover all the space its already freed at the end of the file (see my prior point)
  • If you get desperate, try restarting SQL in single-user mode, so you know that nothing else is working against the db at that time (obviously, this could be impossible on a prod server)
  • Once you are able to complete the shrink, make sure to do a full reindex on the database to eliminate the fragmentation that the shrink creates. This may reclaim some of the space you just freed.
  • If you still can't get the shrink to work, check out some of the discussion on this SO question. There are apparently some situations where shrinks might not progress.
Related Topic