Wednesday, May 8, 2019

SQL Server Database - Is Auto_Close Enabled?

Should Auto_Close be ON for any of your databases?  In short, no.  Having the Auto_Close option ON = increased overhead.  Or, per MSFT, "...this option can cause performance degradation on frequently accessed databases because of the increased overhead of opening and closing the database after each connection."  

In my book that means turn it off.  Here's a quick query to show you which databases have Auto_Close ON:


       -- is auto_close enabled
       SELECT
              name [Database],
              CASE WHEN is_auto_close_on  = 0 THEN 'No'
                      WHEN is_auto_close_on = 1 THEN 'Yes' END AutoCloseEnabled
       FROM sys.databases




And this is what you use to set it OFF:

       -- set auto_close off

       ALTER DATABASE ReportServer
       SET AUTO_CLOSE OFF


Take a look at both of these for more details:
     Set the AUTO_CLOSE Database Option to OFF