Alerts Not Firing

Question

##CharlieFace answer in the comments fixed my issue.

The alerts are not triggering.

Test 1:

RAISERROR('Test Alert Error', 16,1)

... I don't get any emails .

Test 2:strong text

If I test with this

RAISERROR('Test Alert Error', 16,1) WITH LOG

...I do get the emails.

Test 1 sans WITH LOG - Generates the error, but it's not automatically written to the error log unless alerts are configured to log it. Still activates alerts based on severity

Test 2 WITH LOG - Logs error to SQL Server error log: Captures the error message, severity level (16), and state (1) in the server's error log, even if alerts aren't configured.

Both of these statements should trigger the email, but only one with log does.

I have tested sending emails from dbmail and that works. When I check the alert history, the number of occurrences are not getting incremented only when I specify with log. But based on my event definition, if I run select 1/0 ; this produces a level 16 error, and I should get the email, but I don't.

Code to create alert.

USE [msdb] GO EXEC msdb.dbo.sp_update_alert @name=N'Test Alerts', @message_id=0, @severity=16, @enabled=1, @delay_between_responses=0, @include_event_description_in=1, @database_name=N'', @notification_message=N'', @event_description_keyword=N'', @performance_condition=N'', @wmi_namespace=N'', @wmi_query=N'', @job_id=N'00000000-0000-0000-0000-000000000000' GO EXEC msdb.dbo.sp_update_notification @alert_name=N'Test Alerts', @operator_name=N'alertuser', @notification_method = 1 GO

Answer 1

You need to make sure the message is being logged in the first place.

EXEC sp_altermessage @message_id = 50000, @parameter = 'WITH_LOG', @parameter_value = 'true';

Note that you should probably use THROW,where you can specify an exact error number, rather than the default 50000.

See also https://dba.stackexchange.com/questions/208361/sql-server-agent-alerts-using-severity-16