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)
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.