It's easy to get to your data.  It's sitting right behind this tiger.

It’s easy to get to your data. It’s sitting right behind this tiger.

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.

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.

To Fix the Problem

Pick one of the two methods below and make your change. This setting change is done per database, and does not require restarting the SQL Server instance.

Return to sp_Blitz or Ask Us Questions


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.

Set Auto-Close to False

Turn auto-close to “false” to reduce memory churn and reduce CPU 

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:

USE [master]
GO
ALTER DATABASE [AdventureWorks] SET AUTO_CLOSE OFF;
GO

 

css.php