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 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.

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 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.

External

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

Overrideable Parameters