XML FeedsToday I woke up to a little over a hundred emails from one of my database servers letting me know that my resources were jumping around like a kangaroo. Actually, more like a boxing match with one...
In the mix of those emails I also had alerts thrown stating,
[database_name_withheld] has a status of Suspect, Cleanly Shutdown
I actively monitor the state of the database being open or closed (which also shows status of suspect, recovering etc...) I recommend the same so you catch these situations. When I read the emails, I knew exactly what the setting was that had been set to true. "Auto Close"
Why is this bad? Buck Woody (Blog | Twitter) tells us in his blog, "SQL Server Best Practices: AutoClose Should be Off"
Basically, in the case of these alerts from my database server, what Buck explained is exactly what happened. This database that was created by some vendor software had the setting of Auto Close on. The software has a service running in the background and would poll for some data every five minutes or so and then close the connection it opened. Yes, that is a good thing that it closes the connections. But, what was happening was the resources on the server were going crazy due to consumption and releasing them every five minutes. Literally over and over again it would do this. This actually caused problems with the other databases and did exactly the opposite as you would think releasing resources would do.
Luckily Auto Close is on the, "Kill" list and will be gone soon (but not soon enough). In my experience I haven’t found a use for it unless a database is opened once a month. Personally, I don’t have any databases like that but maybe they are really out there.
Final thought – If you have a database that was recently created and you find Auto Close set to True, change it to False. It is a best practice, won’t hurt anything and will prevent things like this situation from coming up. Now lets see if I can restart this Saturday morning.
More importantly, how did it get set? Someone must have been dorking around with the database because that's not something that happens on its own.
Hi Robert, I went as far as doing a test install to the point this software does the DB create. It set it by default on the creation of the database. Not sure why the developers of the company thought that was necessary. Typically just a model setting somehow set on and then they created their DB without noticing the affects. 
Ted, do you use Policy-Based Management, if so maybe it would be good to add auto close to it 
I just found out that if you detach a database and then attach it again, in some cases the auto close option is set to true even if it was set to false before detaching it. This is the case on a server on which I installed SQL Server 2008 Express for testing purposes, but on another server with SQL Server 2008 Standard edition everything works fine. 
Could be an option to check by SQLCop as well.
LTD Social Sitings
Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.