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

Distribution Agent reports the transactional replication latency incorrectly Expand / Collapse
Author
Message
Posted Thursday, February 20, 2014 2:47 AM


SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Yesterday @ 10:27 AM
Points: 137, Visits: 590
Hi All,

I'm currently experiencing problems with latency alerts on my transactional replication as described in this article:-

http://support.microsoft.com/kb/2727217

However, I'm at a loss as to what is meant in the workaround "1.Create a second identical alert that detects a smaller latency value (for example, 20,000 milliseconds). "

If I try and create a second alert, say using the following script:-

EXEC msdb.dbo.sp_add_alert @name=N'Replication Warning: Workaround for inaccurate latency reports', 
@message_id=14161,
@severity=0,
@enabled=1,
@delay_between_responses=30,
@include_event_description_in=1,
@category_name=N'Replication',
@job_id=N'00000000-0000-0000-0000-000000000000' --insert job id for tracer token insertion here

I receive the following error:-

Msg 14501, Level 16, State 1, Procedure sp_add_alert_internal, Line 163
An alert ('Replication Warning: Transactional replication latency (Threshold: latency)') has already been defined on this condition.

Has anyone tried fixing this problem in this manner?

Cheers,

Matthew
Post #1543371
Posted Thursday, February 20, 2014 2:19 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Monday, July 14, 2014 2:06 PM
Points: 3,860, Visits: 7,130
In order to get around this you need to disable the existing alert. It will not allow you to have multiple alerts for the same Error Number (14161)

______________________________________________________________________________
"Never argue with an idiot; They'll drag you down to their level and beat you with experience"
Post #1543737
Posted Friday, February 21, 2014 4:01 AM


SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Yesterday @ 10:27 AM
Points: 137, Visits: 590
Hm, ok well in that case, the work around really doesn't make any sense, because it requires having two alerts set up for the same error?

"When the second alert is triggered by an incorrect latency value, the execution of this procedure will clear the problem. If the latency value is correct, the latency will continue to increase past 30,000 milliseconds. Therefore, the first alert is triggered correctly, and you receive notification of the correct latency value."

I have to admit, I'm a little confused.
Post #1543904
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse