How to turn AUTO_SHRINK off

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