Blitz Result: 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.  This part of our SQL Server sp_Blitz script checks 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.  However, if you haven’t tested this in your own environment, it’s probably not a wise idea.

To Fix the Problem

In SQL Server Management Studio, you can right-click on each database and go into its properties to turn off auto-close.  This takes effect immediately.

You can also change this setting with an ALTER DATABASE script setting AUTO_CLOSE OFF.

After the change, you should see faster query response times for databases that are infrequently accessed.

Return to sp_Blitz or Ask Us Questions