SQL Server Alert not firing

When you set up SQL Server alerts on your SQL Server instance, make sure to run some tests to verify that they are actually firing when errors occur.

One way to verify that alerting is working as expected is to use RAISEERROR. Assuming you have an alert for severity 17 configured, you can use the following statement. Just remember that an actual alert will be fired:

RAISERROR('Testing Alerts!', 17,1) WITH LOG

Depending on the configuration of the alerts, you should be notified of the alert.

If you are having troubles getting error notifications, here are some things to check:

1. Is the alert for an individual error? Make sure the alert is logged. When the RAISEERROR function was used above, “WITH LOG” was specified. Without that there will be no alert. But if it is an SQL Server generated error you can check if logging is enabled or not in the column is_event_logged in sys.messages. The system stored procedure sp_altermessage can be used to change if an error is logged or not:

--TO CHECK IF LOGGING IS ENABLED
SELECT * FROM sys.messages WHERE language_id = 1033 AND message_id = nnnn

--TO ALTER THE LOGGING PARAMETER
EXEC sp_altermessage @message_id = nnnn, @parameter = 'WITH_LOG' ,@parameter_value = 'true'
GO

2. Check that the alert is enabled:

Alert Enabled

Alert Enabled


… and that there is a response configured:
Alert Response

Alert Response

3. Check that the operator is enabled and not off duty:

Operator Enabled

Operator Enabled

4. If you are using mail notification, check that database mail is configured and working by sending a test mail. Also make sure there is a Default Profile by opening the Configuration Wizard, selecting “Manage profile security”:

Database Mail Default Profile

Database Mail Default Profile

5. Check the Alert System page under SQL Server Agent Properties. Make sure the Enable mail profile checkbox is checked:

SQL Agent Properties Alert System

SQL Agent Properties Alert System

SQL Server Agent may need a restart if you make any change of the properties.

6. Look for errors in SQL Server Agent -> Error Logs.

1 comment » Write a comment

  1. Thanks Thomas! that really helped. It’s the little gotchas that keep a good DBA on their toes

Leave a Reply

Required fields are marked *.