How to show pending Merge Replication changes

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

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′

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

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

—

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