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
-- 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 = 'boredDBA@theBoredDBA.com',
@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 = 'boredDBA@theBoredDBA.com',
@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.

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.