How can I remove unallocated space from a SQL Server database?

Solution 1:

Be very careful running shrink for data files, it causes index fragmentation because of the algorithm it uses (I used to own the code when at MS). This blog post I wrote contains an example script that shows what I mean Auto-shrink – turn it OFF!. Although the blog post title is about auto-shrink, manual data file shrink uses the exact same code in SQL Server and so has the same problems.

If you're only using the TRUNCATEONLY option as Kyle says, you won't do any data movement and won't cause fragmentation.

If you have indexes in the database, you have two options:

1. Run the shrink and remove index fragmentation afterwards. Don't do it using index rebuild though (as it will need to grow the database again for space for the new index). Use my old DBCC INDEXDEFRAG or it's replacement ALTER INDEX ... REORGANIZE. That only needs one data page to operate and so won't grow the database again.
2. Create another filegroup in the database, move all indexes into it using the CREATE INDEX ... WITH DROP_EXISTING syntax, and then drop the old filegroup.

Hope this helps!

Solution 2:

See DBCC SHRINKFILE

Solution 3:

SqlACID is right and look specifically at TRUNCATEONLY or target_size