0

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.

  • Who is blocking whom ? Did you manage to get the sql ? – Kin Shah Jun 30 '16 at 2:40
  • Above query is main culprit for blocking. SPID mentioned in the query is of replication process performing update on subscriber. – Pat Jun 30 '16 at 19:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.