Autoclose for Databases
Introduction
Let's start with an easy one? What is Autoclose?
Autoclose is one of the options that you can set for a database, along with autoshrink, auto create statistics, auto update statistics, etc. This option basically "closes" the database file whenever the last users disconnects from the database. The resources are freed up, but when a user connects to the server, the database is reopened.
Note that I said database, not server.
It is also an option that you should NEVER set on a production database.
Hmmmm, he said "never". As a general rule, I'm distrustful of someone who says "never". In this case, however, take my word for it. You never want to set this on a production database.
In fact, I'm struggling to find a reason why you would ever want to set this, but let's take a look at what this option does:
Normally when SQL Server boots, it opens each .mdf and .ldf file for all the databases, the databases are checked and some small amount of resources are consumed to keep track of the database files, handles, etc.
I decided to then set the database option for Northwind to autoclose (SQL Server 2000 Standard). I next checked the SQL Server error log and found that there were a bunch of entries that all said "Starting up database 'Northwind'".
Now I ran sp_who2 to verify there were no users in Northwind. Wait a minute and connect to the server with Query Analyzer. Even though I have the Object Browser open, no queries are made of Northwind until I actually select the database.
I next select the Northwind database in the drop down and re-check the errorlog in Enterprise Manager (requires a refresh). I see 4 more entries that say "Starting up database 'Northwind'".
I disconnect and recheck the errorlog, no entries. I had expected a "close" message, but none appeared. I checked the error logs and no entries were there either. I next ran Handle from SysInternals to check for file handles open. I saw all my .mdf and .ldf files open by the sqlsrvr.exe process. Reconnect and select Northwind, re-run Handle and sure enough, there are the Northwind files being help open by sqlsrvr.exe.
I repeat this a few times and verify that when I disconnect, the file handles are closed. I even open two connections and verify that the database opens on the first connection (nothing happens on the 2nd) and stays open when I close the first until I close the 2nd connection.
If you search Books Online, you will get 6 results, all of which have to do with setting or checking the option. No guidance is given to strategies for using this option. A search on Technet returns the same BOL options along with a few bugs.
One thing to note is that in the desktop edition, this option is true by default ( set in model), but false in other editions. I guess this is to save resources on a desktop. It also allows you to easily move and copy the file when the database is not in use (mentioned in Ch 4 - Pocket Admin Consultant). Course, don't know about you, but on my servers, if I move a db file (mdf, ldf), I usually have problems when I start the server backup or access the database.
This is a strange option to me and I find myself wondering why Microsoft every implemented it every time I find it set. After all, how many resources can an open database hold? Since SQL Server tends to grab a large amount of memory, it's hard to see if memory changes with this option being set. I decided to run a few experiments.
On my test server, the SQL Server process, sqlsrvr.exe, has 154,732kb in use. This is the steady state in general, with a few other things on this server. If I set Northwind to Autoclose on, then the memory usage drops to 154,672kb immediately. When I connect with QA to master, I see memory usage jump to 154,680. 8kb added for my connection, which is what I expect. I then select the "Northwind" database. Memory moves to 154,884, but when I change back to master, the memory is still in use by SQL Server. I disconnect and memory drops back to my baseline of 154,672kb. I repeat this a few times, adding some queries in there and while the memory values change (seem to fluctuate by about 20kb as a starting point), I don't see the memory usage increase when I select Northwind.
I know this isn't the most scientific test, but I don't see that many resources being used. I wonder if a large database, > 1GB, would show similar results and I hope to get some time on a production system to test this over the next few months along with some more in depth analysis, but for now, I'll repeat my advice. DO NOT SET THIS ON A PRODUCTION SYSTEM.
In addition, there were some issues in SQL Server 7. Q309255 confirms that Autoclose may cause a stack dump in SQL Server 7. The fix? Turn it off.
I did find a Q&A at http://www.microsoft.com/sql/techinfo/tips/administration/autoclose.asp that gave a mention that it is used for databases that are rarely used, but in general it should not be used. I guess. If the database isn't used much, probably isn't taking many resources and isn't worth setting this.
If you search Google, you'll find quite a few people who have recommended you avoid this option entirely. I concur and remind you to double check all your servers and shut this option down.
As always, I welcome feedback.
Steve Jones
©dkRanch.net January 2003
