ReplTip – Breakout by Article

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.

with myCTE ([publisher_db], [publication_id], [article], [cmd_count]) as ( select
       a.[publisher_db],
       A.[publication_id],
       a.article,
       COUNT_BIG(1) as [cmd_count]
from
       dbo.MSrepl_commands c with (nolock)
       join dbo.MSpublisher_databases pd with (nolock)
               on pd.[id] = c.[publisher_database_id]
       join dbo.MSarticles a with (nolock)
               on a.[article_id] = c.[article_id]
               and a.[publisher_db] = pd.[publisher_db] group by
       a.[publisher_db],
       A.[publication_id],
       a.article
)
select
        @@SERVERNAME as [server_name],
        t.cmd_count,
       t.publisher_db,
       p.publication,
       t.article
from
       myCTE t
       join dbo.MSpublications p with (nolock)
                on p.[publication_id] = t.[publication_id]
               and p.[publisher_db] = t.[publisher_db] order by
       t.cmd_count desc,
       t.publisher_db,
       p.publication,
       t.article


server_name                   cmd_count           publisher_db                   publication                   article
------------------------------ -------------------- ------------------------------ ------------------------------ -----------
VCHRISSK2012\SQL2K14           178                 Sub_A                         PeerCustomer                   Customer
VCHRISSK2012\SQL2K14           177                 Sub_B                         PeerCustomer                   Customer

(2 row(s) affected)

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.

  SELECT [publisher]
     ,[publisher_db]
     ,[publication]
     ,[distribution_agent]
     ,[transaction_timestamp]
FROM [Subscriber].[dbo].[MSreplication_subscriptions]

For example:

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