Worth Corner, Crawley, RH10 7SL

Support Hours - Mon-Sat, 8.00-18.00

Welcome to our SQL Server blog

This blog was created in order to pass on some of our knowledge and advice in the hope that the community might find it useful.

Obviously Microsoft SQL Server is our area of expertise and therefore this is our most thorough blog with everything from the basic through to the the incredibly advanced and, as such, we should have something for everyone within our pages.

Merge Replication Commands in Pipeline
Published: Apr 03, 2015
Ever been in one of those situations in which you see endless blocking whilst replication makes new generations and you’re left wondering if there’s a specific table causing it or whether it’s just generic replication volume which is causing your problem?

Well the easiest way to tell is to have a look and see what commands are yet to be processed. Sadly tables such as msMerge_contents aren’t the easiest things to immediately decipher, but with a little code it’s not that difficult at all.

Basically all you need is access to the publication server, the name of the subscribing server, and you’re good to go.

Let’s set up a quick example…

In my test machine I’ve set up a database called “testPub” and a subscribing database called “testSub”. Inventive, I know. Anyway… in testPub I have a few tables copied from the AdventureWorks database which are merge replicated to testSub.

I’ve also run a variety of updates… some of these have been replicated, some have not. Our challenge is to find those which have yet to be replicated…

Let’s have a quick look in msMerge_contents:

use testPub

select *
from msMerge_contents

As you can see, this isn’t overly helpful and definitely doesn’t answer our question. However, in actual fact that’s not true at all… you’re looking at all the information you need… you just need to combine it with some other information first.

Merge replication works on “generations”. These are the groups of replicated changes that SQL passes from publisher to subscriber. Therefore what we need is the latest generation to have been processed. This is held with the subscription record…

use testPub

select subscriber_server, db_name, sentGen
from sysmergeSubscriptions

As you can see from my output… the last generation that was sent to my subscriber is 3.

This now makes our final query incredibly easy:

-- total outstanding commands
select COUNT (*) as changes
from MSmerge_contents mc with (nolock)
join sysmergearticles ma with (nolock)
on (ma.nickname = mc.tablenick)
where mc.generation > 3

-- outstanding commands per article
select ma.name, COUNT (*) as changes
from MSmerge_contents mc with (nolock)
join sysmergearticles ma with (nolock)
on (ma.nickname = mc.tablenick)
where mc.generation > 3
group by ma.name
order by changes desc

It’s as simple as that.
Leave a Comment
Your email address will not be published. All fields are mandatory.
NB: Comments will only appear once they have been moderated.

SQL  World  CEO
Kevin  Urquhart

iPhone Selfie

I am a SQL Server DBA, Architect, Developer, Trainer, and CEO of SQL World. This is my blog in which I’m simply trying to share my SQL knowledge and experiences with the world.


© Copyright 2020 SQLTraining Ltd.