Microsoft Knowledge Base Article
This article contents is Microsoft Copyrighted material.
©2005-©2007 Microsoft Corporation. All rights reserved.
Terms
of Use |
Trademarks
Article ID: 246330 - Last Review: October 16, 2003 - Revision: 3.2
BUG: Large Number of Agents on a Replication Distribution Server May Cause Heavy Deadlocking in TEMPDB
This article was previously published under Q246330
BUG #: 56558 (SQLBUG_70)
BUG #: 235803, 235578 (SHILOH_BUGS)
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.
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.
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.
Microsoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article.
APPLIES TO
- Microsoft SQL Server 7.0 Standard Edition
- Microsoft SQL Server 2000 Standard Edition
Community Feedback System
Very often, it takes hours to solve a problem. Very often, you've looked high
and low, and have tried a lot of solutions. When you finally found it, chances
are, it was because someone else helped you. Here's your chance to give back.
Use our community feedback tool to let others know what worked for you and what
didn't.
Please also understand that the community feedback system is not warranted to be
correct, it's simply a system that we've built to let people try and help each
other. If something in a feedback response doesn't make sense to you, or you're
not comfortable making changes that the feedback talks about (like registry
edits), please consult a professional.
Thank you for using kbAlertz.com Feedback System.
-- Scott Cate