ReplTip – Breakout by Article

Share this Post

My friend Brendan Odwyer gave me great query to show Distribution database breakout by article for Transactional Replication publications.  We used this to identify 1 table that had +90 million rows  pending in the Distribution database as result of a large bulk update.  It would have taken Replication days to move that bulk down row-by-row.  Instead we dropped the table, added table, and pushed down Snapshot for just that newly added table.  The Bulk-Export\Bulk-Import took about 20 minutes and we were back up and running.

Use this query to see breakout by Article for Distribution database activity.

If you wanted to return only undistributed row counts, include a WHERE [xact_seqno] > [last replicated transaction].  You can determine the last replicated transaction by querying subscriber’s [transaction_timestamp] value.

For example:

WHERE [xact_seqno] > (SELECT [transaction_timestamp] FROM [your_Subscriber].[dbo].[MSreplication_subscriptions])

Share this Post

2 thoughts on “ReplTip – Breakout by Article”

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.