This monitor checks the state of the Distribution agents for all publications serviced by this Distributor.
One or more of the Distribution agents failed.
Some of the reasons that can explain failures of Distribution agents are as follows:
The subscription has not been initialized, and the snapshot is not available.
The Distribution agent is running under an account that does not have enough permissions.
Query timeouts.
There is a Primary Key violation or a “row not found” error on the Subscriber.
A connection cannot be made 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 a temporary file 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 the Snapshot files
Required privileges are not held by SQL Agent Service.
Incorrect parameters passed to the Distribution Agent.
The Distribution Agent is running under an account that does not have enough 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 members of the [db_owner] fixed role on the Distribution and Subscriber databases.
Query timeout.
Check for blocking on the Distributor and Subscriber, and check if the Distribution Agent is blocked. Resolve blocking and restart the agent.
Increase the Distribution Agent’s query timeout to a larger value.
There is a Primary Key violation or “row not found” errors on the Subscriber.
Check the article properties to confirm that all inserts are being replicated; update and delete commands to not skipping any.
Verify users have no write access to the Subscriber database.
Configure the Distributor Agent to use the -skiperrors parameter to ignore these errors (use this as a temporary fix and resolve the underlying issue).
A connection cannot be made to the Distribution or the Subscriber database.
The Distribution Agent account needs to be a member of the [db_owner] fixed role on the Distribution and Subscriber databases. Check that a connection can be made from SQL Server 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.
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 the workaround from this KB to resolve the issue.
The Distribution Agent is unable to read Snapshot files.
Use a UNC path to store the snapshot folder. Share the folder and grant read permissions to the Distribution agent account.
Required privileges are not held by SQL Agent Service.
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.
The process could not bulk copy into the table
Reduce the value passed to the –BCPBatchSize parameter in Distribution agent job
Rerun the Distribution agent and add parameter -OutputVerboseLevel 2. This will give more details about what statement is failing.
Only members of the sysadmin or db_owner roles can perform this operation.
If the distribution database and the Subscriber are in different domains and no trust relationship exists between the domains (specifically, the Subscriber domain does not trust the distribution domain), then a specifically named account must be used.
Look at the SQL Server agent error details on Subscriber; the Subscriber name will be the first part of the shown 'Subscription'.
On the Subscriber, check that this agent login account is a valid login.
On the Subscriber, check that the agent login account has either 'sysadmin' or 'db_owner' roles for the database being replicated.
http://technet.microsoft.com/library/ms147328.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.2014.Replication.Distributor | ||
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.2014.Replication.MonitorType.ReplicationAgentState | ||
Remotable | True | ||
Accessibility | Public | ||
Alert Message |
| ||
RunAs | Microsoft.SQLServer.Replication.Monitoring.RunAs.Monitor |
<UnitMonitor ID="Microsoft.SQLServer.2014.Replication.Monitor.DistributionAgentState" 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.ReplicationAgentState" ConfirmDelivery="false" RunAs="MSRL!Microsoft.SQLServer.Replication.Monitoring.RunAs.Monitor">
<Category>PerformanceHealth</Category>
<AlertSettings AlertMessage="Microsoft.SQLServer.2014.Replication.Monitor.DistributionAgentState.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.GenericDistributor']/ConnectionString$</ConnectionString>
<XPathName>Distribution$Target/Property[Type='MSRL!Microsoft.SQLServer.Replication.Library.GenericDistributor']/ConnectionString$</XPathName>
<ShowJobsWithUnknownState>false</ShowJobsWithUnknownState>
<EstimatedJobDuration>15</EstimatedJobDuration>
<IntervalSeconds>300</IntervalSeconds>
<SyncTime/>
<TimeoutSeconds>300</TimeoutSeconds>
</Configuration>
</UnitMonitor>