I have a transaction replication setup between two servers. Database size is around 400GB on publisher. There are six publications being published to subscriber. Occasionally, I see one of the publications (not always the same) errors out due to blocking at subscriber. When I searched for blocking, I found out following statement usually blocks update processes running on subscriber. It blocks processes related to publication and causes error since log reader is unable to proceed. Only workaround I have found so far is to kill spid which runs following process. This process is run by Replication Distribution Agent on subscriber. However, distributor resides on publisher.
DECLARE @spid INT DECLARE @locktype NVARCHAR(60) DECLARE @lockmode NVARCHAR(60) DECLARE @indexname SYSNAME DECLARE @tablename SYSNAME --Following spid is related to DML process running on subscriber related to publication showing error SELECT @spid = 69 SELECT @locktype = L.resource_type ,@lockmode = L.request_mode ,@indexname = I.NAME ,@tablename = OBJECT_NAME(I.object_id) FROM sys.dm_tran_locks L JOIN sys.partitions P ON P.hobt_id = L.resource_associated_entity_id JOIN sys.indexes I ON I.object_id = P.object_id AND I.index_id = P.index_id WHERE L.request_status = 'WAIT' AND L.request_session_id = @spid RAISERROR ( 21870 ,10 ,0 ,@spid ,@locktype ,@lockmode ,@indexname ,@tablename )
Any clue why this happens and what's the best practice to avoid this in future? Thank you.