How to show pending Merge Replication changes

Share this Post

SQL Server sp_showpendingchanges is used to determine how many changes need to be moved to a subscriber or uploaded to a publisher.  You can use these steps to “roll your own” and pull similar data.

To tackle this problem from the meta data can be a bit daunting, but there are some ways to get an idea of how many changes are left for a specific table.  You can combine what you find for all the tables in a publication and get an estimate for how many changes remain to be synchronized.

If you have multiple publications that you are watching then your first stop will be in the sysMergePublications table.  Query this table to find the pubid of the publication that you want to monitor.  An example would be simply:  select pubid,* from sysmergepublications

With an output like this

clip_image002

Next get your list of article nicknames for the publication from the sysMergeArticles table:  select nickname,* from sysmergearticles where pubid = ’22C4781D-E6BC-400B-BC27-A4DB70A0D182′

clip_image004

So now you are ready to count the changes for these articles that still need to be synchronized.  To do this we need to check the progress “watermark” of generations (groups of changes) sent down to the individual subscribers.  We store this watermark in the sysMergeSubscriptions table as the sentgen column.  You could get this using a query like this:  select sentgen, * from sysmergesubscriptions where pubid = ’22C4781D-E6BC-400B-BC27-A4DB70A0D182′ AND pubid <> subid

clip_image006

So now you know that for this particular subscriber, the last generation sent was generation #4.  You need to discover how many generations exist in the metadata greater than #4 and how many changes those generations contain.  To do this, you could use a query like this:

select mc.tablenick, COUNT (*) as changes from MSmerge_contents mc
where mc.generation > 4
group by mc.tablenick

A few additions to have the current sentgen passed in as a variable and return your actual table names and you end up with something like this:

declare @sentgen int =
(select sentgen from sysmergesubscriptions
where subid = '7E4942FB-A5D4-45CF-AB87-9D5566C1609A')
select ma.name, COUNT (*) as changes from MSmerge_contents mc
join sysmergearticles ma on (ma.nickname = mc.tablenick)
where mc.generation > @sentgen
group by ma.name

clip_image008

This tells you how many changes are remaining to be sent for each individual table to a specific subscriber.  The subscriber was specified by its subid which I got from sysmergesubscriptions.  In this case I have more than 2 million changes for the table named Artists and only 1 change for Table_1.

Really all you need is the last query, but I thought I’d take you through the data so that it makes sense.  This way you are armed with some information and could potentially modify it or write your own unique query.

Jonathan Clark
Microsoft SQL Server Escalation Services


Share this Post

About: ReplTalk


5 thoughts on “How to show pending Merge Replication changes”

  1. Hey Jonathan, this is a great article, would this same logic work at the subscriber to se epending changes to be uploaded to the publisher?  I tried this on a subscriber and all my sentgen values were NULL

  2. Jonathan, I was able to get it working on running on my subscriber by changing " pubid <> subid " to " pubid = subid "

  3. You got it Chad – the subscriber data is stored on a row where pubid <> subid. I think about it as if the meta data treats both partners as simultaneous pub and sub.  There is a row for the subscriber for itself and for its partner and in its own metadata it is the publisher.  Can get confusing, but gives some context to the various rows in sysmergesubscriptions.   (jonclark)

  4. Thank you again for all the knowledge you distribute,Good post. I was very interested in the article, it's quite inspiring I should admit. I like visiting you site since I always come across interesting articles like this one.Great Job, I greatly appreciate that.Do Keep sharing! Regards, <a href='https://www.facebook.com&#39; target='_blank'>facebook</a>

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.