Replication Monitor and Alerts

Question

Replication Monitor and Alerts

stakes

Ten Centuries

Points: 1376
More actions
December 8, 2010 at 3:24 am

#227875

Hi all,
I figured out that for Replication, in the Performance Monitor the Warnings (like 'latency exceeds threshold') don't trigger an Alert. To my understanding, it's there to actively monitor Replication.
If I want to be informed when the latency exceeds a certain threshold, I should Configure Alerts, which can be done from the Performance Monitor, and choose 'Replication Warning: Transactional replication latency (Threshold: latency)'. I set the Type to 'SQL Server performance condition alert'. So far, so good. But when I want to choose an Object, I can choose 'SQL Server: Replication Logreader' or 'SQL Server: Replication Dist.' and respectively the 'Logreader: Delivery Latency' or 'Distribution: Delivery Latency' Counter. There is no option for the total latency.
I want to be alerted if the total latency is above a certain threshold, just like the Warning in the Performance Monitor. How can I make that possible?
Regards
btw: I've got SQL Server 2008 PTP Transactional Replication configured

Viewing 9 posts - 1 through 8 (of 8 total)

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

stakes Ten Centuries Points: 1376 More actions · Answer 1

Got the solution!

To get the Alert working, you should leave the Type to 'SQL Server event alert', database name to <all databases> and the error number to 14161. The problem is that the Alert won't fire, because the shouldalert parameter isn't set to 1 (which to my opinion automatically should be done when configuring Replication or the Alert). To see if the shouldalert parameter is set to 0 or 1, execute:

SELECT TOP 2 [publication_id]

,[metric_id]

,[value]

,[shouldalert]

,[isenabled]

FROM [distribution].[dbo].[MSpublicationthresholds]

To change it:

exec [distribution].sys.sp_replmonitorchangepublicationthreshold

@publisher = N'STSQLSERVER3',

@publisher_db = N'AdventureWorks',

@publication = N'AWPersonPublication',

@metric_id = 2,--metric_id 1=latency, 2=subscription expire

@value = 30,--Enter Threshold here

@shouldalert = 1,

@mode = 1

As you can see, you can also change the Threshold value with this command. Another tip: the Warnings tab in the Replication Monitor doesn't refresh automatically. So do that if you want to see the result after executing the above command.

Himanshu-336561 SSC Enthusiast Points: 133 More actions · Answer 2

Nice solution Grasshopper.

Just one typo - the @metric_id is backwards -- 1 = Subscription expire, 2 = Latency

heymiky SSCarpal Tunnel Points: 4875 More actions · Answer 3

heymiky

SSCarpal Tunnel

Points: 4875

April 27, 2011 at 1:33 am

#1316392

was going to post that question 🙂 so well done!

MysteryJimbo SSC-Insane Points: 24203 More actions · Answer 4

This may be of use also.

select getdate(), [object_name],[counter_name],[instance_name],[cntr_value],[cntr_type]

from sys.dm_os_performance_counters

where [object_name] LIKE '%Replication%'

Chuck Hottle SSCarpal Tunnel Points: 4777 More actions · Answer 5

This can also be useful.

EXEC distribution..sp_replmonitorhelpsubscription

@publisher =null,@publisher_db =null,@publication_type=0

Pankaj Shere SSC Eights! Points: 907 More actions · Answer 6

This is what I use to;

SQL job which runs a script every 15 mins and checks for latency. The job does the following:

1. Post a tracer token at the publisher

2. Wait for delay for token to make it to the subscriber, delay should be more than the latency

3. Catch the tracer token at the subscriber and if the latency exceeds the limit send email/page..

This link pretty much has it covered, hope this helps 🙂

http://msdn.microsoft.com/en-us/library/ms176091.aspx

mango08 SSC Rookie Points: 37 More actions · Answer 7

the link doesnt work can you please send the correct link?

http://msdn.microsoft.com/en-us/library/ms176091.aspx

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 8

stakes (12/9/2010)
Got the solution!
To get the Alert working, you should leave the Type to 'SQL Server event alert', database name to <all databases> and the error number to 14161. The problem is that the Alert won't fire, because the shouldalert parameter isn't set to 1 (which to my opinion automatically should be done when configuring Replication or the Alert). To see if the shouldalert parameter is set to 0 or 1, execute:
SELECT TOP 2 [publication_id]
,[metric_id]
,[value]
,[shouldalert]
,[isenabled]
FROM [distribution].[dbo].[MSpublicationthresholds]
To change it:
exec [distribution].sys.sp_replmonitorchangepublicationthreshold
@publisher = N'STSQLSERVER3',
@publisher_db = N'AdventureWorks',
@publication = N'AWPersonPublication',
@metric_id = 2,--metric_id 1=latency, 2=subscription expire
@value = 30,--Enter Threshold here
@shouldalert = 1,
@mode = 1
As you can see, you can also change the Threshold value with this command. Another tip: the Warnings tab in the Replication Monitor doesn't refresh automatically. So do that if you want to see the result after executing the above command.

I was adding multiple articles at a time and I was getting errors like can't find Objects that have articles that were already replicated.

I do not want to only add one article at a time.

If replication stops for any reason I want to fire an Alert.

How can this be done?

Thanks.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/