Shrink SQL Server data file, but not all at once?

Solution 1:

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.

Solution 2:

We have approached several options in our environment:

1. If old tables can be faced-out, create new tables on a brand new filegroup and default the database to it. Overtime, drop the old tables until the previous filegroup is empty. Then drop it.
2. If old tables cannot be faced-out but contain historical data that can be offlined for several hours, create a new empty table pointing to a new filegroup. Swap the tables and start copying over rows from the old table to the new one in batches. This can cause fragmentation though.
3. Issue a DBCC SHRINKFILE with EMTPYFILE option and the DB will move all the objects to the new file. Then you can drop the old file. This can take a long time though.
4. Recreate the clustered index (primary key with DROP_EXISTING) of all tables into the new filegroup. This will lock the table though.

Good Luck