AUTO_CLOSE and the SQL Server ERRORLOG

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

 

4 thoughts on “AUTO_CLOSE and the SQL Server ERRORLOG

  1. Some of these options hark back to the days of early 1990’s (maybe even late 80’s) when servers had 16 or 32 MB of RAM. It was probably a cool feature to automatically shutdown unused databases, rather than manually do it, to free up very precious (expensive) memory resources.

    But agree – it’s a long outdated option which should never be used (other than on SQL Express style databases which may get used once a week (or two) on a home computer).

  2. I wouldn’t say that AUTO_CLOSE should never be used. Have you ever worked with many thousands of databases on a single SQL Server? It isn’t pretty. I’ve worked with clients who had 4000 databases, only about 100 of which were in use at any point in time. AUTO_CLOSE is a god-send for those systems. While you can take databases OFFLINE, it’s not as convenient as AUTO_CLOSE when you want to access them. AUTO_CLOSE spins them up automatically when a connection is made.

    The main reason that it was included early on was because SQL Server was also supported on FAT32 filesystems, and it was the default setting for the Personal Edition of SQL Server at the time. If you had a laptop with a FAT32 filesystem, you’d be glad the files weren’t left open when you weren’t using them, after the system rebooted or crashed for some reason.

    1. Greg-

      So I haven’t had a client with 4000 databases and only 100 in use – as I said (paraphrased) I haven’t a found a use case yet but that doesn’t mean there isn’t one! Thanks for providing an example 🙂 And thanks for adding in the history on AUTO_CLOSE – I had thought about including it but really wanted to focus on what people would see in the ERRORLOG if this was turned on and probably shouldn’t be (because if it’s continually re-opening and flooding the log with the recovery messages, that suggests that perhaps AUTO_CLOSE shouldn’t be enabled).

      Thanks,

      E

  3. I had the experience of supporting a database that came to us because key business users saw the application and decided to purchase without any IT input. Shortly after installation I became involved because the business users were experiencing issues. Two things I saw right away was AUTO_CLOSE and AUTO_SHRINK both set to TRUE. Upon asking the explanation was “…the vendor’s developers just did it that way. Feel free to change them if you want.” About a year later the application was abandoned. This was the first time that I had ever seen either option used.

Leave a Reply

Your email address will not be published. Required fields are marked *

Other articles

A Fond Farewell

If you haven’t guessed from the title, I’m writing this post because I am leaving SQLskills. This Friday, January 14th, is my last day, and

Explore

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.