Percent of Deactivated Subscriptions

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

Percent of Deactivated Subscriptions Monitor.

Knowledge Base article:

Summary

Measures the percentage of deactivated subscriptions.

The monitor does not support merge replication.

Causes

If a transactional replication 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.

Resolutions

The subscription must be reinitialized.

The retention period may need to be adjusted to allow for an increased period of time between Subscriber synchronizations. Be aware that by increasing this value, it will cause the amount of replication data retained to grow and has the potential to negatively impact replication performance.

External

Further information for subscription deactivation management can be found at the following location:

Subscription Expiration and Deactivation

http://technet.microsoft.com/library/ms151188.aspx

Overrideable Parameters

Name	Description	Default Value
Alert Priority	Defines Alert Priority.	Normal
Alert Severity	Defines Alert Severity.	Critical
Enabled	Enables or disables the workflow.	Yes
Critical Threshold	Critical Threshold.	10
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
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.	200
Timeout for database connection (seconds)	The workflow will fail and register an event if it cannot access the database during the specified period.	15
Warning Threshold	Warning Threshold.	0

Element properties:

Target

Microsoft.SQLServer.Replication.Windows.Distributor

Parent Monitor

System.Health.ConfigurationState

Category

PerformanceHealth

Enabled

True

Alert Generate

True

Alert Severity

Error

Alert Priority

Normal

Alert Auto Resolve

True

Monitor Type

Microsoft.SQLServer.Replication.Windows.MonitorType.DistributorPercentOfDeactivatedSubscriptions

Remotable

True

Accessibility

Public

Alert Message

MSSQL on Windows Replication: High percentage of deactivated subscriptions detected on the Distributor.

The Distributor (Name: '{0}', Server: '{1}') has detected a high percentage of deactivated subscriptions.
Percent of deactivated subscriptions: {2}\%
Subscriptions:
{3}

RunAs

Microsoft.SQLServer.Core.RunAs.Monitoring

Source Code:

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

  <Category>PerformanceHealth</Category>

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

    <AlertOnState>Error</AlertOnState>

    <AutoResolve>true</AutoResolve>

    <AlertPriority>Normal</AlertPriority>

    <AlertSeverity>Error</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='DistributorPercentOfDeactivatedSubscriptions']$</AlertParameter3>

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

    </AlertParameters>

  </AlertSettings>

  <OperationalStates>

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

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

    <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/Host/Property[Type='SqlCoreLib!Microsoft.SQLServer.Core.DBEngine']/ConnectionString$</ConnectionString>

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

    <MeasuredStateId>0</MeasuredStateId>

    <WarningThreshold>0</WarningThreshold>

    <ErrorThreshold>10</ErrorThreshold>

    <IntervalSeconds>300</IntervalSeconds>

    <SyncTime/>

    <TimeoutSeconds>200</TimeoutSeconds>

  </Configuration>

</UnitMonitor>