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

Replication tracer token dupes query Expand / Collapse
Author
Message
Posted Friday, June 26, 2009 11:52 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
experts , can you help me out to remove duplicates from the below query , i am inserting tracer token every minute and have 2 subscribers ,below query runs fine and gives correct result but as soon as i un-comment the commented columns to bring the required result it gives duplicates...

use distribution
SELECT
--MM.Publisher AS [Publisher Server],
--MI.subscriber AS [Subscriber Server],
--MM.publisher_db AS [Publisher DB],
DATEDIFF(ss, TT.publisher_commit,TH.subscriber_commit) AS [Publisher to Subscriber Latency in Minutes]
--,DATEDIFF(mi, TH.subscriber_commit, (GETDATE())) AS [Realized Subscriber 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
--AND mm.agent_type = 3
--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 #742900
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse