in my transactional pull replication , i had two publications and 1 subscriber server for these two publications
so one on one mapping from publisher server to same subscriber server of two databases for replication.
i was inserting tracer token to measure replication latency and i was using below query but now we are adding one more subscriber server to one of the publication where my query fails to bring correct results , can somebody take a look and point out the problem?
SELECT distinct MM.Publisher as [Publisher Server],MI.subscriber as [Subscriber Server],MM.publisher_db as [Publisher DB],
datediff(mi, TT.publisher_commit,TH.subscriber_commit) AS [Latency From Publisher to Subscriber in Minutes],
datediff(mi, TH.subscriber_commit, (getdate())) AS [Time Difference in last Successful Transaction commit at subscriber to Current Time in Minutes]
FROM MSTracer_tokens TT(nolock)
JOIN MSTracer_history TH(nolock)
ON TT.tracer_id = TH.parent_tracer_id
join MSreplication_monitordata MM(nolock)
join MSsubscriber_info MI
(select max(tracer_id)s ,publication_id FROM MSTracer_tokens (nolock)
subscriber_commit IS NOT NULL
group by publication_id)xyz
on TT.tracer_id = xyz.s