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
.
— 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
