Tracer Tokens
Published: Jan 01, 2016
If you’re using Transactional Replication then these can be invaluable to you. These are incredibly lightweight, easy to use, and they will help you with all manner of reporting and troubleshooting within your replicated environment.

Effectively a Tracer Token is a marker placed in the transaction log of your database that is meaningless to all of SQL Server except your Log Reader which sees it and replicates it through to your subscriber, tracking it at every step of the way.

This can be invaluable because the result of this is that you have complete end to end tracking of your replication without having to do anything onerous such as creating a tracking table and updating values on a schedule, for example, which is something that I have seen done before.

So how do we use these? Well there are 2 methods… the GUI and in code.

Doing this in either is quite simple, so we’ll start with the GUI.

Open Replication Monitor…

If you expand your Publisher you’ll see the following at the top of the main window:

Select Tracer Tokens…

Now all you have to do is click “Insert Tracer” and watch what happens. Firstly you’ll see that SQL Server inserts a token and prepares to track it (note there is one per subscriber… in my example below I have 2 subscribers)…

Gradually you’ll see these update as the token passes through replication…

And there you go. You’ve inserted a tracer token and watched it pass from start to finish through your replicated environment and you can see how well your network and replication is functioning.

To do this same thing in TSQL is just as easy…

Within your published database, just run the following:

exec sys.sp_postTracerToken @publication = 'myPublicationName'


And then to collect your tokens, simply look in the following tables:

select * from distribution.dbo.mstracer_tokens
select * from distribution.dbo.mstracer_history


It’s that simple. With a little hard work and a few joins to the publication and subscription tables you can easily make some valuable reporting and alerts.
Leave a Comment
Your email address will not be published. All fields are mandatory.
NB: Comments will only appear once they have been moderated.

Your SQL Trainer
Kevin Urquhart

I am a SQL Server DBA, Architect, Developer, and Trainer for SQL Training. This is my blog in which I’m simply trying to share my SQL knowledge and experiences with the world.

Kevin Urquhart
SQL Server Consultant, London

Categories


Archives


Copyright © 2018 London SQL Server Training Courses @ SQL Training | Website Design From Scott Heron