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
To disable Auto Close
ALTER DATABASE dbname SET AUTO_CLOSE OFF