Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

tracer token query for replication latency Expand / Collapse
Author
Message
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 06, 2009 9:11 PM
Points: 42, Visits: 105
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

Post #734608
Posted Thursday, January 07, 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: Tuesday, April 01, 2014 4:52 PM
Points: 531, Visits: 1,855
did you get to fix your problem?
Post #844013
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse