Category Archives: Replication

Identify huge transactions occured in Replication


Sometimes replication slows down when a transaction has a large number of row changes. This script helps you identify those transactions (more that 10,000 rows affected) . Should be executed in distribution database .

SELECT
      mt.entry_time, mt.xact_seqno [XACT NO],
      COUNT(mr.xact_seqno)  [# Commands]

FROM MSrepl_transactions mt WITH(NOLOCK)
JOIN MSrepl_commands mr WITH(NOLOCK)
      ON mt.xact_seqno = mr.xact_seqno
GROUP BY
      mt.entry_time, mt.xact_seqno
having  count(1)  > 10000
ORDER BY 1

Advertisements
%d bloggers like this: