How to repetitively shrink a DB file

USE [YouDatabaseName]

WHILE
 (SELECT((size /128.0) - (CAST(FILEPROPERTY(name, 'SpaceUsed') AS INT) / 128.0))
 FROM sys.database_files WHERE NAME = 'YourDataFileName') <> 0

BEGIN
 DBCC SHRINKFILE (YourDataFileName, 1)
END

This will keep on shrinking the file until the free space is 0. Remember to rebuild your indexes after this as it does create some fragmentation, also remember that most databases require a bit of free space during normal operations, so I recommend doing this only if you are planning to archive the DB.

The reason for repetitive shrinking is so that when you backup the DB (which makes it nice and small) and you at some later point restore it again, you don’t restore a huge file with lots of free space that you don’t need.

So before I archive a DB, I’ll shrink it down to its smallest size (also set the recovery model to SIMPLE and get rid of your log file), and then only backup the DB.

The goal here is to save space, if you have oodles of HDD space, then don’t worry about it.

- sent by Pieter Swart