Sqlserver-dba.com

Subscribe

SQLServer-DBA.com Links

Subscribe to newsletter

Dba_db2_button

Powered by TypePad
SQL auto close - Keep it turned off

04 June,2013 by Jack Vamvas

Should the sql auto close database option be set to True or False?

SQL Server Books Online defines   Auto Close as  “Specify whether the database shuts down cleanly and frees resources after the last user exits. Possible values are True and False. When True, the database is shut down cleanly and its resources are freed after the last user logs off”

SQL Server Books Online doesn’t give  advice on sql auto close .  It doesn’t expand on whether it’s a good or bad idea. This leads to misunderstandings amongst DBAs and system administrators  causing performance issues.

SQL Auto Close DBA notes

1)  Auto Close is set on a database level.  There are no server level configurations. Assuming the “model” database is set at Auto Close = False , then every database created on the SQL Server instance will have auto close = False.   Watch out for databases restored from other sources , which may already have Auto Close = True.  Include Auto Close as part of a SQL Server – Daily Health Check Script with Powershell

2) Resource overhead. If Auto Close is turned on , once all connections to a database are closed , the database will proceed through the Auto Close process. When a new connection is requested , data is loaded back into buffer and statements are recompiled . Why add this resource overhead to data requests?  Read more on SQL Cachestore flush

3) Auto Close on Dev servers ? .I have worked in environments where DBAs maintain Auto Close = True on Dev boxes, based on the principle of releasing resources and maximising limited resources. . I don’t think it’s worth the management overhead . If managed correctly , SQL Server is excellent at managing memory consumption  and gives accurate signals  on memory pressure.

4) Is it worth having Auto Close = True? There are probably some cases where Auto Close = True. Before making the decision consider the sql server performance impact  and test thoroughly  

How to identify the  SQL Auto Close status on a database

  

--Method 1 
 SELECT DATABASEPROPERTY(‘dbname’,’IsAutoClose’)

--Method 2 
SELECT name,is_auto_close_on  FROM sys.databases where is_auto_close_on = 1

 Method 3

Sql_auto_close

 

To disable Auto Close

 

ALTER DATABASE dbname
SET AUTO_CLOSE  OFF

Read More 

SQL Server Performance Checklist - SQL Server DBA

SQL Server – How to Protect against problems

SQL Cachestore flush - SQL Server DBA


Author: Jack Vamvas (http://www.sqlserver-dba.com)

******** *******

Enjoy this post? Enter your email address for updates on new posts:

Delivered by FeedBurner

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer