MyTechMantra.com
Connect With MyTechMantra.com

Like Us on Facebook    Follow Us on Twitter     Subscribe to our Feeds     Subscribe to NewsLetter

































SQL Server Best Practice Auto Close Database Option Should Remain OFF

Read Comments   |   Related Topics: SQL Server Security Best Practices > Next Topic: Database Backup & Restore Tips


Introduction

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.

/* 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
SQL Server Best Practice Auto Close Database Option Should Remain OFF

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.


How to Disable Auto Close Database Option in SQL Server Using SSMS

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
GO

Learn More...







Last Updated On: July 18, 2015



Share this Article



Receive Free SQL Server Tips and Keep Learning
Get Free SQL Server Tips




Please leave your Valuable Comment or Let us know how this article helped you: