SQL Server shrink: Best Practices.

DBCC Shrink ran recently

What is database file shrinking in SQL Server?

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

By the way, this check is a part of our SQL Server Health Check service.

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.
Figure 1 – Auto shrink option in SQL Server.

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, explicitly grow LDF to keep the LDF VLF count low.

More information

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

Let our expert SQL Server Consulting team handle the technical complexities, so you can focus on driving your business forward with confidence.

Mark Varnas

Mark Varnas

Hey I'm Mark, one of the guys behind Red9. I make a living performance tuning SQL Servers and making them more stable. I channel my SQL into our SQL Managed Services, SQL Consulting and our internal database products.

Leave a Reply

Your email address will not be published. Required fields are marked *