Home
  •  

Subscription Pending Commands

Microsoft.SQLServer.2008.Replication.Monitor.SubscriptionPendingCmdsMonitor (UnitMonitor)

There are pending commands on the Distributor for specific Subscription waiting delivery.

Knowledge Base article:

Summary

A large number of commands in the distribution database have not been delivered to the selected Subscriber.

The monitor collects this information and displays to the user. The data can be acquired in the same way as the Replication Monitor does.

Causes

The distribution agent may be stopped.

Large transactions with many commands committed on the Publisher database and replicated to the distribution database

The distribution agent may be experiencing performance issues reading from the distribution database or writing to the Subscriber database

Resolutions

Address any issues if the distribution agent stopped and restart the agent;

If the alert is due to large transactions in Publisher database, evaluate the estimated time of delivery. Consider re-initializing the subscription.

If the distribution agent is experiencing slow performance in reading from the distribution database, run update statistics with the fullscan option against the distribution tables. If the performance issues are experienced when writing to the Subscriber, check and address any blockings or resources waits.

Overrideable Parameters

Name

Description

Default Value

Alert Priority

Defines Alert Priority.

Normal

Alert Severity

Defines Alert Severity.

Warning

Enabled

Enables or disables the workflow.

Yes

Generates Alerts

Defines whether the workflow generates an Alert.

Yes

Interval (seconds)

The recurring interval of time in seconds in which to run the workflow.

300

Number of samples

Indicates how many times a measured value should breach a threshold before the state is changed.

6

Synchronization Time

Synchronization Time

 

Threshold

Threshold

20

Timeout (seconds)

Specifies the time the workflow is allowed to run before being closed and marked as failed.

300

Timeout for database connection (seconds)

The workflow will fail and register an event, if it cannot access the database during the specified period.

15

Element properties:

TargetMicrosoft.SQLServer.2008.Replication.Subscription
Parent MonitorSystem.Health.PerformanceState
CategoryPerformanceHealth
EnabledTrue
Alert GenerateTrue
Alert SeverityWarning
Alert PriorityNormal
Alert Auto ResolveTrue
Monitor TypeMicrosoft.SQLServer.2008.Replication.MonitorType.SubscriptionPendingCmds
RemotableTrue
AccessibilityPublic
Alert Message
MSSQL2008 Replication: High number of commands in the distribution database are waiting to be applied.
The Subscription '{0}' has detected high number of commands waiting to be applied.
Number of commands: {1}
RunAsMicrosoft.SQLServer.Replication.Monitoring.RunAs.Monitor

Source Code:

<UnitMonitor ID="Microsoft.SQLServer.2008.Replication.Monitor.SubscriptionPendingCmdsMonitor" Accessibility="Public" Enabled="true" Target="MS2RD!Microsoft.SQLServer.2008.Replication.Subscription" ParentMonitorID="Health!System.Health.PerformanceState" Remotable="true" Priority="Normal" TypeID="Microsoft.SQLServer.2008.Replication.MonitorType.SubscriptionPendingCmds" ConfirmDelivery="false" RunAs="MSRL!Microsoft.SQLServer.Replication.Monitoring.RunAs.Monitor">

  <Category>PerformanceHealth</Category>

  <AlertSettings AlertMessage="Microsoft.SQLServer.2008.Replication.Monitor.SubscriptionPendingCmds.AlertMessage">

    <AlertOnState>Warning</AlertOnState>

    <AutoResolve>true</AutoResolve>

    <AlertPriority>Normal</AlertPriority>

    <AlertSeverity>Warning</AlertSeverity>

    <AlertParameters>

      <AlertParameter1>$Target/Property[Type='MSRL!Microsoft.SQLServer.Replication.Library.GenericSubscription']/SubscriptionName$</AlertParameter1>

      <AlertParameter2>$Data/Context/Property[@Name='SubscriptionPendingCmds']$</AlertParameter2>

    </AlertParameters>

  </AlertSettings>

  <OperationalStates>

    <OperationalState ID="Health" MonitorTypeStateID="Health" HealthState="Success"/>

    <OperationalState ID="Warning" MonitorTypeStateID="Warning" HealthState="Warning"/>

  </OperationalStates>

  <Configuration>

    <SqlTimeout>15</SqlTimeout>

    <ConnectionString>$Target/Property[Type="MSRL!Microsoft.SQLServer.Replication.Library.GenericSubscription"]/Distributor$</ConnectionString>

    <SubscriptionType>$Target/Property[Type="MSRL!Microsoft.SQLServer.Replication.Library.GenericSubscription"]/Type$</SubscriptionType>

    <PublisherName>$Target/Property[Type="MSRL!Microsoft.SQLServer.Replication.Library.GenericSubscription"]/Publisher$</PublisherName>

    <Subscriber>$Target/Property[Type="MSRL!Microsoft.SQLServer.Replication.Library.GenericSubscription"]/Subscriber$</Subscriber>

    <PublisherDb>$Target/Property[Type="MSRL!Microsoft.SQLServer.Replication.Library.GenericSubscription"]/PublicationDatabase$</PublisherDb>

    <SubscriberDb>$Target/Property[Type="MSRL!Microsoft.SQLServer.Replication.Library.GenericSubscription"]/SubscriptionDatabase$</SubscriberDb>

    <Publication>$Target/Property[Type="MSRL!Microsoft.SQLServer.Replication.Library.GenericSubscription"]/PublicationName$</Publication>

    <Threshold>20</Threshold>

    <NumSamples>6</NumSamples>

    <IntervalSeconds>300</IntervalSeconds>

    <SyncTime/>

    <TimeoutSeconds>300</TimeoutSeconds>

  </Configuration>

</UnitMonitor>