• Management Pack:  SQL Server 2012
  • MP Version:  7.0.4.0
  • Released:  4/18/2018
  • Publisher:  Microsoft

Auto Close Configuration Monitor

  • ID:  Microsoft.SQLServer.2012.Database.Configuration.AutoClose
  • Description:  Monitors the auto close setting for the database
  • Target:  SQL Server 2012 DB
  • Enabled:  No

Operational States

Name State Description
Auto Close Setting OK Success  
Auto Close Setting Not OK Warning  

Overridable Parameters

Parameter Name Default Value Description Override
Interval (seconds) 43200 The recurring interval of time in seconds in which to run the workflow.
Expected Value OFF To view the set of possible values, see "Configuration" section in the knowledge of this monitor.
Disable Check for SQL Express true Enables or disables check for SQL Express version.
Timeout (seconds) 300 Specifies the time the workflow is allowed to run before being closed and marked as failed.

Alert Details

Monitor State Message Priority Severity Auto Resolution
Auto Close Setting Not OK (Warning) Auto Close Configuration Error Medium Warning Yes

Run As Profiles

Name
Default

Monitor Knowledgebase

Summary

This monitor checks the Auto Close setting for this database. Since this monitor is a part of an overall standards requirement, an alert would be generated if the setting does not meet the specified standard.

The AUTO_CLOSE option is useful for desktop databases because it allows for database files to be managed as regular files. They can be moved, copied to make backups, or even e-mailed to other users. However when the database is set to AUTOCLOSE = ON, an operation that initiates an automatic database shutdown clears the plan cache for the instance of SQL Server. Clearing the plan cache causes a recompilation of all subsequent execution plans and can cause a sudden, temporary decrease in query performance

Database mirroring requires AUTO_CLOSE OFF.

Autoclose only makes sense were you are sure the application usage pattern will keep the database closed for relatively long periods. Some single-user apps and some multi-tenancy database hosting situations may benefit from AUTO_CLOSE ON. For all other usages, it is generally not a good idea unless you really understand the application behavior and usage patterns

  • Design / Implementation Note – From the BOL

  • If the database is not started, properties that the Microsoft SQL Server retrieves by accessing the database directly instead of retrieving the value from metadata will return NULL. That is, if the database has AUTO_CLOSE set to ON, or the database is otherwise offline.
Causes

A warning alert will be raised if the Auto Close option does not match the required setting. Out of the box, the monitor is configured to alert when this setting is set to β€œON”.
Resolutions

This issue may be resolved by:

  • Changing the configuration setting for this database to match the expected value.

  • Overriding the expected value for this unit monitor for this specific database or all databases.

Alternatively, if this monitor is not of concern for this database:

  • Disabling the monitor using overrides for this specific database or all databases.

  • Disabling the top-level aggregate configuration monitor using overrides for this specific database or all databases.
External References
This monitor does not contain any external references.

See Also for SQL Server 2012 Management Pack


Downloads for SQL Server 2012 Management Pack

Back to top
AZURE OPTIMIZATION ASSESSMENT GET STARTED
MIGRATION TO AZURE GET STARTED
SYSTEM CENTER MIGRATION TO AZURE GET STARTED
MIGRATION TO AZURE FOR SQL AND WINDOWS 2008 GET STARTED