Merge Agent blocked by sp_mergemetadataretentioncleanup

Share this Post


Customer observes high latency in SQL Server 2005 Merge replication. In sysprocesses you see that Merge agents are being blocked by another Merge agent & the blocker Merge agent SPID is sleeping with “AWAITING COMMAND”.

Root Cause

If you take output of “select open_transaction_count from sys.dm_exec_requests where session_id = <blocker SPID>” & find that blocking SPID has an open transaction

When merge agent executes sp_mergemetadataretentioncleanup to clean up metadata, it opens transactions.  If during this time there is a connection failure transactions aren’t rolled back as the cleanup procedures are missing XACT_ABORT set ON setting.


This problem has been fixed in SQL 2005 SP3 and SQL 2008 SP1.

Share this Post

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.