Source Log Shipping

Microsoft.SQLServer.2008.Database.LogShippingSourceMonitor (UnitMonitor)

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.
Note that all Log Shipping is not supported by any edition of SQL Server Express.

Knowledge Base article:

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 (critical) state. Once the log backups resume and are within the defined thresholds, then the monitor will return to a success (healthy) state. By default, this monitor generates alerts when it is in an error state.

The monitor is disabled for SQL Server Express editions by default.

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

Log Shipping topic from SQL Server 2008 books online:

http://go.microsoft.com/fwlink/?LinkId=789160

Overrideable Parameters

Name	Description	Default Value
Alert Priority	Defines Alert Priority.	Normal
Alert Severity	Defines Alert Severity.	Error
Enabled	Enables or disables the workflow.	Yes
Generates Alerts	Defines whether the workflow generates an Alert.	Yes

Element properties:

Target

Microsoft.SQLServer.2008.Database

Parent Monitor

System.Health.PerformanceState

Category

PerformanceHealth

Enabled

True

Alert Generate

True

Alert Severity

Error

Alert Priority

Normal

Alert Auto Resolve

True

Monitor Type

Microsoft.Windows.2SingleEventLog2StateMonitorType

Remotable

True

Accessibility

Public

Alert Message

Log Shipping Source

{0}

RunAs

Default

Source Code:

<UnitMonitor ID="Microsoft.SQLServer.2008.Database.LogShippingSourceMonitor" Accessibility="Public" Enabled="true" Target="SQL2008Core!Microsoft.SQLServer.2008.Database" ParentMonitorID="SystemHealth!System.Health.PerformanceState" Remotable="true" Priority="Normal" TypeID="Windows!Microsoft.Windows.2SingleEventLog2StateMonitorType" ConfirmDelivery="true">

  <Category>PerformanceHealth</Category>

  <AlertSettings AlertMessage="Microsoft.SQLServer.2008.Database.LogShippingSourceMonitor.AlertMessage">

    <AlertOnState>Error</AlertOnState>

    <AutoResolve>true</AutoResolve>

    <AlertPriority>Normal</AlertPriority>

    <AlertSeverity>Error</AlertSeverity>

    <AlertParameters>

      <AlertParameter1>$Data/Context/EventDescription$</AlertParameter1>

    </AlertParameters>

  </AlertSettings>

  <OperationalStates>

    <OperationalState ID="SourceNotBackedUp" MonitorTypeStateID="FirstEventRaised" HealthState="Error"/>

    <OperationalState ID="SourceBackUpComplete" MonitorTypeStateID="SecondEventRaised" HealthState="Success"/>

  </OperationalStates>

  <Configuration>

    <FirstComputerName>$Target/Host/Host/Property[Type="Windows!Microsoft.Windows.Computer"]/NetworkName$</FirstComputerName>

    <FirstLogName>Application</FirstLogName>

    <FirstExpression>

      <And>

        <Expression>

          <SimpleExpression>

            <ValueExpression>

              <XPathQuery Type="UnsignedInteger">EventDisplayNumber</XPathQuery>

            </ValueExpression>

            <Operator>Equal</Operator>

            <ValueExpression>

              <Value Type="UnsignedInteger">14420</Value>

            </ValueExpression>

          </SimpleExpression>

        </Expression>

        <Expression>

          <SimpleExpression>

            <ValueExpression>

              <XPathQuery Type="String">PublisherName</XPathQuery>

            </ValueExpression>

            <Operator>Equal</Operator>

            <ValueExpression>

              <Value Type="String">$Target/Host/Property[Type="SQL!Microsoft.SQLServer.DBEngine"]/ServiceName$</Value>

            </ValueExpression>

          </SimpleExpression>

        </Expression>

        <Expression>

          <RegExExpression>

            <ValueExpression>

              <XPathQuery>EventDescription</XPathQuery>

            </ValueExpression>

            <Operator>ContainsSubstring</Operator>

            <Pattern>.$Target/Property[Type="SQL!Microsoft.SQLServer.Database"]/DatabaseName$ </Pattern>

          </RegExExpression>

        </Expression>

      </And>

    </FirstExpression>

    <SecondComputerName>$Target/Host/Host/Property[Type="Windows!Microsoft.Windows.Computer"]/NetworkName$</SecondComputerName>

    <SecondLogName>Application</SecondLogName>

    <SecondExpression>

      <And>

        <Expression>

          <SimpleExpression>

            <ValueExpression>

              <XPathQuery Type="UnsignedInteger">EventDisplayNumber</XPathQuery>

            </ValueExpression>

            <Operator>Equal</Operator>

            <ValueExpression>

              <Value Type="UnsignedInteger">18265</Value>

            </ValueExpression>

          </SimpleExpression>

        </Expression>

        <Expression>

          <SimpleExpression>

            <ValueExpression>

              <XPathQuery Type="String">PublisherName</XPathQuery>

            </ValueExpression>

            <Operator>Equal</Operator>

            <ValueExpression>

              <Value Type="String">$Target/Host/Property[Type="SQL!Microsoft.SQLServer.DBEngine"]/ServiceName$</Value>

            </ValueExpression>

          </SimpleExpression>

        </Expression>

        <Expression>

          <RegExExpression>

            <ValueExpression>

              <XPathQuery>EventDescription</XPathQuery>

            </ValueExpression>

            <Operator>ContainsSubstring</Operator>

            <Pattern>: $Target/Property[Type="SQL!Microsoft.SQLServer.Database"]/DatabaseName$,</Pattern>

          </RegExExpression>

        </Expression>

      </And>

    </SecondExpression>

  </Configuration>

</UnitMonitor>