Blog

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:

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

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”:

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

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.