Auto Close – Who Needs it?

When someone has a database set to Auto Close ON, after the last database user exits, the database is shut down cleanly and SQL Server frees the resources used. While this may have had some use in the past, that is certainly no longer the case.

Auto Close set to ON will cause database down warnings in modern monitoring tools, which cannot (yet) tell the difference between an auto-closed database and one that is offline or suspect. In a production environment that leads to numerous “cry-wolf” error messages.

The best thing to do is to ensure that all of your databases are set to auto close OFF. Some people say that auto close can be advantageous in Development or testing environments. However, I find that the risks outweigh the benefits.

Auto-close is usually defaulted to OFF. However, moving databases up from free versions of SQL Server may cause AUTO_CLOSE to become set to ON.

Setting this pernicious feature off is easy:

ALTER DATABASE [database name] SET AUTO_CLOSE OFF;
GO

Save yourself time and headache and do this to every database on every instance.

I’m open to hear of a scenario where this option helps; I just cannot see one at this time.