Disclaimer: Hey Guys, this post contains affiliate link to help our reader to buy best product\service for them. It helps us because we receive compensation for our time and expenses.
Introduction:
In this blog, we will take a look at why it is essential to leave AUTO CLOSE database option turned OFF for a Production or a Non-Production SQL Server Database across all versions and editions of SQL Server.
What happens when AUTO CLOSE Option is turned ON for a SQL Server Database?
When AUTO CLOSE option is turned ON (TRUE) for a SQL Server Database; SQL Server Database Engine will close the user database after its use. The database will be turned ON next time when someone wants to access the database.
When AUTO_CLOSE is set ON, this option can cause performance degradation on frequently accessed databases because of the increased overhead of opening and closing the database after each connection. AUTO_CLOSE also flushes the procedure cache after each connection.
Disadvantage when AUTO CLOSE option is turned ON
When a database is closed, SQL Server will flush the data cache and execution plans from the Server Memory. It results in queries taking longer time to execute. Hence it is not a best practice to turn AUTO CLOSE feature ON for a SQL Server Database in Production or Non-Production Environments.
How to Identify Current AUTO CLOSE Status of a SQL Server Database?
/* If the result is 1 then it means AUTO CLOSE option is TRUE or ON for the database */
SELECT DATABASEPROPERTY('TestDB','IsAutoClose')
GO
/* Alternate Method */
/* If is_auto_close_on value is 1 then it means AUTO CLOSE option is TRUE or ON for the database */
SELECT name,is_auto_close_on FROM sys.databases
WHERE is_auto_close_on = 1 AND name = 'TestDB'
GO
Very IMP Note:-
When AUTO CLOSE option is turned ON (TRUE) for a user database in SQL Server then the user database will move in and out of “In Recovery” state. At the same time you would also see Starting up Database ‘Database Name’ message getting written often to SQL Server Error Log.
This above scenarios we experienced at one of our Vietnam client when one morning their production database went into In-Recovery mode and was inaccessible that generated P1 call and production outage.
After few minutes of investigation and looking into SQL server logs we identified that its AUTO CLOSE option was turned ON (TRUE) for one of their prod database by some code deployment by app team.
We made the database offline then online and changed this property and issues got fixed.
How to Disable AUTO CLOSE option for a Database in SQL Server Using TSQL Script?
USE [master]
GO
ALTER DATABASE [TestDB] SET AUTO_CLOSE OFF WITH NO_WAIT
GO
Best Practices Recommendations by Microsoft
If a database is accessed frequently, set the AUTO_CLOSE option to OFF for the database.
ADsql-Tech Forever 