Best Practices for SQL Server Shrink

Best Practices for SQL Server Shrink

Category: Reliability

What Is Shrinking a Database in SQL?

Shrinking data files reclaims space by moving data pages and returning that space back to the file system.

Why shouldn’t you shrink your data files?

Regularly shrinking databases (data files, to be more specific) is a bad idea.

It consumes resources and creates serious index fragmentation.
Make Sure Auto Shrink Is Disabled

Best practices recommend against enabling it, so check whether it’s disabled.

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

You can also turn off the AUTO_SHRINK database option using T-SQL, as shown below (make sure to replace “yourdb” with your database name).

ALTER DATABASE yourdb SET AUTO_SHRINK OFF

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

Sometimes You May Need to Shrink Data Files Anyway.

Only use DBCC SHRINKFILE in exceptional situations — for example, if you’ve deleted a large amount of data that won’t be added back soon, or dropped several large (unused) indexes.

In these cases (or both):

1. Identify low-usage server hours during which you can run the shrink.
2. Monitor Agent Jobs and watch for SQL Server blocking if there’s no outage window.
3. Use DBCC SHRINKFILE and set a specific, targeted size for the file you’re shrinking.
4. Once the shrink is done, use ALTER INDEX REORGANIZE to reduce index fragmentation.
5. Finally, shrink the log file, then explicitly grow it back to keep the VLF count low.