Pending Commands on Distributor

Microsoft.SQLServer.2016.Replication.Monitor.DistributionDBPendingCmds (UnitMonitor)

There are pending commands on the Distributor waiting for delivery.

Knowledge Base article:

Summary

The commands in the distribution database that have not been delivered to the selected Subscriber can be viewed via Undistributed Commands tab on the replication monitor with estimated time to deliver those commands.

Causes

The distribution agent may be stopped.

Increased replication volume may be causing performance issues.

The distribution agent may have experienced performance issues while reading from the distribution database and/or writing to the Subscriber database.

Resolutions

Restart the Distribution 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 performance issues reading from the Distribution database, run update statistics with fullscan against the MSrepl_commands table in the Distribution database. If the performance issues appear when writing to the Subscriber, check for and address any blockings or resources waits.

Additional

Test if changing the parameter values below can help the distribution agent performance:

CommitBatchSize

The number of transactions to be issued to the Subscriber before a COMMIT statement is issued. The default is 100.

QueryTimeOut

The number of seconds before the query times out. The default is 1800 seconds.

SubscriptionStreams

The number of connections allowed per Distribution Agent to apply batches of changes in parallel to a Subscriber. For a SQL Server Publisher, a range of values from 1 to 64 is supported

External

http://msdn.microsoft.com/library/ms183374.aspx

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	5
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:

Target

Microsoft.SQLServer.2016.Replication.Distributor

Parent Monitor

System.Health.PerformanceState

Category

PerformanceHealth

Enabled

True

Alert Generate

True

Alert Severity

Warning

Alert Priority

Normal

Alert Auto Resolve

True

Monitor Type

Microsoft.SQLServer.2016.Replication.MonitorType.DistributorPendingCmds

Remotable

True

Accessibility

Public

Alert Message

MSSQL2016 Replication: A high number of commands in the Distribution database are waiting to be applied.

The Distributor (Name: '{0}', Server: '{1}') has detected a high number of commands waiting to be applied.
Number of commands: {2}
A word of caution about how to interpret this monitor:
Since SQL Server Replication is a store and forward mechanism, it is expected that some commands are sitting in the Distribution database as they are about to be replicated. It starts becoming an issue when the number of non-replicated commands and/or the time it takes to replicate them is too much with regard to your business requirements. The interpretation of a given number of commands is all relative to your expectations. There is no right or wrong number.

RunAs

Microsoft.SQLServer.Replication.Monitoring.RunAs.Monitor

Source Code:

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

  <Category>PerformanceHealth</Category>

  <AlertSettings AlertMessage="Microsoft.SQLServer.2016.Replication.Monitor.DistributorPendingCmds.AlertMessage">

    <AlertOnState>Warning</AlertOnState>

    <AutoResolve>true</AutoResolve>

    <AlertPriority>Normal</AlertPriority>

    <AlertSeverity>Warning</AlertSeverity>

    <AlertParameters>

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

      <AlertParameter2>$Target/Property[Type='MSRL!Microsoft.SQLServer.Replication.Library.GenericDistributor']/ConnectionString$</AlertParameter2>

      <AlertParameter3>$Data/Context/Property[@Name='DistributorPendingCmds']$</AlertParameter3>

      <AlertParameter4>$Data/Context/Property[@Name='Message']$</AlertParameter4>

    </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.GenericDistributor']/ConnectionString$</ConnectionString>

    <Threshold>5</Threshold>

    <NumSamples>6</NumSamples>

    <IntervalSeconds>300</IntervalSeconds>

    <SyncTime/>

    <TimeoutSeconds>300</TimeoutSeconds>

  </Configuration>

</UnitMonitor>