May 5, 2009 at 8:40 am
I have gone in to Replication monitor and for my transactional publication I have set
Warn if latency exceeds the threshold down to 1 second. and I have a latency currently of about 2~3 seconds and when I look at All subscriptions I see my performance is critical (as expected)...
I am just using 1 second for testing right now to get the message.
However,
I can't get the Replication Warning: Transactional replication latency (Threshold: latency) alert to fire and I don't see error number 14161 in my SQL Server Error log on the publisher nor the subscriber. So I suspect that is why the alert didn't fire.
Any suggestions on how to get the alert to fire?
I am running SQL 2K5 build 4035.
I have also created a dummy alert and my operators are notified by that one.
Thanks
Henry
May 5, 2009 at 9:50 am
I believe this is still a problem in 2005.
How did you specified the new value, though Replication Monitor or on the Alert itself?
* Noel
May 5, 2009 at 10:09 am
I set it up replication monitor.
May 5, 2009 at 10:13 am
There is an entry in Connect saying that it was fixed in 2008. People with 2005 SP2 are complaining.
Your build number matches SP3 but I don't think they did anything related to this there.
http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=342188
* Noel
May 5, 2009 at 10:18 am
Yeah I saw that, good to know I am not the only one seeing this.
May 5, 2009 at 10:21 am
At my job we try to minimize reliance in SQL Server Agent and SSMS UI. So we run our own replication checks. Latency is readily available from distribution DB and to check replication we use tracer tokens or custom tables that are timestamped on primary and read at replica(s).
* Noel
May 12, 2009 at 12:57 pm
We have SQL 2005 SP3 with CU1 and this still hasn't been fixed. I looked through the CU3 release articles (which were published in April) and doesn't look like it's been fixed there either.
I wish they'd fix this. I know that it used to work, but I don't recall what release we were using at that time.
May 13, 2009 at 12:25 pm
RML51 (5/12/2009)
We have SQL 2005 SP3 with CU1 and this still hasn't been fixed. I looked through the CU3 release articles (which were published in April) and doesn't look like it's been fixed there either.I wish they'd fix this. I know that it used to work, but I don't recall what release we were using at that time.
It worked very well in SQL 2000, that's how badly Alert System has been damaged!
* Noel
May 15, 2009 at 1:11 am
Henry Treftz (5/5/2009)
Any suggestions on how to get the alert to fire?
Run sp_replmonitorchangepublicationthreshold
with @shouldalert = 1
May 18, 2009 at 7:23 am
Suresh B. (5/15/2009)
Henry Treftz (5/5/2009)
Any suggestions on how to get the alert to fire?Run sp_replmonitorchangepublicationthreshold
with @shouldalert = 1
I ran the following and it did not enable our alerts for latency. They still are not firing.
sp_replmonitorchangepublicationthreshold @publisher = 'server_name\instance_name', -- EDIT
@publisher_db = 'db_name', -- EDIT
@publication = 'pub_name', --EDIT
@thresholdmetricname = 'latency',
@shouldalert = 1,
@publication_type = 0 -- 0 = Transactional
May 18, 2009 at 11:36 am
Apparently sp_alertmessage was fixed on SP3.
I can't test it right now but it should do the 14161 Error work.
can you try:
EXEC sp_altermessage @message_id = 14161 ,@parameter = 'WITH_LOG' , @parameter_value = 'true'
* Noel
May 18, 2009 at 11:52 am
noeld (5/18/2009)
Apparently sp_alertmessage was fixed on SP3.I can't test it right now but it should do the 14161 Error work.
can you try:
EXEC sp_altermessage @message_id = 14161 ,@parameter = 'WITH_LOG' , @parameter_value = 'true'
Executed this on the distributor, where my alerts should fire. Then set my threshold low to see if error would fire. Still no luck.
May 19, 2009 at 1:04 am
RML51 (5/18/2009)
Suresh B. (5/15/2009)
Henry Treftz (5/5/2009)
Any suggestions on how to get the alert to fire?Run sp_replmonitorchangepublicationthreshold
with @shouldalert = 1
I ran the following and it did not enable our alerts for latency. They still are not firing.
sp_replmonitorchangepublicationthreshold @publisher = 'server_name\instance_name', -- EDIT
@publisher_db = 'db_name', -- EDIT
@publication = 'pub_name', --EDIT
@thresholdmetricname = 'latency',
@shouldalert = 1,
@publication_type = 0 -- 0 = Transactional
Following has worked for me:
execute sp_replmonitorhelppublicationthresholds
@publisher = '',
@publisher_db = '',
@publication = ''
execute sp_replmonitorchangepublicationthreshold
@publisher = '',
@publisher_db = '',
@publication = '',
@metric_id = 2,
@value = 180, -- I have set 3 minutes threshold
@shouldalert = 1
May 19, 2009 at 7:36 am
Thanks!
I got it to work, and I figured out why it didn't work for me the first time. Apparently, if you reset the latency threshold value from Replication Monitor it resets the @shouldalert value to 0.
On my first try, I ran this...
execute sp_replmonitorchangepublicationthreshold
@publisher = 'server_name\instance_name',
@publisher_db = 'db_name',
@publication = 'publication_name',
@thresholdmetricname = 'latency',
@shouldalert = 1,
@publication_type = 0
Then I used the Replication Monitor GUI to reset the latency threshold to 3 second in order to test to see if the alert would fire. I did not fire, because resetting the latency threshold from Replication Monitor also resets the @shouldalert back to 0.
To get to to work, you have to reset the latency threshold value and the @shouldalert at the same time. So this works...
execute sp_replmonitorchangepublicationthreshold
@publisher = 'server_name\instance_name',
@publisher_db = 'db_name',
@publication = 'publication_name',
@thresholdmetricname = 'latency',
@shouldalert = 1,
@value = 30, -- 30 second threshold
@publication_type = 0
So if you want to test to see if it will fire, you just have to rerun the above with a lower threshold @value. For example:
execute sp_replmonitorchangepublicationthreshold
@publisher = 'server_name\instance_name',
@publisher_db = 'db_name',
@publication = 'publication_name',
@thresholdmetricname = 'latency',
@shouldalert = 1,
@value = 3, -- 3 second threshold used to test
@publication_type = 0
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply