Tracer Tokens
Published: 20th June 2014
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.
Comments:
NB: Comments will only appear once they have been moderated.