Log shipping monitor (secondary)

Job details

Name:	Log shipping monitor (secondary)
Platform:	Sqlserver
Category:	Cluster and Replication
Description:	Monitor the secondary database in each log shipping configuration.
Long description:	Monitor the secondary database in each log shipping configuration.
Version:	1.2
Default schedule:	7,17,27,37,47,57 * * *
Requires engine install:	Yes
Compatibility tag:	.[type=‘instance’ & databasetype=‘sqlserver’]/instance[maj_version > ‘2005′ & hasengine=‘YES’ & engine_edition = ‘Microsoft SQL Server’ & eng_inst_priv = 0]

Parameters

Name	Default value	Description
ignore databases	model	List over the databases (separated by comma) which will be ignored from being checked.

Job Summary

Purpose: The job is designed to monitor the status of secondary databases in SQL Server log shipping configurations to ensure they are being updated properly.
Why: This job is crucial for maintaining the reliability and integrity of log shipping by alerting on potential delays or failures in the log restore process. It ensures business continuity by verifying the secondary database’s synchronization with the primary.
Manual checking: To check this manually, you would query the msdb..log_shipping_monitor_secondary table. Here’s an SQL command to help with that:

SELECT secondary_server, secondary_database, restore_threshold, last_restored_date FROM msdb..log_shipping_monitor_secondary;

Implementation Details

The job executes a stored procedure to monitor log shipping on secondary databases, excluding specified databases from monitoring based on a configurable list of database names.
A cursor iterates over the log shipping monitor entries, checking if the last log restore is older than the configured threshold and raising an alert if so.
Detailed execution logs are maintained by appending information on each database being monitored, with specifics on how long ago the last restore occurred.
If a database has not had a log entry restored in line with the set thresholds, the job updates the monitoring status accordingly and adds detailed information for review.

Dependencies

The job is self-dependent on the dbw_log_ship_mon_sec stored procedure, ensuring all necessary setups and cleanups are within its operational context.

Output Description

Presentation via dbWatch involves a report comprising details of each secondary server and database, last restore timings, and the configured warning thresholds. This assists in quick visual assessment for database administrators.
The data in the report is fetched and displayed in a table format showing important metrics such as the server, database, last restore, copied file information, and the warning threshold (in minutes).

Reporting Configuration

Field	Description
Server	The secondary server where the log shipping destination resides.
Database	The name of the secondary database.
Last Restore	Timestamp of the last successful log restore operation.
Copied File Info	Information about the last log file copied to the secondary server.
Warning Threshold (minutes)	Indicates the critical time frame in minutes to alert if exceeded without a restore.

The monitor uses a default schedule that triggers checks every 10 minutes, ensuring frequent and regular validations of log shipping status across all monitored SQL Server instances.