How to turn AUTO_SHRINK off

31 August,2016 by Jack Vamvas

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]
GO
ALTER DATABASE [mydatabase] SET AUTO_SHRINK OFF WITH NO_WAIT
GO

 

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: Jack Vamvas (http://www.sqlserver-dba.com)


Share:

Verify your Comment

Previewing your Comment

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

Working...
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.

Working...

Post a comment on How to turn AUTO_SHRINK off


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer