There are pending commands on the Distributor for specific Subscription waiting delivery.
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.
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
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.
Name | Description | Default Value |
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 |
Timeout (seconds) | Timeout (seconds) | 300 |
Synchronization Time | Synchronization Time |
|
Threshold | Threshold | 20 |
Target | Microsoft.SQLServer.2014.Replication.Subscription | ||
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.2014.Replication.MonitorType.SubscriptionPendingCmds | ||
Remotable | True | ||
Accessibility | Public | ||
Alert Message |
| ||
RunAs | Microsoft.SQLServer.Replication.Monitoring.RunAs.Monitor |
<UnitMonitor ID="Microsoft.SQLServer.2014.Replication.Monitor.SubscriptionPendingCmdsMonitor" Accessibility="Public" Enabled="true" Target="MS2RD!Microsoft.SQLServer.2014.Replication.Subscription" ParentMonitorID="Health!System.Health.PerformanceState" Remotable="true" Priority="Normal" TypeID="Microsoft.SQLServer.2014.Replication.MonitorType.SubscriptionPendingCmds" ConfirmDelivery="false" RunAs="MSRL!Microsoft.SQLServer.Replication.Monitoring.RunAs.Monitor">
<Category>PerformanceHealth</Category>
<AlertSettings AlertMessage="Microsoft.SQLServer.2014.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>300</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/>
</Configuration>
</UnitMonitor>