TSQL Commands to generate and track Tracer Tokens

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           2010-01-23 14:25:33.503 2                  39

1              3           2010-01-23 14:29:46.180 2                  38

1              3           2010-01-23 20:13:45.170 2                  205

1              3           2010-01-23 21:09:33.953 1                  4

1              3           2010-01-23 21:17:44.940 3                  5

1              3           2010-01-23 13:10:15.197 1                  2433

1              3           2010-01-23 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:

       CHRISSKACER-AdventureWorksLT-TranProducts-CHRISSKACER-3

       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

 

 

Chris Skorlinski

Microsoft SQL Server Escalation Services