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
-- check tracer tokens have been placed in the last 5 mins
if not exists
select *
from tracerTokens
where publisherCommit >= dateadd(n, -5, current_timestamp)
-- send email
exec msdb.dbo.sp_send_dbmail
@profile_name = 'myEmailProfile',
@recipients = '',
@subject = 'Replication Latency Warning',
@body = 'No tracer tokens have been placed in the last 5 minutes.'
-- data exists, check the latency
-- 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
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
-- 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 = '',
@subject = 'Replication Latency Warning',
@body = @tableHTML,
@body_format = 'HTML'

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.

Your SQL Trainer
Kevin Urquhart

I am a SQL Server DBA, Architect, Developer, and Trainer for SQL Training. This is my blog in which I’m simply trying to share my SQL knowledge and experiences with the world.

Kevin Urquhart
SQL Server Consultant, London



Copyright © 2018 London SQL Server Training Courses @ SQL Training | Website Design From Scott Heron