1

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:

enter image description here

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

1 Answer 1

5
+100

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;

Event 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: enter image description here

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;

Rethrow with log

Please also read the remarks of the SQL server alerts.

3
  • The link states: "Any SQL Server message stored in the sysmessages table sent to the Microsoft Windows application log with the indicated severity causes the alert to be sent" But docs.microsoft.com/en-us/sql/relational-databases/… states "1 = Message is event-logged when an error is raised. This is the same for all message languages within a message_id." I believe what you are saying is correct about the application log, to me there is an inconsistency in sys.messages.is_event_logged
    – SEarle1986
    Jan 10 at 22:59
  • 1
    See updated answer. Jan 11 at 10:07
  • Got it - the fact that SSMS raises a different error to that in the application log was the key. How confusing :s
    – SEarle1986
    Jan 11 at 14:39

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.