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.
- Using SSMS, expand Databases.
- Right-click the database name and select Properties.
- 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):
- Identify low use server times that you can run the shrink.
- Monitor your Agent Jobs and watch SQL Server for blocking if you don’t have an outage window.
- Use DBCC SHRINKFILE and set a specific, targeted size for the file you’re shrinking.
- When shrink is finished, use ALTER INDEX REORGANIZE to reduce the index fragmentation.
- 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.
