Distribution Agents state (aggregated for all Publications)

Summary

One or more of the Distribution agents failed.

Causes

Some of the reasons that can explain failures of Distribution agents are:

• 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 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 the Snapshot files

• Required privileges are not held by SQL Agent Service.

• Incorrect parameters passed to the Distribution Agent.

Resolutions

• 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 large value.

• There is a Primary Key violation or “row not found” errors on the Subscriber.

  • Check 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 this 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 DistributionAgent is unable to readSnapshot files

Use a UNC path to store the snapshot folder. Share the folder and grant read permissions to the Distribution agent account.

• Required privilegesare 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 specified 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.

External

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

Overrideable Parameters