How to see all pending merge changes

There is a handy system stored procedure sp_showpendingchanges that will provide an “approximation” of the pending changes that need to be replicated¬†at that database. This proc will list inserts/updates/deletes per article and can also filter by the subscriber server. It has been around since SQL 2008 and is a welcome change. Would be nicer if it was represented in the replication monitor like the one for transactional replication though.

You can run the procedure without any arguments in which case it provides a summary.

You can also provide all arguments as below to look at a specific subscriber and table and see the rows waiting to be sent.

exec sp_showpendingchanges @destination_server = ‘DESKTOP-NSONKMO’
, @publication = ‘Testuse_partition_groups’
, @article = ‘tCompany’
, @show_rows = 1

However, if the subscriber database name is the same as the publisher one nothing will get returned. Not great as this is a common setup!

In this case you’ll need to roll your own script using something like the code I created below:

select tCompany.* from tCompany
inner join MSmerge_contents on tCompany.rowguid = MSmerge_contents.rowguid
inner join MSmerge_genhistory on MSmerge_contents.generation = MSmerge_genhistory.generation
where MSmerge_genhistory.genstatus = 0 and MSmerge_genhistory.generation >
(select recgen from sysmergesubscriptions where subscriber_server = ‘DESKTOP-NSONKMO\paulsinst’)

Leave a Reply

Your email address will not be published.