How to show pending Merge Replication changes


How to show pending Merge Replication changes

Jonathan Clark
Microsoft SQL Server Escalation Services

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. 


Comments (8)

  1. celina says:

    This is a great inspiring article. I am pretty much pleased with your good work. You put really very helpful information. Keep it up. Keep blogging. Looking to reading your next post.

    <a href = http://www.nealdnielsen.com/>Brighton Michigan lawyers</a>

  2. Chad Churchwell says:

    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

  3. Chad Churchwell says:

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

  4. 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)

  5. petsspark says:

    How needs to be online purchasing from family pet shop Mumbai continues to be discussed the following. Searching family pet shop Mumbai is not any longer any challenging process.

  6. Quotes and joke says:

    And keep mid-air full of laughter keep your antics running. While informing antics. clean or maybe filthy, you've to make note of which not necessarily everybody gives the same perception connected with humour, which means, discovering this hilarious aspect quotes and joke, then do a very little study of what you are feeling is acceptable for your friends. Thoroughly clean antics are simply since hilarious since filthy antics. For more information and enjoy quotes and joke visit the site  http://www.quotesandjoke.com/

  7. The earth planet says:

    Cricket wallpapers is quite favorite between cricket lovers. Wall picture can be picture that the cricket fan can place on backdrop associated with his or her computer screen, phone or perhaps notebook computer. Every single cricket fan desires to get cricket wallpapers. Getting the best pose in their beloved person is not always easy intended for lovers. Cricket wallpapers illustrates lovers choices in addition to commitment. Lovers flick through numerous options to get nearly all popular mobility associated with cricket person. For more information about the cricket visit the site http://theearthplanet.com/

  8. asda says:

    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>

Skip to main content