By chance, I happened to see that the autoclose feature on SQL dbs (I'm using SQL 2008) shouldn't be on and I found that all of my SQL dbs were set to have autoclose on.

I've set autoclose to off on them all (I have about 400 dbs of varying sizes on my SQL Server instance - biggest db probably 350MB), but the memory allocation on my SQL Server process is now on 1,807,252k when its usually about 1,100k.

Is this normal? Should I go back to autoclose on?

Should the system ones (eg master, model, etc) have their autoclose set to off as well? This might have already been like this, but I'm not 100% sure.


In general, everyone that gives advice on this says to never enable auto-close because it just causes unnecessary delay when a user queries the database after it's been closed, along with a spike in resource utilization when a database has to come online. And in general, that is golden advice for databases that are used on a daily basis.

However, when you get outside the theoretical perfect environment where every production database is really in production, it may make sense to enable auto-close in some instances if the following conditions are met:

  • There is a large number of databases that contain only archive data that is queried infrequently (less than once per day, for example).

  • The users are aware that there will be some delay the first time they query the database after it's been idle for a while, and they are OK with that.

Due to data retention policies requiring the data to stay around long after it's no longer used on a daily basis, and the struggle to get business units to actually decommission database they aren't using, it is certainly reasonable that auto-close might make sense in some cases. If it can lower your hardware resource requirements and still meet the users' needs, it may be something to seriously, but cautiously, consider.

As for the memory allocation difference cited, there is no way to be sure what caused the difference without more information.

|improve this answer|||||
  • I totally agree with you. I've seen environments with over 15k databases where around 5% of them were really active. A simple restart in the instance would take more than an hour. Setting autoclose "ON" was the way to go! – Danilo Braga Jul 6 '18 at 21:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.