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.