ReplTip – Breakout by Article


–Chris Skorlinski, Microsoft SQL Escalation Services

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)

 

 

–Can be modified with WHERE clause for rows [xact_seqno] > [last replicated transaction]

 

SELECT [publisher]

      ,[publisher_db]

      ,[publication]

      ,[distribution_agent]

      ,[transaction_timestamp]

  FROM [Subscriber].[dbo].[MSreplication_subscriptions]

 

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


Comments (2)

  1. Vamsi Vakiti says:

    Very useful query especially when you deal with millions and billions of rows of data…thanks for sharing

  2. Rick O says:

    Nice work Brendan & Chris

Skip to main content