Recommendations and guidelines for setting the AUTO_SHRINK database option in SQL Server

Article ID: 2160663 - View products that this article applies to.
Expand all | Collapse all

SUMMARY

AUTO_SHRINK is a database option in SQL Server. When you enable this option for a database, this database becomes eligible for shrinking by a background task. This background task evaluates all databases which satisfy the criteria for Shrinking and shrink the data or log files.

You have to carefully evaluate setting this option for the databases in a SQL Server instance. Frequent grow and shrink operations can lead to various performance problems.

  1. If multiple databases undergo frequent shrink and grow operations, then this will easily lead to file system level fragmentation.
  2. After AUTO_SHRINK successfully shrinks the data or log file, a subsequent DML or DDL operation can slow down significantly if space is required and the files need to grow.
  3. The AUTO_SHRINK background task can take up resources when there are a lot of databases that need shrinking.
  4. The AUTO_SHRINK background task will need to acquire locks and other synchronization which can conflict with other regular application activity.

Consider setting databases to a required size and pre-grow them. Leave the unused space in the database files if you think the application usage patterns will need them again. This can prevent frequent shrink and growth of the database files.

MORE INFORMATION

See the ALTER DATABASE books online topic for specific conditions under which auto shrink works.

KB article 315512 Considerations for the "autogrow" and "autoshrink" settings in SQL Server


For more information about the products or tools that automatically check for this condition on your instance of SQL Server and on the versions of the SQL Server product, see the following table:


Collapse this tableExpand this table
Rule SoftwareRule TitleRule DescriptionProduct versions against which is rule is evaluated
SQL Server 2008 R2 Best Practice Analyzer(SQL Server 2008 R2 BPA)




Database have auto shrink option enabled





The SQL Server 2008 R2 Best Practice Analyzer (SQL Server 2008 R2 BPA) provides a rule to detect situations where a specific instance of SQL Server has databases that have the AUTO_SHRINK option turned ON. The SQL Server 2008 R2 BPA supports both SQL Server 2008 and SQL Server 2008 R2. 

If you run the BPA tool and encounter a warning with the title of Database Engine - Database have auto shrink option enabled , then you need to follow the recommendations discussed in this article to carefully evaluate the need for AUTO_SHRINK option for all databases on the specific instance of SQL Server.
SQL Server 2008
SQL Server 2008 R2





SQL Server 2012 Best Practice Analyzer(SQL Server 2012 BPA)

Database have auto shrink option enabled


The SQL Server 2012 Best Practice Analyzer (SQL Server 2012 BPA) provides a rule to detect situations where a specific instance of SQL Server has databases that have the AUTO_SHRINK option turned ON. If you run the BPA tool and encounter a warning with the title of Database Engine - Database have auto shrink option enabled , then you need to follow the recommendations discussed in this article to carefully evaluate the need for AUTO_SHRINK option for all databases on the specific instance of SQL Server.SQL Server 2012





Properties

Article ID: 2160663 - Last Review: April 2, 2012 - Revision: 5.0
APPLIES TO
  • Microsoft SQL Server 2008 Enterprise
  • Microsoft SQL Server 2008 R2 Enterprise
Keywords: 
KB2160663

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com