June 3, 2009 at 12:13 pm
I am looking better way to query replication latency for our T1 support who dont have admin access ,i there any good query other than
select * from distribution.dbo.MSdistribution_history
that gives brief latency ?
Thanks much
June 3, 2009 at 7:48 pm
i got the soln to insert tracer token after each interval of time through sql agent job on each subscriber and now i am quering the data like
SELECT publication_id, agent_id,
datediff(ss, subscriber_commit, (getdate())) AS Latency
FROM MSTracer_tokens
JOIN MSTracer_history
ON tracer_id = parent_tracer_id
WHERE tracer_id = (SELECT MAX(parent_tracer_id)
FROM MSTracer_history
WHERE subscriber_commit IS NOT NULL)
can some body help me out to add the difference from the time the query is run to the latest subscriber_commit in my query and also the server and db name ?
June 4, 2009 at 10:51 am
Experts , Just wondering if some body have clue on this?
June 4, 2009 at 10:43 pm
I could able to get what i was looking
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 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
where
TT.tracer_id=(SELECT MAX(parent_tracer_id)
FROM MSTracer_history(nolock)
WHERE
subscriber_commit IS NOT NULL
and
parent_tracer_id=tt.tracer_id)
but it is giving all the tracer id results , how i can tweak it to give me only the latest token result for both of my publisher DBs
June 4, 2009 at 11:57 pm
posting the latest version i just wrote and worked for me ,if it helps for somebody....
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 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)Tid ,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)Q
ON TT.tracer_id = Q.Tid
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply