Microsoft SQL Server 2012 Internals

Shrinking files and databases

I am often misquoted as saying, “You should never shrink a database,” when in fact I say, “You should never regularly perform a shrink operation.” Because of the detrimental effects of performing a shrink operation, shrinking should be performed only when absolutely necessary. For example, shrinking might be required when

• A large amount of data has been removed from a database, the database won’t be populated with replacement data, and the disk space is required for other uses.

• A transaction log file has grown uncontrollably and needs to be resized.

• A transaction log file has an excessive number of virtual log files and needs to be resized.

Performing a database shrink operation (or individual data or log file shrinks) ...