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

The synchronization time specified by using a 24-hour format. May be omitted.

 

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.Replication.Windows.Publisher
Parent MonitorSystem.Health.ConfigurationState
CategoryPerformanceHealth
EnabledTrue
Alert GenerateTrue
Alert SeverityWarning
Alert PriorityNormal
Alert Auto ResolveTrue
Monitor TypeMicrosoft.SQLServer.Replication.Windows.MonitorType.PublisherSubscriptionsMonitor
RemotableTrue
AccessibilityPublic
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}
RunAsMicrosoft.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>
<NetbiosComputerName>$Target/Host/Property[Type='SqlCoreLib!Microsoft.SQLServer.Core.DBEngine']/NetbiosComputerName$</NetbiosComputerName>
<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>
<CheckMachineAndInstanceName>false</CheckMachineAndInstanceName>
</Configuration>
</UnitMonitor>