Tracer Token

  • Comments posted to this topic are about the item Tracer Token

    [font="Verdana"]--www.sqlvillage.com[/size][/font]

  • Nice one. 🙂

  • Thanks so much Anirban!!!

    [font="Verdana"]--www.sqlvillage.com[/size][/font]

  • Cool Article.

  • Mohan Kumar (4/25/2008)


    Thanks so much Anirban!!!

    You are welcome.....:P

  • In what way this is used? Using this how we can avoid the query time out?

  • Please correct me if I'm wrong Mohan but I think the way this would be used is by inspecting the results and increasing the query timeout to a value greater than the max value of the results of your query. If the max value is extraordinarily high (minutes vs. seconds) then there are clearly other (presumably network related) issues.

    Does this answer your question Priya

    Ron Cicotte
    ron.cicotte@gmail.com
    Data Transformations, LLC

  • Hey , any body have query that will work on multiple subscriptions , this does not work on multiple subscription , i am looking like

    publisher , subscriber , publisher_db ..

    i tried below but it is working fine on one subscriber but i am looking if somebody can tweak it for multiple subscribers

    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

  • Not sure if this is quite what you wanted, but you can tweak the query in the article to add in the name of the publication and the name of the subscriber's distribution agent by joining MSpublications and MSdistribution_agents thus:

    select MSpublications.publication,

    MSdistribution_agents.name,

    MSTracer_tokens.publisher_commit,

    MSTracer_tokens.distributor_commit,

    datediff(ss, MSTracer_tokens.publisher_commit, MSTracer_tokens.distributor_commit) 'Latency bw Pub and Dis',

    MStracer_history.subscriber_commit,

    datediff(ss, MSTracer_tokens.distributor_commit, MStracer_history.subscriber_commit) 'Latency bw Dis and Sub'

    from MSpublications

    inner join MSTracer_tokens

    on MSpublications.publication_id = MStracer_tokens.publication_id

    inner join MSTracer_history

    on MSTracer_tokens.tracer_id = MSTracer_history.parent_tracer_id

    inner join MSdistribution_agents

    on MStracer_history.agent_id = MSdistribution_agents.id

    Hope this helps,

    Rachel

  • hi Rachel, i tried using your query but can you tell me why does it show multiple rows for the same publication and name?

  • It will show a row for every tracer token which has been added. According to BOL the information gets cleared after the history retention period of the distribution database. You can change this if you go to Distributor Properties in replication.

  • thanks Rachel, is it accurate to get the information for each token? would'nt it be better to get the Get latency information for the last inserted token?, how does replication monitor get this information? in there I don't see multiple rows for the tokens, i ran your query and your results latency in seconds does not match my replication monitor information. Is there anything i need to change?

  • I'm afraid your questions are beyond me... Maybe someone else can answer them.

    Sorry,

    Rachel.

  • Hello. Will you please give directions for getting to the tracer token tab in SQL Server 2008. I can't find it by just poking around and I can't find instructions on line.

    Thanks.

  • In Management Studio, right-click on the Replication folder and choose Launch Replication Monitor. In the Replication Monitor, coose the pulication you're interested in and you'll see the Tracer Tokens tab.

    I assume that's what you mean?

Viewing 15 posts - 1 through 15 (of 16 total)

You must be logged in to reply to this topic. Login to reply