Unsynchronized Subscriptions on Distributor

Microsoft.SQLServer.2014.Replication.Monitor.UnsyncSubscriptions (UnitMonitor)

This monitor detects unsynchronized subscriptions for specific Distributors.

Knowledge Base article:

Summary

This monitor detects unsynchronized subscriptions for a specific Distributor.

If the number of unsynchronized subscriptions exceeds the threshold, an alert is fired with the following format:

The Distributor (Name: '{0}', Server: '{1}') has detected {2} unsynchronized subscription(s).

Causes

The most common cause of the issue is either the Distribution agent is not running, or is experiencing performances issues.

Resolutions

Ensure the Distribution agent is running.

Run update statistics using fullscan on the tables in the Distribution database.

If any particular publication has a large amount of transactions during the period, consider re-initialization of the corresponding subscription. If a snapshot is being used to deliver the related undistributed commands, cleaning up the distribution tables will help other subscriptions.

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.	12
Per-Subscription threshold	Per-Subscription threshold	0
Synchronization Time	Synchronization Time
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
Unhealthy subscriptions count threshold	Unhealthy subscriptions count threshold	0

Element properties:

Target

Microsoft.SQLServer.2014.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.2014.Replication.MonitorType.DistributorUnsyncSubscriptions

Remotable

True

Accessibility

Public

Alert Message

MSSQL2014 Replication: Unsynchronized Subscriptions detected.

The Distributor (Name: '{0}', Server: '{1}') has detected {2} unsynchronized subscription(s).
{3}

RunAs

Microsoft.SQLServer.Replication.Monitoring.RunAs.Monitor

Source Code:

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

  <Category>PerformanceHealth</Category>

  <AlertSettings AlertMessage="Microsoft.SQLServer.2014.Replication.Monitor.DistributorUnsyncSubscriptions.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='DistributorUnsyncSubscriptions']$</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>

    <ThresholdPendingCommandForSubscription>0</ThresholdPendingCommandForSubscription>

    <ThresholdUnhealthySubscriptionsCount>0</ThresholdUnhealthySubscriptionsCount>

    <NumSamples>12</NumSamples>

    <IntervalSeconds>300</IntervalSeconds>

    <SyncTime/>

    <TimeoutSeconds>300</TimeoutSeconds>

  </Configuration>

</UnitMonitor>