SQL Server Shrink: Best Practices

What is database file shrinking in SQL Server?

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

Why should you not shrink your data files?

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

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 recommend disabling it, so ensure it is turned off.

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

Also, you can change the AUTO_SHRINK database option to OFF using T-SQL, as shown below (make sure to replace yourdb to your database 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.

For instance, if you had deleted a large amount of data that was not needed soon, or if you had dropped many large, unused indexes.

In those cases, or in 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 index fragmentation.
5. Finally, if necessary, shrink the log file, explicitly grow LDF to keep the VLF count low.

More information

• Microsoft – Set the AUTO_SHRINK Database Option to OFF
• Microsoft – DBCC SHRINKFILE (Transact-SQL)