0

I've recently encountered an issue with a SQL system I've inherited. Agent alerts have been configured to trigger on Severity and on Error ID. However, it seems like a given error can only trigger a single alert.

For instance if I trigger an Error 823 by pulling a disk, it generates an alert, but my "Error 823" alert then consumes the event, and my "Severity 24" alert doesn't trigger.

Ultimately the "Error 823" alert is redundant, given the fixed severity, but it poses the question - if a SQL agent alert consumes the event that triggers it, can I set a precedence so that I can determine which alert to fire if an event meets the criteria of multiple alerts? Or failing that, is it possible to configure it so that an event is not consumed by an alert?

EDIT: Some additional details - I have a few SQL agent alerts (not SQL agent jobs) configured as follows:

Event alert definition:
 Database name: <all>
 Alerts will be raised based on:
   * Error Number: 823
 Response: Notify operator(s)
 Delay between responses: 1 minute(s)

and

Event alert definition:
 Database name: <all>
 Alerts will be raised based on:
   * Severity: 24
 Response: Notify operator(s)
 Delay between responses: 1 minute(s)

I'm suspecting that what I want to achieve can't be done with such a simple tool, and I'm looking at using an external monitoring tool to better capture and disseminate these events.

  • Could you post the setup of the Agent job as it stands right now? (Config options for on failure, what the job is actually doing in terms of action etc) – George.Palacios Apr 11 '18 at 7:41
  • No, but you could write your own logic for this and have the agent jobs write to a table instead of directly sending an email. – Sean Gallardy Apr 11 '18 at 12:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.