• Management Pack:  SQL Server
  • MP Version:  6.1 for SQL Server 2008
  • Released:  8/5/2010
  • Publisher:  Microsoft

Source Log Shipping Monitor

  • ID:  Microsoft.SQLServer.2008.Database.LogShippingSourceMonitor
  • Description:  This monitor detects when a log shipping source has not had its logs backed up within the threshold defined as a part of the log shipping configuration.
  • Target:  SQL 2008 DB
  • Enabled:  Yes

Operational States

Name State Description
Log Shipping Source Not Backed Up Error  
Log Shipping Source Back Up Complete Success  

Alert Details

Monitor State Message Priority Severity Auto Resolution
Log Shipping Source Not Backed Up (Error) Log Shipping Source Medium Critical Yes

Run As Profiles

Name
Default

Monitor Knowledgebase

Summary

This monitor detects when a log shipping source has not had its logs backed up within the threshold defined as a part of the log shipping configuration. When this condition occurs the monitor will change to an error (red) state. Once the log backups resume and are within the defined thresholds then the monitor will return to a success (green) state. By default this monitor generates alerts when it is in an error state.

Causes

Log shipping is out of synchronization beyond the backup threshold. The backup threshold is the number of minutes that are allowed to elapse between log-shipping backup jobs before an alert is generated. This message does necessarily indicate a problem with log shipping. Instead, this message might indicate one of the following problems:

  • The backup job is not running. Possible causes for this include the following: the SQL Server Agent service on the primary server instance is not running, the job is disabled, or the job's schedule has been changed.

  • The backup job is failing. Possible causes for this include the following: the backup folder path is not valid, the disk is full, or any other reason that the BACKUP statement could fail.

Resolutions

To troubleshoot this issue:

  • Make sure that the SQL Server Agent service is running for the primary server instance and that the backup job for this primary database is enabled and is scheduled to run at the appropriate frequency.

  • The backup job on the primary server might be failing. In this case, examine the job history for the backup job to look for the cause.

  • The log shipping backup job, which runs on the primary server instance, might not be able to connect to the monitor server instance to update the log_shipping_monitor_primary table. This could be caused by an authentication problem between the monitor server instance and the primary server instance.

  • The backup alert threshold might have an incorrect value. Ideally, this value is set to at least three times the frequency of the backup job. If you change the frequency of the backup job after log shipping is configured and functional, you must update the value of the backup alert threshold accordingly.

  • When the monitor server instance goes offline and then comes back online, the log_shipping_monitor_primary table is not updated with the current values before the alert message job runs. To update the monitor tables with the latest data for the primary database, run sp_refresh_log_shipping_monitor on the primary server instance.

  • On the primary or monitor server instance, the date or time is incorrect. This may also generate alert messages. Possibly the system date or time was modified on the one of them.

Note: Different time zones for the two server instances should not cause a problem.

External References

Log Shipping topic from SQL Server 2008 books online:

http://msdn.microsoft.com/en-us/library/bb895393.aspx

See Also for SQL Server Management Pack


Downloads for SQL Server Management Pack

AZURE OPTIMIZATION ASSESSMENT GET STARTED
MIGRATION TO AZURE GET STARTED
SYSTEM CENTER MIGRATION TO AZURE GET STARTED
MIGRATION TO AZURE FOR SQL AND WINDOWS 2008 GET STARTED