Chetna BhallaIntroduction
In this article, 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.
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.
AUTO CLOSE is a database level setting and hence it cannot be configured at SQL Server Level. As a best practice AUTO CLOSE option should remain OFF (FALSE) for all the user databases.
How to Identify Current AUTO CLOSE Status of a SQL Server Database?
Execute the below mentioned TSQL query to identify the 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('AdventureWorks2008R2','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 = 'AdventureWorks2008R2'
GO
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. For more information, see Different States of an SQL Server Database and What does each state mean?
How to Disable AUTO CLOSE Database Option in SQL Server Using SSMS?
1. Open SQL Server Management Studio and then connect to SQL Server Instance.
2. Right click the user database and then click on Properties from the drop down menu. Under Select a Page on the left pane click on Options and then set the value as FALSE for AUTO CLOSE as shown in the snippet below.
3. Finally, click OK to save the changes.
How to Disable AUTO CLOSE option for a Database in SQL Server Using TSQL Script?
Execute the below TSQL script to disable AUTO CLOSE option for a database in SQL Server.
USE [master]
GO
ALTER DATABASE [AdventureWorks2008R2] SET AUTO_CLOSE OFF WITH NO_WAIT
GOConclusion
It is highly recommended to check all the databases in Production or a Non-Production Environment to see if there are any databases for which AUTO CLOSE database option is turned ON by mistake.
Did this article help you? Then Do Like Us on Facebook! And Signup for Our Newsletter
Related Articles
- SQL Server Security Best Practices for DBAs and Developers to Implement in Production and Non Production SQL Servers
- How to Configure and Use Transparent Data Encryption in SQL Server
- How to Configure and Use Backup Encryption in SQL Server 2014
- Different Types of SQL Server Recovery Models
- Different Types of Database Backups supported in SQL Server
- Using System Configuration Checker Identify Potential Issues before Installing SQL Server
- How to Perform FILEGROUP Backup in SQL Server a Step by Step Tutorial with Examples
- How to Perform PARTIAL Backup in SQL Server a Step by Step Tutorial with Examples
- How to Perform TAIL-LOG Backup in SQL Server a Step by Step Tutorial with Examples
Ashish Mehta
Admin