SQL Server Monitoring – Database State

Updated February 19, 2022.

Purpose

To get a quick overview of the databases on the instance and if they have configurations that should be changed.

Sources

This feature queries the DMV sys.databases which returns data for the databases on the instance.

Reports

The report page Database State has a table showing the databases on the instance, as well a few but critical and important settings.

The RCSI (Read Committed Snapshot Isolation) setting will be shown with green background if enabled.

The following settings well be shown with yellow background if not configured optimal:

SettingDescription
Comp. levelDatabases running at a lower compatibility level than the instance will be colored yellow
Recovery modelUser databases not configured to be using Full recovery model will be colored yellow.
There can be situations where it is acceptable to have a production database in Simple or Bulk Logged but these situations are rare
Page verifyDatabases not configured to be using Checksum as Page verify method will be colored red
Auto closeDatabases configured to be using Auto close will be colored red
Auto shrinkDatabases configured to be using Auto shrink will be colored red
Auto update stats. async.Databases configured to be using Auto update stats. async. will be colored yellow
Delayed durabilityDatabases configured to be using Delayed durability will be colored yellow

When hovering over the table a tooltip is showing the history regarding database configuration changes.

Model

The model for the report uses the fact [Database state] and [Database state history] and the dimension [Database].

The fact view [Database state] in the database contains all values from the DMV, but as good practice dictates it, it is only the values used that are loaded into Power BI report.

Configuration

There is no configuration for this feature.

Scheduling

The default schedule for the feature is every hour (3600 seconds).

Retention

The default retention for the table dbo.fhsmDatabaseState is 1.825 days (5 years).