Merge Agent blocked by sp_mergemetadataretentioncleanup
SQL Server Escalation Services
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 some transactions & if during this time there is a failure of Merge agent or connectivity problems then those transactions aren’t rolled back. We don’t have XACT_ABORT set ON in that stored procedure.
This problem has been fixed in SQL 2005 SP3 and SQL 2008 SP1.