I've setup alerts in SQL Agent for errors of severity level 15-16 for testing, and found out that these are not always triggered
To be more exact, these seem to trigger only when given error number has is_event_logged = 1
in sys.messages
This means when below commands are run:
create table -- Msg 102, Level 15, State 1 -- Incorrect syntax near 'table'
select 1/0 -- Msg 8134, Level 16, State 1 -- Divide by zero error encountered
Those errors (102, 8134) have is_event_logged = 0
and are not written to application log -> alerts for severity errors level 15-16 do not fire
When I try generate error severity 15 or 16, that has is_event_logged = 1, alerts fire just fine
raiserror(18054,16,1)
raiserror(15716,15,1)
Same with severity 17-25 errors, only ones that have is_event_logged = 1
, can fire an alert
Questions:
-
It seems not severity level of error, but actually
is_event_logged
column controls whether or not error can trigger an alert that looks for severity level errors ?
Is this intended behavior in SQL Server ? -
If I need to create custom error message as an alert for an application, using
sp_addmessage
procedure, and later setup an Alert for this error message,
@severity parameter does not really matter, I need to use @with_log parameter ? -
Is there any way to make ALL errors of given severity levels (15,16,17-25) to fire an alert, regardless of is_event_logged equals to 1 or 0 ?
I am afraid to miss something important, because there are a lot of 17-25 severity errors that are not logged to app.log -> don't fire an alert email ?
Best Answer
Yes, this is working as designed.
Looking at the docs:
The mechanism that is used for SQL Agent alerting is to read the log. Thus, if it's not logged, there is no alert.
Instead, you can use Extended Events to capture errors, even if they aren't logged. You can then write your own process to read the Extended Events file and send alerts as necessary.