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

Applies to: SQL Server 2008 EnterpriseSQL Server 2008 R2 Enterprise

Summary


AUTO_CLOSE is a database option available in SQL Server. When this option is turned ON, a database will be shut down after all resources that reference this database are freed.

After a database is shutdown, next time an application attempts to use the database, the database has to be first opened and then get the status to online. This could take some time and can result in application time outs.

The AUTO_CLOSE setting can be a useful feature in some situations. When you have 32-bit SQL Server that contains a large number of databases, memory is a big constraint. Under such scenarios, it will be useful to enable AUTO_CLOSE and conserve the memory resources required to keep a database open when there is no application using the database. When the database is open, there are some default memory allocations that are required [e.g. internal structures to represent various database metadata objects, transaction log buffers, etc.].

More Information


Refer to the ALTER DATABASE books online topic for more information about this database setting.

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:

Rule softwareRule titleRule descriptionProduct versions against which the rule is evaluated 
SQL Server 2008 R2 Best Practice Analyzer (SQL Server 2008 R2 BPA





Databases have auto close 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_CLOSE 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 - Databases have auto close option enabled, then you need to follow the recommendations discussed in this article to carefully evaluate the need for AUTO_CLOSE 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)

Databases have auto close 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_CLOSE option turned ON. If you run the BPA tool and encounter a warning with the title of Database Engine - Databases have auto close option enabled, then you need to follow the recommendations discussed in this article to carefully evaluate the need for AUTO_CLOSE option for all databases on the specific instance of SQL Server.SQL Server 2012