This monitor checks the status of the Replication Distribution Agent services for Subscription. Note that SQL Server Agent Windows Service is not supported by any edition of SQL Server Express. Therefore, this monitor is not applicable for SQL Server Express cases.
The Replication Distribution Agent is an executable used in Snapshot and Transactional Replication. The agent moves snapshots, and the transactions held in the distribution database tables to the destination tables on the Subscribers. Note that SQL Server Agent Windows Service is not supported by any edition of SQL Server Express. Therefore, this monitor is not applicable for SQL Server Express cases.
The Distribution agent is running under an account that does not have enough permissions.
Query timeouts.
There are Primary Key violations or “row not found” errors on the Subscriber.
The agent cannot connect to the Distribution or the Subscriber databases.
Memory exhaustion for the non-interactive desktop heap. http://support.microsoft.com/kb/949296
The Distribution Agent failed to create temporary files in 'C:\Program Files\Microsoft SQL Server\100\COM' directory (Applicable for SQL 2008 and SQL 2008 R2) http://support.microsoft.com/kb/956032
The Distribution Agent is unable to see Snapshot files
The required privilege is not held by Distribution agent.
Incorrect parameters passed to the Distribution agent command.
The process could not bulk copy into the table
Ensure the Distribution agent is running under an account that as the required permissions. See this article to identify required permissions.
If there are multiple servers involved in the Replication topology, SQL Server Agents need to run under a domain account.
The Distribution agent account needs to be a member of the [db_owner] fixed role on the Distribution and Subscriber databases.
Query is timing out.
Check for blocking on the Distributor and Subscriber, and check if the Distribution agent is blocked by something. Resolve blocking and run the agent again.
Increase the Distribution agent query timeout to a larger value or use 0 (infinite wait)
There is a Primary Key violation or “row not found” errors on the Subscriber.
Check the article properties to confirm that all Insert, update and delete commands are being replicated.
Ensure users do not have write access to the Subscriber database.
Use -skiperrors parameter of the Distribution agent to ignore these errors (use this as a temporary fix and resolve the underlying issue)
Cannot connect to the Distribution or the Subscriber database.
Ensure the Distribution agent account is a member of the [db_owner] fixed role on the Distribution Subscriber databases. Check connection from Management Studio to both the Subscriber and the Distribution databases.
On the Distributor and Publisher, enable the TCP and UDP ports used by SQL Server in the Firewall (the 1433 port is used by default).
Memory exhaustion for the non-interactive desktop heap.
Use any of the following workarounds from this KB to resolve the issue.
The distribution agent failed to create temporary files in 'C:\Program Files\Microsoft SQL Server\100\COM' directory (Applicable for SQL 2008 and SQL 2008 R2) Use this KB to resolve the issue.
The Distribution Agent is unable to see Snapshot files Use UNC paths to store the snapshot folder. Share and grant read permissions to the Distribution agent account.
Required privilege is not held by client. Follow http://msdn.microsoft.com/library/ms143504.aspx and make sure SQL Agent Service SID has the following rights:
Log on as a service (SeServiceLogonRight)
Replace a process-level token (SeAssignPrimaryTokenPrivilege)
Bypass traverse checking (SeChangeNotifyPrivilege)
Adjust memory quotas for a process (SeIncreaseQuotaPrivilege)
Incorrect parameters passed to the Distribution agent command. Check parameters passed to the Distribution agent command line.
Reduce the valued passed to the –BCPBatchSize parameter in Distribution agent job
Rerun the Distribution agent and add the parameter -OutputVerboseLevel 2. This will give more details about what statement is failing.
Initialize Subscriber using a backup.
http://technet.microsoft.com/library/ms151868.aspx
Name | Description | Default Value |
Alert Priority | Defines Alert Priority. | Normal |
Alert Severity | Defines Alert Severity. | Error |
Enabled | Enables or disables the workflow. | Yes |
Estimated Job Duration | Threshold which is being used to check the job schedule compliance | 15 |
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 |
Show Jobs with Unknown State | Include jobs with unknown state to the monitor output and alert context. Will affect health. | No |
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 |
Target | Microsoft.SQLServer.2016.Replication.Subscription | ||
Parent Monitor | System.Health.PerformanceState | ||
Category | PerformanceHealth | ||
Enabled | True | ||
Alert Generate | True | ||
Alert Severity | Error | ||
Alert Priority | Normal | ||
Alert Auto Resolve | True | ||
Monitor Type | Microsoft.SQLServer.2016.Replication.MonitorType.ReplicationAgentState | ||
Remotable | True | ||
Accessibility | Public | ||
Alert Message |
| ||
RunAs | Microsoft.SQLServer.Replication.Monitoring.RunAs.Monitor |
<UnitMonitor ID="Microsoft.SQLServer.2016.Replication.Monitor.SubscriptionDistributionAgentState" Accessibility="Public" Enabled="true" Target="MS2RD!Microsoft.SQLServer.2016.Replication.Subscription" ParentMonitorID="Health!System.Health.PerformanceState" Remotable="true" Priority="Normal" TypeID="Microsoft.SQLServer.2016.Replication.MonitorType.ReplicationAgentState" ConfirmDelivery="false" RunAs="MSRL!Microsoft.SQLServer.Replication.Monitoring.RunAs.Monitor">
<Category>PerformanceHealth</Category>
<AlertSettings AlertMessage="Microsoft.SQLServer.2016.Replication.Monitor.SubscriptionDistributionAgentState.AlertMessage">
<AlertOnState>Error</AlertOnState>
<AutoResolve>true</AutoResolve>
<AlertPriority>Normal</AlertPriority>
<AlertSeverity>Error</AlertSeverity>
<AlertParameters>
<AlertParameter1>$Data/Context/Property[@Name='Message']$</AlertParameter1>
</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>
<SqlTimeout>15</SqlTimeout>
<ConnectionString>$Target/Property[Type='MSRL!Microsoft.SQLServer.Replication.Library.GenericSubscription']/Distributor$</ConnectionString>
<XPathName>Distribution$Target/Property[Type='MSRL!Microsoft.SQLServer.Replication.Library.GenericSubscription']/Distributor$$Target/Host/Property[Type='MSRL!Microsoft.SQLServer.Replication.Library.GenericSubscriber']/ConnectionString$$Target/Property[Type='MSRL!Microsoft.SQLServer.Replication.Library.GenericSubscription']/SubscriptionName$</XPathName>
<ShowJobsWithUnknownState>false</ShowJobsWithUnknownState>
<EstimatedJobDuration>15</EstimatedJobDuration>
<IntervalSeconds>300</IntervalSeconds>
<SyncTime/>
<TimeoutSeconds>300</TimeoutSeconds>
</Configuration>
</UnitMonitor>