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.