Raising Errors - Testing SQL Agent Alerts

I was going to include this on my last post, a bit more on SQL alerting but thought I'd create a separate article instead. This is all about testing the SQL Agent alerts we've set up previously and how to do it via T-SQL.

If you're unlucky enough to repeatedly have errors being caught then you'll already be receiving notifications one way or another that things aren't going as they should do but if your system is relatively sound then we need a safe way to generate errors to test if our alert responses are working as they should be.

To do this we use the RAISERROR command to essentially create the same error as what our alerts are looking to handle. As an example we've created an alert for Severity 016 (Misc User Error) so to generate this error we use the following:

RAISERROR ('Test Severity 16',16,1)

In the results window we should be able to see:

Msg 50000, Level 16, State 1, Line 1

Test Severity 16

However, we won't have seen an alert generated in the error log and thus won't have been handled correctly. For that we need to add the WITH LOG command:


RAISERROR ('Test Severity 16',16,1) WITH LOG


Now we can see the error has been raised to the error log and will have been handled appropriately by whatever functionality we have assigned to the alert.



A final mention goes to some of the alerts we have set up previously, specifically the severity 20-25 range. These are considered fatal errors and upon generated the client connection is terminated, due to the high severity the RAISERROR command must be issued with the WITH LOG option or it will not work:

Msg 2754, Level 16, State 1, Line 1

Error severity levels greater than 18 can only be specified by members of the sysadmin role, using the WITH LOG option.


With the WITH LOG option:


Msg 2745, Level 16, State 2, Line 1

Process ID 62 has raised user error 50000, severity 20. SQL Server is terminating this process.

Msg 50000, Level 20, State 1, Line 1

Test Severity 20

Msg 0, Level 20, State 0, Line 0

A severe error occurred on the current command. The results, if any, should be discarded.

This is how to test alerts, obviously don't try any of the RAISERROR commands on a live production system otherwise any handling that is in place will be invoked and you could quite easily cause wide spread panic!!


More reading: https://msdn.microsoft.com/en-us/library/ms178592.aspx