Error Severity 16 Alert being raised for events where is_event_logged = 0

Question

I have an error level 16 alert set up as follows:

USE [msdb]
GO

EXEC msdb.dbo.sp_add_alert @name=N'Error - Severity 16', 
        @message_id=0, 
        @severity=16, 
        @enabled=1, 
        @delay_between_responses=0, 
        @include_event_description_in=1, 
        @category_name=N'[Uncategorized]', 
        @job_id=N'00000000-0000-0000-0000-000000000000'

I was curious why

SELECT 1/0;

Msg 8134, Level 16, State 1, Line 17 Divide by zero error encountered.

was not raising an error but

BACKUP DATABASE MyDatabase TO DISK = 'C:\FolderThatDoesntexist'

Msg 3201, Level 16, State 1, Line 8 Cannot open backup device 'C:\FolderThatDoesntexist'. Operating system error 5(Access is denied.).

Msg 3013, Level 16, State 1, Line 8 BACKUP DATABASE is terminating abnormally.

was

This post and The blog post it references suggest that SQL Server will only raise events for errors that are logged and this can be found in sys.messages

So I queried sys.messages for these error codes:

SELECT  severity,
        message_id,
        is_event_logged
FROM    sys.messages
WHERE   language_id = 1033 AND
        message_id IN (8134,3201,3013)

but found that all three are set to 0:

Why therefore do 3201 and 3013 trigger my alert but 8134 does not?

Answer 1

Only events that are send to the application event log of windows can be used for alerting.

This can be done with RAISERROR when WITH LOG is specified see documentation

The following code will generate 2 errors, the second one will be send to event log, the first one not. The second one will trigger your alert, the first one not.

RAISERROR('Test for dba.stackexchange 1', 16, 1);
RAISERROR('Test for dba.stackexchange 2', 16, 1) WITH LOG;

Operating system errors (like access is denied, not enough disk space, ...) are logged to the application event log of windows.

Divide by zero is not logged to the application event log of windows.

This are the messages that triggers the alert for the backup error.

SELECT *
FROM sys.messages
WHERE 1=1
AND is_event_logged = 1
AND Severity = 16
AND message_id IN (3041,18204)
AND language_id = 1033

message_id	language_id	severity	is_event_logged	text
3041	1033	16	1	BACKUP failed to complete the command %.*ls. Check the backup application log for detailed messages.
18204	1033	16	1	%s: Backup device '%s' failed to %s. Operating system error %s.

Which are different messages that are displayed in SSMS, and in your query. You can test this on your alert by providing a filter. The message_id is visible in event log:

How to trigger the alert

You can wrap your code like this and rethrow the exception WITH LOG and then your alert should work.

DECLARE 
    @ErrorMessage  NVARCHAR(4000), 
    @ErrorSeverity INT, 
    @ErrorState    INT;

BEGIN TRY
    SELECT 1/0;
END TRY
BEGIN CATCH
    SELECT 
        @ErrorMessage = ERROR_MESSAGE(), 
        @ErrorSeverity = ERROR_SEVERITY(), 
        @ErrorState = ERROR_STATE();

    RAISERROR(@ErrorMessage, @ErrorSeverity, @ErrorState) WITH LOG;
END CATCH;

Please also read the remarks of the SQL server alerts.