This blog is part of the blog series named SQL Server Monitoring.
Purpose
To get a quick overview of the log shipping status over time.
Sources
The feature queries the msdb DMV’s log_shipping_monitor_error_detail, log_shipping_monitor_history_detail, log_shipping_monitor_primary, log_shipping_monitor_secondary, log_shipping_primary_databases, log_shipping_secondary and log_shipping_secondary_databases.
Report
The report has 6 charts, 3 for the monitoring status, 1 for the primary status and 2 for the secondary status.
Furthermore the report has 2 tables, 1 for history messages and 1 for error messages.
Model
The model for the report uses the facts [Log shipping monitor primaries], [Log shipping monitor secondary copies], [Log shipping monitor secondary restores], [Log shipping primary databases], [Log shipping secondaries], [Log shipping secondary databases], [Log shipping monitor error details] and [Log shipping monitor history details], and the dimensions [Date], [Time], [Database] and [Server].
Configuration
There is no configuration for this feature.
Scheduling
The default schedule for the feature is every 2 minutes (120 seconds).
Retention
The default retention for the tables are:
- dbo.fhsmLogShippingMonitorErrorDetail – 90 days
- dbo.fhsmLogShippingMonitorHistoryDetail – 90 days
- dbo.fhsmLogShippingMonitorPrimary – 40 days
- dbo.fhsmLogShippingMonitorSecondary – 40 days
- dbo.fhsmLogShippingPrimaryDatabases – 40 days
- dbo.fhsmLogShippingSecondary – 40 days
- dbo.fhsmLogShippingSecondaryDatabases – 40 days
