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
              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

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


  1. This one and auto-shrink always confused me. Why are they even a thing in the GUI? This should be buried in T-SQL code so accidental DBA's can't turn them on easily.

  2. Hi Rebecca,

    Is it possible to get any form of notification, during failure of database mail services...?? So that we can figure out that database mail services are gone down and should be fixed....

    Any suggested option from you... Thanks in Advance.....

  3. Well, there are several ways of checking service state, but database mail is a little different. You could either set up a recurring Agent job that just fires an email at some frequency throughout the day -- if you don't get it, your mail is suspect. Or, you could fire the sysmail_help_status_sp procedure in msdb. It returns one of two values, STARTED or STOPPED, which will let you know whether database mail is functional.