Merge Agent blocked by sp_mergemetadataretentioncleanup
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”.
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.