Auto Close Enabled

Mom taught you that if you weren’t using one of your toys, you should put it away.  Some folks take this to an extreme and tell SQL Server to close databases that aren’t in use either.  We check sys.databases to see if is_auto_close_on = 1 for any of the databases.

When databases are closed, they take longer for the next user to access.

In rare cases, like hosting providers that have thousands of databases on each SQL Server instance, it does make sense to close files that aren’t in use to improve performance.

But that’s not common. For most people this just flushes the data cache and execution plans out of memory for that database, and makes your SQL Server work harder than it has to. If you’re not absolutely sure this setting is right for you, it’s probably hurting you far more than it’s helping.

How to Disable Auto Close in SQL Server

Option 1: Use the Graphical Interface

In SQL Server Management Studio, you can right-click on each database, select “Properties”, and choose the “Options” tab. Set Auto Close to false and click OK.

OPTION 2: Use the ALTER DATABASE Command in T-SQL

You can also change this setting with a T-SQL script like the following for each database: