Destination Log Shipping

Microsoft.SQLServer.2012.Database.LogShippingDestinationMonitor (UnitMonitor)

This monitor detects when a log shipping destination has not had a log restored to it 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 destination has not had a log restored to it 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 restores 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.

Causes

Log shipping is out of synchronization beyond the restore threshold. The restore threshold is the number of minutes that can elapse between restore operations before a message is generated.

Resolutions

To troubleshoot this issue:

Make sure that the SQL Server Agent service is running for the secondary server instance, and that the restore job for this secondary database is enabled and is scheduled to run at the appropriate frequency.
The restore job on the secondary server might be failing. In this case, check the job history for the restore job to look for the cause.
The log shipping restore job, which runs on the secondary server instance, might not be able to connect to the monitor server instance to update the log_shipping_monitor_secondary table. This might be caused by an authentication problem between the monitor server instance and the secondary 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 restore job. If you change the frequency of the restore 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_secondary table is not updated with the current values before the alert message job runs. Error 14421 can be raised when a restore job succeeds with, "Could not find a log backup file that could be applied to secondary database." When this occurs, the restore time is not updated. The cause of the error in this case might be an issue with the copy job.
To update the monitor tables with the latest data for the secondary database, run sp_refresh_log_shipping_monitor on the secondary server instance.
On the secondary 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 2012 books online:

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

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.2012.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 Destination

{0}

RunAs

Default

Source Code:

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

  <Category>PerformanceHealth</Category>

  <AlertSettings AlertMessage="Microsoft.SQLServer.2012.Database.LogShippingDestinationMonitor.AlertMessage">

    <AlertOnState>Error</AlertOnState>

    <AutoResolve>true</AutoResolve>

    <AlertPriority>Normal</AlertPriority>

    <AlertSeverity>Error</AlertSeverity>

    <AlertParameters>

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

    </AlertParameters>

  </AlertSettings>

  <OperationalStates>

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

    <OperationalState ID="DestInSync" 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">14421</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">18268</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>