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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: