BUG: Large Number of Agents on a Replication Distribution Server May Cause Heavy Deadlocking in TEMPDB (246330)

SYMPTOMS

If the Snapshot Agent, Logreader Agent, Distribution Agent or Merge Agents run concurrently on the distribution server, or if you drop or add subscriptions while these agents run on the server, you may experience deadlocks on the replication status tables maintained on the tempdb database. As the number of agents that run simultaneously increases, the deadlocking behavior manifests itself more frequently, causing the agents to retry more often upon failure.

CAUSE

All replication agents and stored procedures, which add and drop subscriptions, update tempdb.dbo.Msreplication_agent_status to maintain the status of replication. This table is also accessed by the SQL Performance monitor and the Replication monitor, which SQL Enterprise manager starts. Contention on the tempdb.dbo.Msreplication_agent_status table in a circular fashion causes deadlocks.

WORKAROUND

You can reduce the number of deadlocks on the replication status table on tempdb by using any of the following:

Close the Enterprise manager sessions that cause the Replication monitor to start.
Stop any Performance monitor sessions that monitor SQL counters.
If multiple agents are running, the problem can be avoided by making sure that all of them run on a schedule and that no two agents run at the same time.

On SQL Server 7.0, the problem may occur less often after you apply Microsoft SQL Server 7.0 Service Pack 2 or later.

STATUS

Microsoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article.