Merge Agent blocked by sp_mergemetadataretentioncleanup

Chris Skorlinski
SQL Server Escalation Services

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 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.

Resolution

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