Click here to monitor SSC
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 (236 reputation)SSC Veteran (236 reputation)SSC Veteran (236 reputation)SSC Veteran (236 reputation)SSC Veteran (236 reputation)SSC Veteran (236 reputation)SSC Veteran (236 reputation)SSC Veteran (236 reputation)

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

Group: General Forum Members
Points: 236 Visits: 522
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
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 260
Nice solution Grasshopper.

Just one typo - the @metric_id is backwards -- 1 = Subscription expire, 2 = Latency
heymiky
heymiky
Right there with Babe
Right there with Babe (761 reputation)Right there with Babe (761 reputation)Right there with Babe (761 reputation)Right there with Babe (761 reputation)Right there with Babe (761 reputation)Right there with Babe (761 reputation)Right there with Babe (761 reputation)Right there with Babe (761 reputation)

Group: General Forum Members
Points: 761 Visits: 844
was going to post that question :-) so well done!
MysteryJimbo
MysteryJimbo
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1348 Visits: 15327
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 Veteran
SSC Veteran (212 reputation)SSC Veteran (212 reputation)SSC Veteran (212 reputation)SSC Veteran (212 reputation)SSC Veteran (212 reputation)SSC Veteran (212 reputation)SSC Veteran (212 reputation)SSC Veteran (212 reputation)

Group: General Forum Members
Points: 212 Visits: 1204
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 (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)

Group: General Forum Members
Points: 31 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 (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 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
SSCertifiable
SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)

Group: General Forum Members
Points: 5201 Visits: 4874
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