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