Subscribe to RSS feed  Follow @jackvamvas - Twitter

*Use the Comments section for questions Links


SQL Server DBA Jobs

SQL auto close - Keep it turned off

04 June,2013 by Tom Collins

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 

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

 Method 3



To disable Auto Close



Read More 

SQL Server Performance Checklist - SQL Server DBA

SQL Server – How to Protect against problems

SQL Cachestore flush - SQL Server DBA

Author: Tom Collins (


Verify your Comment

Previewing your Comment

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

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.


Post a comment on SQL auto close - Keep it turned off | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer