Transactional Replication – Tracer Tokens
When working with SQL Server transactional replication, one of the common checks to see if things look healthy is to insert a tracer token into the replication data stream and see how long it takes to get to the subscribers.
This is a process that can be very tedious if you have multiple publications to check, as you need to go to the replication monitor, click on the publisher, then go to tracer tokens tab and insert a tracer token. Then wait to see the result. If you need to do this more than a couple times, you may be waiting for a while.
Also to make things worse the replication monitor is not very keyboard friendly for this type of navigation, so it is a lot of clicking around with the mouse.
To make this easier, I decided that there must a be a way to script it, but I could not find any examples that met me needs, so I did some digging around in the SQL profiler and ran a trace to find out what inserting a token was doing. After some adjustments here is the script that I came up with to list all publications and subscribers and inject a tracer token to each.
This does assume that the publisher is acting as its own distributor.
USE Distribution
GO
DECLARE @startTime as DATETIME = GETDATE();
DECLARE @tsql AS NVARCHAR(MAX) = '';
SELECT @tsql = @tsql + 'EXEC [' + publisher_db + '].[sys].[sp_posttracertoken] @publication = ''' + publication + ''';' + char(13) + char(10)
FROM [distribution].[dbo].[MSpublications]
exec sp_executesql @tsql;
DECLARE @loopMax as INTEGER = 60;
WHILE (
SELECT COUNT(*)
FROM [distribution].[dbo].[MStracer_tokens] t
INNER JOIN [distribution].[dbo].[MStracer_history] h ON t.tracer_id = h.parent_tracer_id
INNER JOIN [distribution].[dbo].[MSpublications] p on t.publication_id = p.publication_id
WHERE t.publisher_commit >= @startTime
and h.subscriber_commit is NULL ) > 0 AND @loopMax > 0
BEGIN
WAITFOR DELAY '00:00:01';
SET @loopMax = @loopMax -1;
END
SELECT p.[publisher_db],
p.[publication],
convert(smalldatetime,t.publisher_commit) 'PubCommit',
convert(smalldatetime,t.distributor_commit) 'DistCommit',
convert(smalldatetime,h.subscriber_commit) 'SubCommit',
Datediff(s,t.publisher_commit,t.distributor_commit) as 'Dist(sec)',
Datediff(s,t.distributor_commit,h.subscriber_commit) as 'Sub(sec)'
FROM [distribution].[dbo].[MStracer_tokens] t
INNER JOIN [distribution].[dbo].[MStracer_history] h ON t.tracer_id = h.parent_tracer_id
INNER JOIN [distribution].[dbo].[MSpublications] p on t.publication_id = p.publication_id
WHERE t.publisher_commit >= @startTime
ORDER BY t.publisher_commit DESC;
Over the last several years, this little script has saved me a ton of time. It literally saves me a 10 minute process and turns it into a copy, paste and 15 seconds of run time.
I am sharing this in hopes that it saves you some time.
More from Stedman Solutions:
Steve and the team at Stedman Solutions are here for all your SQL Server needs.
Contact us today for your free 30 minute consultation..
We are ready to help!
Leave a Reply