Today I opened up a SQL Server ERRORLOG and saw these two messages repeated every 20 seconds or so:
Starting up database ‘AdventureWorks2014′.
CHECKDB for database ‘AdventureWorks2014′ finished without errors on 2015-08-23 02:15:08.070 (local time). This is an information message only; no user action required.
When you initially see these two messages repeated over and over, it might seem like SQL Server is caught in some issue with recovery. Or you might think it’s running CHECKDB over and over. Neither are true. The database has AUTO_CLOSE enabled. (And you see the CHECKDB message because it’s reading the boot page and noting the last time CHECKDB ran successfully…to see what updates that entry, check out my post What DBCC Checks Update dbccLastKnownGood?)
When AUTO_CLOSE is enabled, after the last user exits the database, the database shuts down and its resources are freed. When someone tries to access the database again, the database reopens. You might be thinking that for databases that are not accessed that often, this might be a good thing. After all, freeing resources and giving them back to SQL Server for use elsewhere sounds useful. Not so much. There’s a cost associated with that shut down, and a cost to open the database back up when a user connects. For example – shutting down a database removes all plans for that database from cache. The next time a user runs a query, it will have to be compiled. If the user disconnects, the plan is freed from cache. If someone connects one minute later and runs the same query, it has be compiled again. You get the point: this is inefficient. And really, how many databases in your production environment do you really not access? If you’re not accessing the database, why is it in a production instance? If you want a few more details on AUTO_CLOSE, check out the entry for ALTER DATABASE in Books Online.
I am sure (maybe?) that there are valid cases for having AUTO_CLOSE enabled. But I haven’t found one yet
On top of the resource use, realize that every time the database starts up, you’re going to get the above two messages in the ERRORLOG. In the log I was looking at, there were multiple databases with this option enabled, so the log was flooded with these messages. In general, I’m a huge fan of cycling the ERRORLOG on a regular basis (just set up an Agent job that runs sp_cycle_errorlog every week), and I try to reduce “clutter” in the log as much as possible. This means don’t enable a setting like AUTO_CLOSE which can put in all those messages, and use trace flag 3226 to stop logging successful backup messages (they still go to msdb).
Oh yes, to disable AUTO_CLOSE:
ALTER DATABASE 'AdventureWorks2014' SET AUTO_CLOSE OFF; GO