Worth Corner, Crawley, RH10 7SL

Support Hours - Mon-Sat, 8.00-18.00

Welcome to our SQL Server blog

This blog was created in order to pass on some of our knowledge and advice in the hope that the community might find it useful.

Obviously Microsoft SQL Server is our area of expertise and therefore this is our most thorough blog with everything from the basic through to the the incredibly advanced and, as such, we should have something for everyone within our pages.

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.

SQL  World  CEO
Kevin  Urquhart

iPhone Selfie

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


© Copyright 2020 SQLTraining Ltd.