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
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.
ReplyDeleteCompletely agreed, Thomas!!
DeleteHi Rebecca,
ReplyDeleteIs 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.....
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.
ReplyDelete