SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Replication Monitor and Alerts


Replication Monitor and Alerts

Author
Message
stakes
stakes
SSC Veteran
SSC Veteran (254 reputation)SSC Veteran (254 reputation)SSC Veteran (254 reputation)SSC Veteran (254 reputation)SSC Veteran (254 reputation)SSC Veteran (254 reputation)SSC Veteran (254 reputation)SSC Veteran (254 reputation)

Group: General Forum Members
Points: 254 Visits: 526
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
stakes
stakes
SSC Veteran
SSC Veteran (254 reputation)SSC Veteran (254 reputation)SSC Veteran (254 reputation)SSC Veteran (254 reputation)SSC Veteran (254 reputation)SSC Veteran (254 reputation)SSC Veteran (254 reputation)SSC Veteran (254 reputation)

Group: General Forum Members
Points: 254 Visits: 526
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
Himanshu-336561
Grasshopper
Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)

Group: General Forum Members
Points: 21 Visits: 285
Nice solution Grasshopper.

Just one typo - the @metric_id is backwards -- 1 = Subscription expire, 2 = Latency
heymiky
heymiky
SSC Eights!
SSC Eights! (921 reputation)SSC Eights! (921 reputation)SSC Eights! (921 reputation)SSC Eights! (921 reputation)SSC Eights! (921 reputation)SSC Eights! (921 reputation)SSC Eights! (921 reputation)SSC Eights! (921 reputation)

Group: General Forum Members
Points: 921 Visits: 845
was going to post that question :-) so well done!
MysteryJimbo
MysteryJimbo
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2223 Visits: 15344
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
Chuck Hottle
SSC-Addicted
SSC-Addicted (418 reputation)SSC-Addicted (418 reputation)SSC-Addicted (418 reputation)SSC-Addicted (418 reputation)SSC-Addicted (418 reputation)SSC-Addicted (418 reputation)SSC-Addicted (418 reputation)SSC-Addicted (418 reputation)

Group: General Forum Members
Points: 418 Visits: 1205
This can also be useful.

EXEC distribution..sp_replmonitorhelpsubscription
@publisher =null,@publisher_db =null,@publication_type=0
Pankaj Shere
Pankaj Shere
SSC Rookie
SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)

Group: General Forum Members
Points: 45 Visits: 387
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 Smile

http://msdn.microsoft.com/en-us/library/ms176091.aspx
mango08
mango08
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 404
the link doesnt work can you please send the correct link?

http://msdn.microsoft.com/en-us/library/ms176091.aspx
Welsh Corgi
Welsh Corgi
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10172 Visits: 4894
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/
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search