SQL Replication Latency alert

Question

hi,

we use sql 2005 64bit (3 node cluster)

i would like to get a warning for Replication latency.

i defined Replication Warning: Transactional replication latency (Threshold: latency) for 3 min.

when the latency is over the threshold there is a yelow warning in the replication monitore but there is no alert in sql server log or windows event log (evend id 14161).

who can i get the evend id 14161 to my sql server log or windows event log ??

thanks in advance

 

 

 

 

Answer 1

Ever get an answer for this?

Answer 2

I've had problems getting this working as well.

What I do is schedule a job to inject transaction tokens into the publication and then query them and send out email if the latency of the log reader agent or the distribution agent gets too slow.

Here is the code to inject the tokens. I make this a job and schedule it every 5 minutes.

Use MyPublicatonDatabase

GO

DECLARE @out_tracer_token_id int
exec sys.sp_posttracertoken @publication = N'MyPublication',@tracer_token_id=@out_tracer_token_id out
select @out_tracer_token_id

Then I run another job which also runs every 5 minutes.

use distribution
GO
declare @subject sysname
declare @table table([name] sysname, subscriber_db sysname, publisher_commit datetime, distributor_commit datetime, [Time to Subscriber] int, subscriber_commit datetime, [Time to Publisher] int)
insert into @table
select distinct syss.name, msda.subscriber_db, publisher_commit,distributor_commit,[Time to Subscriber]=datediff(ss, publisher_commit,distributor_commit)
,subscriber_commit, datediff(ss, distributor_commit, subscriber_commit) [Time to Publisher]
from MStracer_history msth
join MSdistribution_agents msda on msth.agent_id = msda.id
join sys.servers syss on msda.subscriber_id = syss.server_id
join MStracer_tokens on msth.parent_Tracer_id=MStracer_tokens.tracer_id
where subscriber_commit >dateadd(mi,-20,getdate())
--testing latency
if exists( select  *   From @table where [time to subscriber]>600 or [Time to Publisher]>600)
begin
select @subject='Latency problem on '+@@Servername
print 'send out latency alert'
EXEC msdb.dbo.sp_send_dbmail @recipients ='[e-mail];',@subject = @subject, @body = @subject, @profile_name = 'MAIL',@body_format = 'HTML' ;
end
select  min(datediff(ss, publisher_commit,getdate()))  From @table where [time to subscriber]>600 or [Time to Publisher]>600
declare @min int
select  @min =min(datediff(ss, publisher_commit,getdate()))  From @table  
if @min>600
begin
select @subject='log reader problem on '+@@Servername
print 'send out log reader failure alert'
EXEC msdb.dbo.sp_send_dbmail @recipients ='[e-mail];',@subject = @subject, @body = @subject, @profile_name = 'MAIL',@body_format = 'HTML' ;
end
 

 

---

looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941