Distribution Agent State for Subscription

Summary

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.

Causes

• 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

Resolutions

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

External

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

Overrideable Parameters