Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Tracer Token

By Mohan Kumar,

SQL Server 2005 has lot of new and improved components, and the Tracer Token in replication is one of them. It allows us to validate connections and helps in measuring latencies between the publisher, distributor and subscriber(s). This functionality allows an accurate calculation of latencies that ultimately helps in finding which subscriber take more time to receive a change from the publisher than expected.

You may be wondering whether it puts an extra load on your system or if it will slow down replication? The answer is NO because it only writes very small amount of data in transaction log of the publication database.

I used this feature recently in a company where they had transaction replication failures almost every night. The SQL Server wasreporting following errors:

Query timeout expired
The agent failed with a 'Retry' status. Try to run the agent at a later time.

I did not want to just setup just any value for the QueryTimeout without knowing what it should be. So, I setup the Tracer Token feature and ran it for two days. Afterwards I knew what value to use and configured the system accordingly. Today, it is the fourth week using that setting, and it has not failed yet.

This is a wonderful new feature SQL Server 2005 introduced, and here is how you can implement it and start using it today.

Method #1. (Replication Monitor)

Launch Replication Monitor, and then Expand the Server => Select Publication

Click on the Tracer Token Tab in right pane and then click on Insert Tracer as shown below

After SQL Server sends the token through the replication process, it will display Latencies as shown below:

The latency will be recorded when you click on Insert Tracer. Every time you click on Insert Tracer, a new date/time will be added in Dropdown box (Time Inserted). Hence, you can view your latency at any given time using this dropdown box provided you had Inserted a Tracer Token.

The Publisher to Distributor column displays the time elapsed between a transaction committed at the Pulisher and the corresponding command entered in the distribution database.

The Distributor to Subscriber column display the time elapsed before the commit of the transaction at the subscriber.

Limitation of using Replication Monitor

There is a limitation in setting up the Tracer Token using Replication Monitor. The Tracer Token has to be inserted manually and It limits you to viewing one value at a time.

To overcome this limitation, we have another method to accomplish same thing.

Method #2. (Thru SQL Server Agent)

You can automate your tracer token insert using this method. You need to follow the steps below:

  • Create a SQL Server Agent Job on Publisher Server
  • Add a Job Step with the following T-SQL command:

sp_posttracertoken 'xyz' -- substituting xyz for the name of your publication

  • Schedule this Job to run at whatever interval you want to insert a token into your replication process.

How to analyze these tracer values?

This data is stored in the distribution database. There are a few system defined stored procedure available to view the recorded data. They are:

  • sp_helptracertokens - returns data for every tracer token inserted at publisher for specified publication
  • sp_helptracertokenhistory - returns tokenid specific data

But, I have simplified a way to get the data by writing a query mentioned below:

use distribution
GO
select publisher_commit,
       distributor_commit,
       datediff(ss, publisher_commit, distributor_commit) 'Latency bw Pub and Dis',
       subscriber_commit,
       datediff(ss, distributor_commit, subscriber_commit) 'Latency bw Dis and Sub'
 from MSTracer_tokens join MSTracer_history 
    on tracer_id = parent_tracer_id

The result of this query will have the following data:

  • Date and Time when transaction committed at publisher
  • Date and Time when Command for same transaction inserted at Distributor,
  • Time (in Seconds) Elapses to commit data between publisher and distributor
  • Date and Time when transaction committed at Subscriber
  • Time (in Seconds) Elapses to commit data between Distributor and Subscriber.

Conclusion

Tracer Token is excellent feature to measure latencies and validate connections that can be used to validate or set Agent profile settings and/or identify timeout related problem that can cause replication to fail.

Total article views: 3999 | Views in the last 30 days: 16
 
Related Articles
BLOG

Measuring Transactional Replication Latency Without Tracer Tokens

Measuring Transactional Replication Latency Without Tracer Tokens SQL Server 2005 introduced Tracer ...

FORUM

Tracer tokens not moving from distributor to subscriber

tracer tokens subscriber latency

FORUM

Tracer Token

Comments posted to this topic are about the item [B]Tracer Token[/B] Nice one. :) Thanks so much Ani...

FORUM

tracer token query for replication latency

Hi, in my transactional pull replication , i had two publications and 1 subscriber server for these ...

FORUM

Replication tracer token dupes query

experts , can you help me out to remove duplicates from the below query , i am inserting tracer toke...

Tags
replication    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones