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.
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:
ALTER DATABASE [AdventureWorks] SET AUTO_CLOSE OFF;