SQL Server Best Practice Auto Close Database Option Should Remain OFF
Related Topics: SQL Server Security Best Practices>
Next Topic: Database Backup & Restore Tips
In this article we will take a look at why it is important 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. This 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.
/* 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'
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?
Thank you for taking your time to read
this article. Let's be Connected....
Sign-up for Our Newsletter to Get Free SQL Server Tips and News to Build your Career
Like MyTechMantra on Facebook to get updates on What's Happening in SQL Server
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.
ALTER DATABASE [AdventureWorks2008R2] SET AUTO_CLOSE OFF WITH NO_WAIT
- 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
Last Updated On: July 18, 2015
Please leave your Valuable Comment or Let us know how this article helped you: