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.


Alerting For Replication Latency Using Tracer Tokens
Published: Jan 15, 2016
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.
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.

Categories


© Copyright 2020 SQLTraining Ltd.