December 9, 2010 at 6:18 am
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.
April 22, 2011 at 4:27 pm
Nice solution Grasshopper.
Just one typo - the @metric_id is backwards -- 1 = Subscription expire, 2 = Latency
April 27, 2011 at 1:33 am
was going to post that question 🙂 so well done!
April 28, 2011 at 12:30 pm
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%'
May 4, 2011 at 12:53 pm
This can also be useful.
EXEC distribution..sp_replmonitorhelpsubscription
@publisher =null,@publisher_db =null,@publication_type=0
May 11, 2011 at 2:26 pm
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 🙂
August 25, 2013 at 3:31 pm
the link doesnt work can you please send the correct link?
October 5, 2015 at 11:56 am
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/
Viewing 8 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply