Best practices for the SQL Server shrink

Category: Reliability
Item: DBCC Shrink ran recently

What is shrinking a database in SQL?

Shrinking data files recovers space by moving pages of data and returning it to the file system.

Why should you not shrink your data files?

Regularly shrinking databases (data files, to be more specific) is a terrible thing.

It drains resources and creates severe index fragmentation. You can learn more here, in this excellent article by Paul Randal.

Make sure Auto Shrink is disabled

Best practices don’t recommend you enabled it, so check if it is disabled.

1. Using SSMS, expand Databases.
2. Right-click the database name and select Properties.
3. On the Options page, set the Auto Shrink property to False, click OK.

Also, you can change the AUTO_SHRINK database option to OFF using TSQL, as shown below (make sure to replace “yourdb” to your dabase name):

ALTER DATABASE yourdb SET AUTO_SHRINK OFF

Note: SQL Server Auto Shrink feature is disabled by default on SQL Server instance databases.

Sometimes you do have to shrink data files

Use DBCC SHRINKFILE only in exceptional circumstances. If you had deleted a large amount of data that was not coming soon or you had dropped many large indexes (unused indexes).

In those cases (or both):

1. Identify low use server times that you can run the shrink.
2. Monitor your Agent Jobs and watch SQL Server for blocking if you don’t have an outage window.
3. Use DBCC SHRINKFILE and set a specific, targeted size for the file you’re shrinking.
4. When shrink is finished, use ALTER INDEX REORGANIZE to reduce the index fragmentation.
5. Finally, if necessary, shrink the log file, and then explicitly grow on it to keep the VLF count low.

More information

Paul Randal, SQLSkills – Why you should not shrink your data files.
Microsoft – Set the AUTO_SHRINK Database Option to OFF
Microsoft – DBCC SHRINKFILE (Transact-SQL)