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.

Gathering Tracer Token Data
Published: Jan 08, 2016
After my last post surrounding Tracer Tokens I was told by a friend that it was a little harsh to simply end saying that you can just join to a few system tables and make something meaningful. Therefore I’ve given in and this post will show you how you can use tracer token and system data to your advantage.

So, what I’m going to do is to show you a nice way to post your tracer tokens via a scheduled job, posting to all publications, and then collect and store this data in a table.

So where do we start?

Well, I’m going to make a proc which will scan through all the databases in your server, find any publications, and then post a tracer token into each one.

The idea being that we will then place this proc in a scheduled job (which I won’t walk you through as I’m sure you can work that bit out) and that’s it… we’ll have tracer tokens running through all of our publications ready for us to monitor.

What we will need to do is obtain a list of all databases, then loop through them looking for publications (with error handling for non-replicated databases, of course).

Then we loop through those databases and associated publications placing our tracer tokens. It’s that simple:

create procedure [dbo].[replicationPostTracerToken]
   set nocount on

@counter smallint, @sql nvarchar(max)
set @counter = 1

-- Obtain list of databases
declare @database table
id smallint identity(1, 1),
databaseName varchar(50)
insert into @database
select name
from sys.databases

-- create table ready to hold publication list and relevant database
declare @publication table
id int identity(1, 1),
dbName varchar(100),
publication varchar(250)

-- loop through each database looking for publications
while @counter <= (select max(id) from @database)
@sql = 'select ''' + databaseName + ''', name from ' + databaseName + '.dbo.syspublications where sync_method != 0'
from @database
where id = @counter

-- try catch because not all databases are replicated
begin try
insert into @publication
exec sp_executeSQL @sql
end try
-- null catch
begin catch
end catch

select @counter += 1

@counter = 1

-- having got all publications and databases, loop through placing tracer tokens
while @counter <= (select max(id) from @publication)
@sql = 'exec ' + dbName + '.sys.sp_postTracerToken @publication = ''' + publication + ''''
from @publication
where id = @counter

exec sp_executeSQL @sql

set @counter = @counter + 1

   set nocount off

Okay, having done this we now need something to collect these tokens and do something useful with them.

Therefore we’ll create a main data table and a variable holding table, collate the results, and store them for analysis:

create procedure [dbo].[replicationCollectTracerToken]
-- create table for data if it doesn't already exist
if object_id('tracerTokens') is null
       create table
publicationID int,
subscriberID int,
publication varchar(250),
subscriberName varchar(100),
agentName varchar(1000),
publisherCommit datetime,
distributorCommit datetime,
subscriberCommit datetime,
distributorSecs int,
subscriberSecs int,
constraint pk_tracerTokens primary key clustered(publicationID, subscriberID, publisherCommit)

   set nocount on
-- create variable table
declare @results table
publicationID int,
subscriberID int,
publication varchar(250),
subscriberName varchar(100),
agentName varchar(1000),
publisherCommit datetime,
distributorCommit datetime,
subscriberCommit datetime,
distributorSecs int,
subscriberSecs int

-- insert data into variable table.  Include system table info to make tokens meaningful
insert into @results
select t.publication_id, a.subscriber_id, p.publication, s.name subscriberName, a.name agentName,
t.publisher_commit, t.distributor_commit, h.subscriber_commit,
from distribution.dbo.mstracer_tokens t with (nolock)
join distribution.dbo.mstracer_history h with (nolock)
on t.tracer_id = h.parent_tracer_id
join distribution.dbo.msPublications p with (nolock)
on t.publication_id = p.publication_id
join distribution.dbo.msDistribution_agents a with (nolock)
on h.agent_id = a.id
join master.sys.servers s with (nolock)
on a.subscriber_id = s.server_id

-- insert into main data table, avoiding duplicates
insert into tracerTokens
select r.*
from @results r
select publicationID, subscriberID, publisherCommit
from @results
publicationID, subscriberID, publisherCommit
from tracerTokens
) x
on r.publicationID = x.publicationID
and r.subscriberID = x.subscriberID
and r.publisherCommit = x.publisherCommit

-- this is a cleanup to ensure no missed data
   -- as data could have been added in a previous
   -- run which hadn't gone end to end at the time but now has
update t
set t.distributorCommit = r.distributorCommit,
t.subscriberCommit = r.subscriberCommit,
t.distributorSecs = r.distributorSecs,
t.subscriberSecs = r.subscriberSecs
from tracerTokens t
left join @results r
on t.publicationID = r.publicationID
and t.subscriberID = r.subscriberID
and t.publisherCommit = r.publisherCommit
where t.distributorCommit is null
t.subscriberCommit is null

It’s as simple as that. Now you have a table of token data which you can easily use for reporting and alerting with regard to latencies in your replicated environment.
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.