SQL Server Articles, SQL Server Tips, SQL Server Tutorials, SQL Server Tuning, SQL Server DBA, SQL Server Basics, Training, etc - MyTechMantra.com

SQL Server Best Practice Auto Close Database Option Should Remain OFF

Introduction

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
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?

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
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

Conclusion

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

Chetna Bhalla

Chetna Bhalla

LESS ME MORE WE

Chetna Bhalla, the founder of MyTechMantra.com, believes that by sharing knowledge and building communities, we can make this world a better place to live in. Chetna is a Graduate in Social Sciences and a Masters in Human Resources and International Business. She is an alumnus of Vignana Jyothi Institute of Management, Hyderabad, India. After graduation, Chetna founded this website, which has since then become quite a favorite in the tech world. Her vision is to make this website the favorite place for seeking information on Databases and other Information Technology areas. She believes that companies which can organize and deploy their data to frame strategies are going to have a competitive edge over others. Her interest areas include Microsoft SQL Server and overall Database Management. Apart from her work, Chetna enjoys spending time with her friends, painting, gardening, playing the violin, and spending time with her son.

Newsletter Signup! Join 15,000+ Professionals




Be Social! Like & Follow Us...

Follow us

Don't be shy, get in touch. We love meeting interesting people and making new friends.

Recent SQL Server Tips

Manning