Replication tracer token dupes query

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » Development » Replication tracer token dupes query

Replication tracer token dupes query

Rate Topic

Display Mode

Topic Options

Author

Message

sqlqueue

Posted Friday, June 26, 2009 11:52 AM

SSC Rookie

Group: General Forum Members
Last Login: Thursday, August 06, 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 »

Permissions