Transactional Changes in SQL Server 2005 (1): Using Tracer Tokens to Monitor Latency
Transactional replication in SQL Server 2005 has a tracer token functionality which gives us a simple method of measuring latency in transactional replication. Basically, a token is written to the transaction log of the publisher. This is treated as a normal replicated command, and passes from the transaction log to the distribution database and then is 'run' by the distribution agent. In actual fact there is nothing really to apply at the subscriber but nevertheless, this technique allows accurate determination of Publisher -> Distributor -> Subscriber time latencies.
To take advantage of this useful functionality, the Publisher, Distributor and Subscriber must be SQL 2005 for pull subscriptions, while for push, the subscriber may be SQL 2000, although only Publisher -> Distributor latencies will be measured. There are also a number of other minor caveats listed in BOL.
(a) via the GUI
To activate the tracer token functionality, enter the replication monitor graphical interface and select the publication in the left pane (PubDocs in the case below). There are 3 tabs on the RHS. The first lists the distribution agents, while the last lists the snapshot and logreader agents. The middle one gives access to the tracer token functionality. To add a tracer token, select 'Insert New Tracer...'. It'll then be listed in the grid, and initially the 'Publisher to Distributor' and 'Distributor to Subscriber' sections will indicate 'Pending', as the tracer token has only been added to the transaction log and hasn't yet entered the replication pathways. The grid is set to refresh automatically every 10 seconds, although my experience is that the tracer token may incorrectly remain in the pended state after a 'refresh' and manually initiating a refresh from the left pane will correctly update the state.
(b) via TSQL
To do this manually, the script would be:
select * from distribution..MStracer_tokens
Paul Ibison, Copyright © 2013