This blog is part of the blog series named SQL Server Monitoring.
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.
Report
The report has a table showing the databases on the instance, as well a few but critical and important settings.
The following settings well be highlighted with green, yellow or red background depending on their settings:
| Setting | Description | Background color |
|---|---|---|
| Collation | Databases not configured with the same collation as the instance | Yellow |
| Comp. | Databases running at a lower compatibility level than the instance | Yellow |
| Recovery | User databases not configured to be using Full recovery model. There can be situations where it is acceptable to have a production database in Simple or Bulk Logged but these situations are rare | Yellow |
| RCSI | Databases not configured to not have RCSI enabled | Yellow |
| Page verify | Databases not configured to be using Checksum as Page verify method | Red |
| Auto close | Databases configured to be using Auto close | Red |
| Auto shrink | Databases configured to be using Auto shrink | Red |
| Auto update stats. async. | Databases configured to be using Auto update stats. async. | Yellow |
| Encrypted | Databases configured to be Encrypted | Green |
| Delayed durability | Databases configured to be using Delayed durability | Yellow |
Model
The model for the report uses the fact [Database state] and [Database state history] and the dimension [Database].
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).
