Subscribe to RSS feed  Follow @jackvamvas - Twitter

*Use the Comments section for questions Links


SQL Server DBA Jobs

How to turn AUTO_SHRINK off

31 August,2016 by Tom Collins

I run  a daily health check across SQL Servers . One of the checks made is whether the SQL Server configurable AUTO_SHRINK is turned ON. Unless there is a clear reason why AUTO_SHRINK needs to be turned ON then keeping AUTO_SHRINK OFF can assist in  keeping contention lower.

As part of a SQL Server set up I use a  SQL Server database files configuration checklist.   One of the items on the list is AUTO_SHRINK OFF

Configuring AUTO_SHRINK to OFF – means the files are not automatically shrunk – when checks occur for unused space.  I execute an Shrink when a permanent data delete occurs. If it’s a recurring INSERT and DELETE then I don’t Shrink , preferring to leave the space available.

To turn AUTO_SHRINK OFF use this sql code as an example.


USE [master]


A SQL Server daily healthcheck can help you discover issues that potentially could cause an outage. It's a way of discovering configurations which have changed or not following the sql server standards.

To read more on how to set up a SQL Server healtcheck  read SQL Server – Daily Health Check Script with Powershell

Read More on managing database files

SQL Server - Preallocate SQL Transaction Logs for large queries – Initial Size

SQL DATA FILE AUTOGROW performance troubleshooting

SQL Server – Monitor SQL Transaction Log AutoGrowth for performance issues

Author: Tom Collins (


Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.


Post a comment on How to turn AUTO_SHRINK off | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer