June 14, 2009 at 9:16 am
Hi,
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)
ON TT.publication_id=MM.publication_id
join MSsubscriber_info MI
ON MM.publisher=MI.publisher
join
(select max(tracer_id)s ,publication_id FROM MSTracer_tokens (nolock)
join
MSTracer_history(nolock)
on
tracer_id=parent_tracer_id
where
subscriber_commit IS NOT NULL
group by publication_id)xyz
on TT.tracer_id = xyz.s
January 7, 2010 at 5:41 pm
did you get to fix your problem?
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply