July 26, 2017 at 11:17 am
We are having issues with replication latency in our environment. I figured out it's from a particular database. But is there any way we can list out the particular table where heavy modifications are happening which in turn causing the replication latency.
Edit: Got the answer.
select article_id,count(command_id) as count1 into #temp from msrepl_commands with (nolock)
group by article_id
select article,count1 from msarticles a
join #temp b
on a.article_id=b.article_id
order by count1 desc,article asc
July 26, 2017 at 1:30 pm
muth_51 - Wednesday, July 26, 2017 11:17 AMWe are having issues with replication latency in our environment. I figured out it's from a particular database. But is there any way we can list out the particular table where heavy modifications are happening which in turn causing the replication latency.
Something like this might work. I think the join is the gist of what it seems you are looking for - you could certainly change the columns. I was just guessing what you would want.
--Run at distributor
select ds.UndelivCmdsInDistDB,
ds.DelivCmdsInDistDB,
a.publisher_db,
p.publication,
a.article,
a.destination_object,
a.source_owner,
a.source_object,
dh.start_time,
dh.duration,
dh.current_delivery_latency,
dh.delivered_transactions,
dh.delivery_latency,
dh.total_delivered_commands,
dh.delivery_rate,
dh.comments
FROM MSdistribution_status ds
INNER JOIN MSarticles a
ON ds.article_id = a.article_id
INNER JOIN MSdistribution_history dh
ON dh.agent_id = ds.agent_id
INNER JOIN MSpublications p
ON p.publication_id = a.publication_id
Sue
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply