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 DMVs sys.databases and sys.database_scoped_configurations which returns data for the databases on the instance.
Report
The report has 2 tables where the upper one are showing the databases on the instance, as well a few but critical and important settings, and where the lower table are showing the database scoped configurations.
When right-clicking on a row there is the possibility to go to the drill-through page “Database configuration history”.
The following settings well be highlighted with green, yellow or red background depending on their settings:
| Setting | Description | Background color |
|---|---|---|
| State | Databases not online | Yellow |
| Standby | Databases in standby mode | Yellow |
| Read only | Databases in read only mode | Yellow |
| 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 |
| Always On group | Database not on primary | Yellow |
| 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 |
History – drill-through page
The Database configuration history page list all the configuration changes made on the database in question.
Model
The model for the report uses the facts [Database scoped configurations], [Database state] and [Database state history] and the dimensions [Database] and [Database scoped configuration].
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).
