Subscriptions state for Publisher

Microsoft.SQLServer.Replication.Windows.Monitor.PublisherSubscriptionsMonitor (UnitMonitor)

This monitor checks whether there are any inactive subscriptions for any publications.

Knowledge Base article:

Summary

Subscriptions can be deactivated or can expire if they are not synchronized within a specified retention period. The actions that occurs depend on the type of replication and the retention period that is exceeded.

Causes

The Subscription has not synchronized successfully within the retention period.

Transactional:

If a subscription is not synchronized within the maximum distribution retention period (default of 72 hours), and there are changes in the distribution database that have not been delivered to the Subscriber, the subscription will be marked as deactivated by the Distribution clean up job that runs on the Distributor. The subscription must be reinitialized.

Merge:

If a publication has a retention period of 14 days, a subscription can expire if it has not synchronized within 14 days. If the Publisher is running SQL Server 2005 or a later version and the agent for the subscription is from SQL Server 2005 or a later version, a subscription only expires if there have been no changes to the data in that subscription's partition. For example, suppose a Subscriber receives customer data only for customers in Germany. If the retention period is set to 14 days, the subscription expires on day 14 only if there have been changes to the German customer data in the last 14 days.

Resolutions

Check if the Distribution/Merge agent has failed with an error. Check the Job history and get more details.

For subscriptions that have become inactive, check the Msrepl_errors table in the Distribution database for any errors. Fix the error and re-initialize the Subscription.

If no errors exist for transactional replication check the MSdistribution_history table for what caused of the subscription to become inactive. If no errors exist for Merge replication, check the msmerge_history table for what caused the Subscription to become inactive.

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.	900
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

Element properties:

Target

Microsoft.SQLServer.Replication.Windows.Publisher

Parent Monitor

System.Health.ConfigurationState

Category

PerformanceHealth

Enabled

True

Alert Generate

True

Alert Severity

Warning

Alert Priority

Normal

Alert Auto Resolve

True

Monitor Type

Microsoft.SQLServer.Replication.Windows.MonitorType.PublisherSubscriptionsMonitor

Remotable

True

Accessibility

Public

Alert Message

MSSQL on Windows Replication: Inactive subscription(s) detected for Publisher.

The Publisher (Name: '{0}', Server: '{1}') has detected inactive subscription(s).
Information:
{2}

RunAs

Microsoft.SQLServer.Core.RunAs.Monitoring

Source Code:

<UnitMonitor ID="Microsoft.SQLServer.Replication.Windows.Monitor.PublisherSubscriptionsMonitor" Accessibility="Public" Enabled="true" Target="SQLReplWD!Microsoft.SQLServer.Replication.Windows.Publisher" ParentMonitorID="Health!System.Health.ConfigurationState" Remotable="true" Priority="Normal" TypeID="Microsoft.SQLServer.Replication.Windows.MonitorType.PublisherSubscriptionsMonitor" ConfirmDelivery="false" RunAs="SqlCoreLib!Microsoft.SQLServer.Core.RunAs.Monitoring">

  <Category>PerformanceHealth</Category>

  <AlertSettings AlertMessage="Microsoft.SQLServer.Replication.Windows.Monitor.PublisherSubscriptions.AlertMessage">

    <AlertOnState>Warning</AlertOnState>

    <AutoResolve>true</AutoResolve>

    <AlertPriority>Normal</AlertPriority>

    <AlertSeverity>Warning</AlertSeverity>

    <AlertParameters>

      <AlertParameter1>$Target/Host/Property[Type='SqlCoreLib!Microsoft.SQLServer.Core.DBEngine']/InstanceName$</AlertParameter1>

      <AlertParameter2>$Target/Host/Property[Type='SqlCoreLib!Microsoft.SQLServer.Core.DBEngine']/ConnectionString$</AlertParameter2>

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

    </AlertParameters>

  </AlertSettings>

  <OperationalStates>

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

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

  </OperationalStates>

  <Configuration>

    <MachineName>$Target/Host/Property[Type='SqlCoreLib!Microsoft.SQLServer.Core.DBEngine']/MachineName$</MachineName>

    <InstanceName>$Target/Host/Property[Type='SqlCoreLib!Microsoft.SQLServer.Core.DBEngine']/InstanceName$</InstanceName>

    <SqlTimeoutSeconds>15</SqlTimeoutSeconds>

    <ConnectionString>$Target/Property[Type='MSRL!Microsoft.SQLServer.Replication.Core.GenericPublisher']/Distributor$</ConnectionString>

    <MonitoringType>$Target/Host/Property[Type="SqlDiscW!Microsoft.SQLServer.Windows.DBEngine"]/MonitoringType$</MonitoringType>

    <DistributorDbName>$Target/Property[Type='MSRL!Microsoft.SQLServer.Replication.Core.GenericPublisher']/DistributorDatabaseName$</DistributorDbName>

    <PublisherServerName>$Target/Property[Type='MSRL!Microsoft.SQLServer.Replication.Core.GenericPublisher']/ConnectionString$</PublisherServerName>

    <IntervalSeconds>900</IntervalSeconds>

    <SyncTime/>

    <TimeoutSeconds>300</TimeoutSeconds>

  </Configuration>

</UnitMonitor>