Alerting For Replication Latency Using Tracer Tokens
Published: 30th June 2014
This is a direct follow up to my previous post on Tracer Tokens and Replication Latency.

This post will include some simple code to check for excessive latency in your replication and, if appropriate, send out an email informing you of the problem.

I’ve been in many a situation where this exact script has been invaluable, not just for general latency (because there is a built in alert which also warns of this) but for when your replication latency passes specific thresholds and also to track latency throughout the day looking for when peaks and troughs occur.

Anyay, in this example I’ll provide a proc which can be run on a schedule and provides an email if and when replication latency passes your defined levels:

create procedure replicationLatencyAlert
as
  
-- check tracer tokens have been placed in the last 5 mins
  
if not exists
   (
      
select *
      
from tracerTokens
      
where publisherCommit >= dateadd(n, -5, current_timestamp)
   )
  
begin
      
-- send email
      
exec msdb.dbo.sp_send_dbmail
              
@profile_name = 'myEmailProfile',
              
@recipients = 'boredDBA@theBoredDBA.com',
              
@subject = 'Replication Latency Warning',
              
@body = 'No tracer tokens have been placed in the last 5 minutes.'
  
end
   else
-- data exists, check the latency
  
begin
      
-- create variable table - change varchar size as required for environment
      
declare @latency table
      
(
          
id smallint identity,
          
publication varchar(30),
          
subscriberName varchar(30),
          
latency smallint
      
)
      
insert into @latency
      
select t.publication, t.subscriberName,
              
case when t.subscriberCommit is not null
                      
then (t.distributorSecs + t.subscriberSecs)
                      
else datediff(ss, t.publisherCommit, current_timestamp)
                      
end latency
      
from tracerTokens t
      
join
      
(
          
select agentName, max(publisherCommit) publisherCommit
          
from tracerTokens
          
group by agentName
      
) a
      
on t.agentName = a.agentName
      
and t.publisherCommit = a.publisherCommit
      
where case when t.subscriberCommit is not null
                  
then (t.distributorSecs + t.subscriberSecs)
                  
else datediff(ss, t.publisherCommit, current_timestamp)
                  
end >= 300    -- set to your latency threshold.  In this case 5 mins.
      
order by publication, latency desc, subscriberName

      
if scope_identity() > 0
      
begin
          
-- Currently over threshold.  Send email.
          
declare @emailSubject varchar(100),
                  
@textTitle varchar(100),
                  
@tableHTML nvarchar(max)

          
select @emailSubject = 'My Test Email',
                  
@textTitle = 'Replication Latencies in the following Publications'

          
set @tableHTML = '<html><head><style>' +
              
'td {border: solid black 1px;padding-left:5px;padding-right:5px;padding-top:1px;padding-bottom:1px;font-size:11pt;} ' +
              
'</style></head><body>' +
              
'<div style="margin-top:20px; margin-left:5px; margin-bottom:15px; font-weight:bold; font-size:1.3em; font-family:calibri;">' +
              
@textTitle + '</div>' +
              
'<div style="margin-left:50px; font-family:Calibri;"><table cellpadding=0 cellspacing=0 border=0>' +
              
'<tr bgcolor=#4b6c9e>' +
              
'<td align=center><font face="calibri" color=White><b>Publication</b></font></td>' +
              
'<td align=center><font face="calibri" color=White><b>Subscriber Name</b></font></td>' +
              
'<td align=center><font face="calibri" color=White><b>Latency</b></font></td></tr>'

          
declare @body varchar(max)
          
select @body =
          
(
              
select ROW_NUMBER() over(order by id) % 2 as TRRow,
                      
td = publication,
                      
td = subscriberName,
                      
td = latency
              
from @latency
              
order by publication, latency desc, subscriberName
              
for XML raw('tr'), elements
          
)

          
set @body = REPLACE(@body, '<td>', '<td align=center><font face="calibri">')
          
set @body = REPLACE(@body, '</td>', '</font></td>')
          
set @body = REPLACE(@body, '_x0020_', space(1))
          
set @body = Replace(@body, '_x003D_', '=')
          
set @body = Replace(@body, '<tr><TRRow>0</TRRow>', '<tr bgcolor=#F8F8FD>')
          
set @body = Replace(@body, '<tr><TRRow>1</TRRow>', '<tr bgcolor=#EEEEF4>')
          
set @body = Replace(@body, '<TRRow>0</TRRow>', '')

          
set @tableHTML = @tableHTML + @body + '</table></div></body></html>'

          
set @tableHTML = '<div style="color:Black; font-size:11pt; font-family:Calibri; width:100px;">' + @tableHTML + '</div>'

          
exec msdb.dbo.sp_send_dbmail
              
@profile_name = 'myEmailProfile',
              
@recipients = 'boredDBA@theBoredDBA.com',
              
@subject = 'Replication Latency Warning',
              
@body = @tableHTML,
              
@body_format = 'HTML'
      
end
   end
go


Okay, it may not be the best way to obtain the data, but it works and has saved me on a few occasions. Give it a go.
Comments:
NB: Comments will only appear once they have been moderated.