Log shipping monitor (primary)

Job details

Parameters

Job Summary

• Purpose: The purpose of this job is to monitor the primary database in each log shipping configuration, focusing on the details about the last backup file and last restored file.
• Why: This job is crucial because it ensures the health and functionality of the log shipping setup, which is vital for disaster recovery and maintaining high availability in SQL Server environments. Ensuring that the log shipping is occurring as expected prevents potential data loss and system downtime.
• Manual checking: You can check this manually in the database by issuing the following SQL commands:

SELECT primary_server, primary_database, last_backup_date, last_backup_file, backup_threshold FROM msdb..log_shipping_monitor_primary ORDER BY last_backup_date DESC;

Details of Implementation

This monitoring job for dbWatch Control Center is configured specifically for SQL Server instances (post 2005 version) that include the database engine. The job checks if there are any primary databases in a log shipping configuration that have failed to complete a log backup within a specified threshold time. By iterating through these databases, the job assesses if the last backup time exceeds the predefined threshold and flags these instances accordingly.

Here’s a breakdown of how it’s set up:

• Version: Implementation uses version 1.2 of the monitoring system, maintained by dbwatch.no.
• Schedule: The job is scheduled to run every 10 minutes as per the default setup.
• Dependencies: It has a self-dependency to ensure the procedure can access consistent and isolated environment settings.
• Acknowledgements: Acknowledgements are required, which suggests that user intervention or confirmation might be needed post alerts.

Implementation Logic in SQL Procedure:

• The procedure “dbw_log_ship_mon_pri” begins by declaring necessary variables for holding execution details, database names, server names, backup threshold, and dates.
• A critical section of the procedure involves filtering out databases that are to be ignored during the monitoring as specified by the “ignore databases” parameter.
• A cursor is utilized to loop through each applicable primary database from the log_shipping_monitor_primary table. For every database, the procedure calculates the time elapsed since the last backup and compares it with the threshold.
• If the last backup occurred later than the threshold, the system flags this instance and updates the monitoring system with detailed information which includes how overdue the backup is, either in minutes or hours.
• If no log shipping is implemented on the instance, or all configurations are within the threshold, relevant messages are recorded.
• Error handling is robust, catching and recording any execution errors, and ensuring all resources are appropriately cleaned up (e.g., temporary tables are dropped, cursors are deallocated).

Reporting Template

The report generated by this job provides a succinct overview of log shipping statuses across monitored SQL Server instances, detailing:

• Server
• Database
• Last backup date and time
• Backup file information
• Backup threshold in minutes

This allows database administrators to quickly assess the state of log shipping across their environment and address any potential issues promptly.