Merge Agent blocked by sp_mergemetadataretentioncleanup

Problem

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.

Resolution

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