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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 |
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.
|
1 2 3 4 5 6 |
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])
Very useful query especially when you deal with millions and billions of rows of data…thanks for sharing
Nice work Brendan & Chris