Rahman Agoro, the frontline SQL DBA

Just another WordPress.com weblog

Monitoring SQL replication using the tracer tokens TSQL dynamically

Posted by rahmanagoro on March 17, 2011

I recently wanted to monitor the latency between publisher/subscriber within a SQL transactional replication setup. This can be done quite easily within the GUI, but I wanted to automate the process and make it run as a frequent check on the database.

I have written a script and stored procedure as to how I managed to achieve this.



Author:  Rahman Agoro
Description This stored procedure will insert tracer tokens and monitor SQL replication
Date  14/02/2011
USE    declare @alarm int exec spCheckReplicationLatency '00:02:00', '%SUBSCRIBER%',@alarm output select @alarm */
CREATE procedure [dbo].[spCheckReplicationLatency] (@DelayLength char(8)= '00:00:00', @Exclusion varchar(100),@Alarm int output )

DECLARE @LoopStart int
DECLARE @LoopEnd int
DECLARE @Database varchar(50)
DECLARE @Publication varchar(100)
DECLARE @ReplicationIssue int
DECLARE @Error int
DECLARE @Seperator char(10)
DECLARE @Message varchar(1000)
DECLARE @operatorEmail varchar(100)

SET @Seperator = char(9)
--Check to see if the distribution database is present.
IF DB_ID('distribution') is null
RAISERROR ('distribution database not present',16,1)
IF OBJECT_ID('tempdb..##replicationpublicationlatency') is not null
drop table ##replicationpublicationlatency

create table ##replicationpublicationlatency
 pubID   int identity(1,1),
 databasename varchar(50),
 publication varchar(100)

insert into ##replicationpublicationlatency (databasename,publication)
select publisher_db,publication
from distribution..MSpublications

SET @LoopStart = 1
SET @LoopEnd = (SELECT COUNT(*) from ##replicationpublicationlatency)

WHILE (@LoopStart <= @LoopEnd)

 select @Database = databasename, @Publication = publication from ##replicationpublicationlatency
 WHERE pubID = @LoopStart

 SET @SQL = ''
 SET @SQL = @SQL + CHAR(10) + 'USE ' + @Database + '  exec sp_posttracertoken ' + '''' + @Publication + '''' + char(10) + char(13)

 SET @LoopStart = @LoopStart + 1
 exec master..sp_executesql @SQL
DECLARE @ReturnInfo varchar(255)
IF ISDATE('2000-01-01 ' + @DelayLength + '.000') = 0
        SELECT @ReturnInfo = 'Invalid time ' + @DelayLength
        + ',hh:mm:ss, submitted.';
        -- This PRINT statement is for testing, not use in production.
        PRINT @ReturnInfo
        GOTO ErrorHandler
    WAITFOR DELAY @DelayLength

IF OBJECT_ID('tempdb..##TokenHistory') IS NOT NULL
DROP TABLE ##TokenHistory

;with CTE_Latest_Tracer_Token_History(publication,publication_id,agent_id,agent_name,publisher_commit,distributor_commit,subscriber_commit,publisher_distributor,distributor_subscriber,Total_latency, RANKING)
SELECT a.publication,publication_id, agent_id, a.name,
       t.publisher_commit, t.distributor_commit, h.subscriber_commit,datediff(ss,t.publisher_commit,t.distributor_commit )publisher_distributor,
       datediff(ss,t.distributor_commit, h.subscriber_commit)distributor_subscriber,datediff(ss,t.publisher_commit,t.distributor_commit) + datediff(ss,t.distributor_commit, h.subscriber_commit) Total_latency,
       ROW_NUMBER() OVER (PARTITION BY publication_id, agent_id ,a.name ORDER BY t.publisher_commit DESC) AS RANKING

FROM distribution..MStracer_tokens t
JOIN distribution..MStracer_history h
ON t.tracer_id = h.parent_tracer_id
  JOIN distribution..MSdistribution_agents a
on a.id = agent_id
and a.name not like @Exclusion
select *
into ##TokenHistory
from CTE_Latest_Tracer_Token_History
where RANKING in (1)
order by publication

SET @ReplicationIssue = (select count(*) from ##TokenHistory where Total_latency is null)
SET @Message = 'Replication Latency Report ' + @@SERVERNAME
 if ISNULL(@ReplicationIssue,0) > 0         
    Set @Alarm = 1        
    print cast(@ReplicationIssue as nvarchar)+N' replication agents has errors'         
 select @operatorEmail = email_address from msdb..sysoperators where name = 'DBA'       

     EXEC msdb.dbo.sp_send_dbmail @recipients=@operatorEmail, 
     @subject = @Message, 
     @body = 'Please see attached replication latency report', 
     @body_format = 'HTML', 
     @query ='SELECT agent_name,publisher_distributor,distributor_subscriber,Total_latency FROM ##TokenHistory  ' , 
     @query_attachment_filename = 'Replication_Latency_Report.txt', 
     @attach_query_result_as_file = 'TRUE',   
     @query_result_separator = @Seperator

--WE have an issue if the issue flag  = 1
 Insert into tblReplicationLatencyLog (AgentName,LogDate,Issue)
 SELECT agent_name,GETDATE(),1
 from ##TokenHistory where Total_latency is NULL

 Set @Error = @@Error if @Error <> 0 GoTo ErrorHandler    

 Insert into tblReplicationLatencyLog (AgentName,LogDate,Issue)
 SELECT 'No Issue',GETDATE(),0

    Set @Alarm = 0         

IF  @Error <> 0 
 Print 'Error Occured !' 


In this example, I wanted to log cases where there had been replication latency issues. Calling the stored procedure also gives an option to specify a wait for period, this is because when you insert a tracer token, you need to allow for some time for the token to get onto the subscribers and report back to the publisher. This is an example of how the stored procedure is actually used.


declare @alarm int exec spCheckReplicationLatency '00:02:00', '%SUBSCRIBER%',@alarm output select @alarm 

The reason why I had the subscriber to exclude was because in my case, there was a particular subscriber across the atlantic which was like a DR database server and according to SLA’s, its allowed a few minutes lag behind, and I didnt want to raise alarms all the time due to this particular server.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: