tracer token query for replication latency

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » Development » tracer token query for replication latency

tracer token query for replication latency

Rate Topic

Display Mode

Topic Options

Author

Message

sqlqueue

Posted Sunday, June 14, 2009 9:16 AM

SSC 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

DBA-640728

Posted Thursday, January 07, 2010 5:41 PM

SSC-Addicted

Group: General Forum Members
Last Login: Wednesday, May 08, 2013 5:21 PM
Points: 496, Visits: 1,724

did you get to fix your problem?

Post #844013

« Prev Topic | Next Topic »

Permissions