Database Status – Auto Close

Should we really care about Auto Close turned ON on your Database?
Ummm……I would say YES. It does matter to me a lot If It is turned ON for my Production Database. (I’m not saying that I’m Okay with turning ON this Setting on my Non-Prod Databases).

Before wrapping up my day in the Office i don’t know for some reason, I thought of doing a Basic scan of Databases on couple of servers which our team is supporting before leaving. Basically I was looking for “Recovery Model”, “Auto Shrink”,”Page_verify_Option”,”Auto Update Stats”. Well, i found few to fix and interestingly I saw one of the Databases showing something similar to below Screenshot as it’s current status.

hmmm….What does it mean?? Well, It’s basically saying that “Auto Close” is turned ON and since all the Connections are dropped It’ll show you the status as- “Normal/AutoClosed/Shutdown“. Once any session is opened on this database, the “Autoclosed” from above screenshot will disappear! It’s time to poke my fellow DBA why this has been enabled on his Database.

FYI If you query system catalogs for status, it simply says ONLINE even with DATABASEPROPERTYEX. what you’ve to look for is “is_auto_close_on” in sys.databases as shown below

So…Coming to the Point..Why you should concern about this is….SQL Server will be allocating and consuming few resources each and every time a database has been initialized for the first time. So if Auto Close is turned ON, your database will be Closed when the last session is terminated. When a user issues a new query or a new request hitting this Database, SQL Server has to basically Startup your Database again which leads to consuming those few resources again and this process repeats each time your DB is Auto Closed and a new connection begins….Hope i made it clear.

If you look into error logs, you’ll be seeing few instances where it says something similar to below which is an obvious and unnecessary Overhead on your SQL Server.