I want to get auto email, if the replication latency count is greater than 2 min.

Question

Post reply

I want to get auto email, if the replication latency count is greater than 2 min.

MAK-1128556

SSCarpal Tunnel

Points: 4594
More actions
March 24, 2010 at 2:47 pm

#220317

I want to get auto email, if the replication latency count is greater than 2 min.
I did have this store procedure but some one can help me to get auto email.
http://blogs.mssqltips.com/blogs/chadboyd/archive/2007/10/24/monitoring-replication-latency-automatically-using-tracer-tokens-sp-replchecklatency-allpubs.aspx

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply

John Rowan SSC Guru Points: 56440 More actions · Answer 1

I believe that there's a performance counter for replication latency, correct?

If so, create an alert for that Performance Counter Object and set it to file for > 2 min.

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

MAK-1128556 SSCarpal Tunnel Points: 4594 More actions · Answer 2

Thanks for reply, can you please let me know how to do it?

John Rowan SSC Guru Points: 56440 More actions · Answer 3

Well, first you'll need to figure out which performance counter that you'd like to monitor. I'll leave that to you. There are a few perfmon counters that are related to replication latency so you can pick your flavor.

Next, in SSMS, go to SQL Server Agent, right-click on Alerts and select 'New Alert'. The New Alert editor will be displayed. Fill in the blanks. For the Alert Type, select 'SQL Server performance condition alert. This will allow you to find the performance counter in the Object/Counter drop downs. The editor is pretty self explanitory so once you get into it, you should be able to figure it out.

On the Response screen in the editor, you'll need to tell the Alert what it should do if the conditions are met. There are more options on the options page. You'll need to look at these to see if you want to take advantage of any of them.

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

Animal Magic SSChampion Points: 13964 More actions · Answer 4

replication monitor makes it slightly easier than that still. Highlight a publication, go to the warnings tab and click configure alerts. That takes you to the same screen as going through the new alert etc in SSMS but fills in the error number etc.

MAK-1128556 SSCarpal Tunnel Points: 4594 More actions · Answer 5

Thanks, i did try that, but I am getting an email in different times, but these email say lateracy is 60.00 that I set, but when i check on replication monitor it is not 60 see delay, it is from 4 see to 8 see.

Animal Magic SSChampion Points: 13964 More actions · Answer 6

There is also a quick script you can use which will also email you if the job is set to having a warning which it will be if the latency is exceeded

declare @cnt int

select @cnt = (select count(agent_name) as Affected_Agents from distribution.dbo.MSreplication_monitordata with (nolock)

where warning = 2)

if @cnt > 0

Begin

exec msdb..sp_send_dbmail @recipients = '<EnterEmail>',

@subject = 'WARNING - PERFORMANCE THRESHOLD ALERT!!!',

@query = 'set nocount ON select agent_name as Affected_Agents from distribution.dbo.MSreplication_monitordata with (nolock)

Where warning = 2',

@query_result_width = 60

End

MAK-1128556 SSCarpal Tunnel Points: 4594 More actions · Answer 7

Thanks, I change the schedule to "start automatically when SQl Server Agent start.

but never get the email even i change @query_result_width = 03. but when I force to run the job it will run just give me status of job "Succeede".

I want you get email when latency count is>60

MAK-1128556 SSCarpal Tunnel Points: 4594 More actions · Answer 8

also, why do you using Where warning = 2?

my data is always 0

Animal Magic SSChampion Points: 13964 More actions · Answer 9

MAK-1128556 (3/25/2010)
also, why do you using Where warning = 2?
my data is always 0

warning of 2 means the latency has been exceeded. if the value is 0 sql doesnt think there are any issues. in replication monitor are you seeing the little yellow symbol with a hazard sign in it? if you are then the data should show a warning of 2.

RML51 SSCarpal Tunnel Points: 4410 More actions · Answer 10

As I recall, there's was a bug introduced with SQL 2005 SP2 that forces you to manually enable the alert and set the time value for the threshold. We encountered this problem with our installation. You can use the following script to set these values:

Use DISTRIBUTION -- Name of distribution database - edit if necessary

EXECUTE sp_replmonitorchangepublicationthreshold

@publisher = 'server_name\instance_name', -- EDIT

@publisher_db = 'db_name', -- EDIT

@publication = 'publication_name', --EDIT

@thresholdmetricname = 'latency',

@value = 30, -- 30 second latency threshold

@shouldalert = 1,

@publication_type = 0 -- 0 = Transactional

Then just set the alert to email you when it fires.

http://connect.microsoft.com/SQLServer/feedback/details/342188/replication-latency-threshold-alerts-not-firing

I don't think MS ever fixed this with SP3 or any CU updates.

MAK-1128556 SSCarpal Tunnel Points: 4594 More actions · Answer 11

I never got warning other then 0, even on replication moritor say 60scc

MAK-1128556 SSCarpal Tunnel Points: 4594 More actions · Answer 12

Also, I can change the @value = 30, -- 30 second latency threshold

on replication monitor, still didnot get the email when latency in >60scc

RML51 SSCarpal Tunnel Points: 4410 More actions · Answer 13

You can't use replication monitor to change these values. It won't work if you do it that way. You have to use the script that I sent. The script also sets the shouldalert value to 1. This is critical.

-- This will display the set values

USE DISTRIBUTION -- EDIT if necessary

EXECUTE sp_replmonitorhelppublicationthresholds

@publisher = 'server_name\instance_name', --EDIT

@publisher_db = 'db_name', --EDIT

@publication = 'publication_name' --EDIT