Testing SQL Server Alerts

a.k.a. why the heck aren't my Alerts alerting?

I'm learning Power BI by building a sample dashboard for database operational analytics. One of the metrics we want to track was whether any SQL Alerts had fired in the reporting time frame.

Seems reasonable enough so I defined a few alerts on my machine and did some dumb things that should fire off the alerts. And it didn't. At first, I thought I must be looking in the wrong place but I watched profiler and the SSMS gui was calling "EXECUTE msdb.dbo.sp_help_alert" which at its core uses "msdb.dbo.sysalerts". All of that looked right but by golly the last_occurrence_date fields all showed zeros.

I took to twitter asking what else I could do to invoke errors and y'all had some great ideas

• https://twitter.com/crummel4/status/645764451724095489
• https://twitter.com/SirFisch/status/645763600116617216

but the one that kept coming up was raiserror . There I scan specify whatever severity I'd like so here's my alert tester for severity 15

DECLARE 
    @DBID int
,   @DBNAME nvarchar(128)
,   @severity int;

SELECT 
    @DBID = DB_ID()
,   @DBNAME = DB_NAME()
,   @severity = 15;

RAISERROR
(
    N'The current database ID is:%d, the database name is: %s.'
,   @severity -- Severity.
,   1 -- State.
,   @DBID -- First substitution argument.
,   @DBNAME
);

I run it, verify SSMS shows The current database ID is:23, the database name is: tempdb. Msg 50000, Level 15, State 1, Line 29 and as expected sp_help_alert shows ... 0 for occurrence for date and time? What the heck? I raised an error of the appropriate severity and had SQL Alerts defined for that severity - it's peanut butter and chocolate, they go together but this was more like mayonnaise and peanut butter.

A quick trip around the interblag raised some interesting possibilities. Maybe I needed to restart SQL Agent. Maybe I was tracking the wrong thing and some other links I had since closed but none of these bore fruit.

Resolution

I scaled up my RAISERROR call to enumerate all the severity levels just to get something to work and it wasn't until I hit 19 that I found my mistake. Error severity levels greater than 18 can only be specified by members of the sysadmin role, using the WITH LOG option.

Once I tacked on the "WITH LOG" option, my alerts began firing.

DECLARE 
    @DBID int
,   @DBNAME nvarchar(128)
,   @severity int;

SELECT 
    @DBID = DB_ID()
,   @DBNAME = DB_NAME();

DECLARE Csr CURSOR
FOR
-- Sev 20 and above breaks the current connection
SELECT TOP 20
    CAST(ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS int) AS Severity
FROM
    sys.objects AS O;

OPEN Csr;

FETCH NEXT FROM Csr
INTO @severity;

WHILE(@@FETCH_STATUS = 0)
BEGIN

    RAISERROR
    (
        N'The current database ID is:%d, the database name is: %s.'
    ,   @severity -- Severity.
    ,   1 -- State.
    ,   @DBID -- First substitution argument.
    ,   @DBNAME
    )
    WITH LOG;

    FETCH NEXT FROM Csr
    INTO @severity;
END

CLOSE Csr;
DEALLOCATE Csr;

Resources

Glenn Berry and Brent Ozar Unlimited both have great posts with some Alerts should have turned on for all of your servers.

• The Accidental DBA (Day 17 of 30): Configuring Alerts for High Severity Problems
• Configure SQL Server Alerts