TSQL Commands to generate and track Tracer Tokens

Share this Post

Tracer Tokens are a great way to monitor the flow of transactions from the Publisher to the Subscriber.  These can be generated and tracked from SQL Server Replication Monitor or via TSQL commands.  This post includes some of the common TSQL commands used to generate and track Tracer Tokens.  Details can be found on SQL Server Books Online.

–Tracer Token Tracking Tables

USE Distribution

SELECT * FROM MStracer_tokens

SELECT * FROM MStracer_history

 

–End to End time

SELECT publication_id, agent_id,

       t.publisher_commit, t.distributor_commit, h.subscriber_commit

FROM MStracer_tokens t

JOIN MStracer_history h

ON t.tracer_id = h.parent_tracer_id

 

–Let’s see that in seconds

SELECT publication_id, agent_id, t.publisher_commit,

       Datediff(s,t.publisher_commit,t.distributor_commit) as ‘Time To Dist (sec)’,

       Datediff(s,t.distributor_commit,h.subscriber_commit) as ‘Time To Sub (sec)’

FROM MStracer_tokens t

JOIN MStracer_history h

ON t.tracer_id = h.parent_tracer_id

 

 

publication_id agent_id    publisher_commit        Time To Dist (sec) Time To Sub (sec)

————– ———– ———————– —————— —————–

1              3           20100123 14:25:33.503 2                  39

1              3           20100123 14:29:46.180 2                  38

1              3           20100123 20:13:45.170 2                  205

1              3           20100123 21:09:33.953 1                  4

1              3           20100123 21:17:44.940 3                  5

1              3           20100123 13:10:15.197 1                  2433

1              3           20100123 13:28:16.373 2                  1368

 

 

—Find worst performing subscribers.

SELECT

convert(varchar(10),agent_id) as ‘agent id’,

max(Datediff(s,distributor_commit,subscriber_commit)) as ‘MAXTime To Sub (sec)’,

avg(Datediff(s,distributor_commit,subscriber_commit)) as ‘AVG Time To Sub (sec)’

FROM MStracer_tokens t

JOIN MStracer_history h

ON t.tracer_id = h.parent_tracer_id

group by agent_id

order by 2 desc

 

agent id   MAXTime To Sub (sec) AVG Time To Sub (sec)

———- ——————– ———————

3          2433                 211

5          262                  262

 

–Who is Agent 3?

SELECT name, publication, subscriber_id,subscriber_db

FROM distribution.dbo.MSdistribution_agents

 

       Sample data:

       CHRISSKACERAdventureWorksLTTranProductsCHRISSKACER3

       TranProducts

       AdventureWorks_TranSub1

 

 

–View Job command line to get Subscriber Name

SELECT sjs.step_name, sjs.command from msdb.dbo.sysjobsteps sjs

join msdb.dbo.sysjobs sj

on sj.job_id = sjs.job_id

Where name = ‘CHRISSKACER-AdventureWorksLT-TranProducts-CHRISSKACER-3’

 

 

       –Look for -Subscriber [CHRISSK1\SQL2K8] property which identifies the server

Technorati Tags:

.

 

— When latency is observed, use commands below to

     insert a new tracer token in the publication database

     every 1 to 5 minutes.

WHILE (1=1)

BEGIN

       EXEC sys.sp_posttracertoken

       @publication = ‘TranProducts’

       — Wait 1 minute

       WAITFOR DELAY ’00:01:00′

END

 

 


Share this Post

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.