Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase

tracer token query for replication latency Expand / Collapse
Posted Sunday, June 14, 2009 9:16 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, August 6, 2009 9:11 PM
Points: 42, Visits: 105
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

(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

Post #734608
Posted Thursday, January 7, 2010 5:41 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Friday, December 2, 2016 2:02 PM
Points: 547, Visits: 1,985
did you get to fix your problem?
Post #844013
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse