Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Replication Monitor and Alerts Expand / Collapse
Author
Message
Posted Wednesday, December 08, 2010 3:24 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, April 10, 2014 10:29 AM
Points: 202, Visits: 387
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
Post #1031747
Posted Thursday, December 09, 2010 6:18 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, April 10, 2014 10:29 AM
Points: 202, Visits: 387
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.
Post #1032410
Posted Friday, April 22, 2011 4:27 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, April 18, 2014 2:29 PM
Points: 4, Visits: 175
Nice solution Grasshopper.

Just one typo - the @metric_id is backwards -- 1 = Subscription expire, 2 = Latency
Post #1097664
Posted Wednesday, April 27, 2011 1:33 AM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Tuesday, April 08, 2014 6:25 AM
Points: 716, Visits: 755
was going to post that question so well done!
Post #1099221
Posted Thursday, April 28, 2011 12:30 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 2:08 AM
Points: 1,350, Visits: 15,200
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%'

Post #1100475
Posted Wednesday, May 04, 2011 12:53 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 3:04 PM
Points: 198, Visits: 1,116
This can also be useful.

EXEC distribution..sp_replmonitorhelpsubscription
@publisher =null,@publisher_db =null,@publication_type=0
Post #1103436
Posted Wednesday, May 11, 2011 2:26 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, April 18, 2014 12:14 PM
Points: 30, Visits: 353
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



Post #1107333
Posted Sunday, August 25, 2013 3:31 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Yesterday @ 11:53 AM
Points: 1, Visits: 339
the link doesnt work can you please send the correct link?

http://msdn.microsoft.com/en-us/library/ms176091.aspx
Post #1488253
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse