Technical Article

Script to indentifty big replication transactions

,

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

Rate

5 (3)

You rated this post out of 5. Change rating

Share

Share

Rate

5 (3)

You rated this post out of 5. Change rating