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
go

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
go

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.

Your SQL Trainer
Kevin Urquhart

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

Kevin Urquhart
SQL Server Consultant, London

Categories


Archives


Copyright © 2018 London SQL Server Training Courses @ SQL Training | Website Design From Scott Heron