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
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.
ResolutionI 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;
ResourcesGlenn 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