I have covered SQL Server Alerts (Alerts are good, arent they?) on this blog before and I more recently did a post regarding Notifications (Are your Jobs talking to you) and how they should be configured. Now we need to check that these things are linked up so that when an Alert condition is met that you get the appropriate Notifications sent to Operators.
Straight into the code we need and then a review of what it does …
DECLARE @ChosenOperator SYSNAME
DECLARE @FailSafeOp TABLE
AlertFailSafeOperator NVARCHAR(255) ,
AlertNotificationMethod INT ,
AlertForwardingServer NVARCHAR(255) ,
AlertForwardingSeverity INT ,
AlertPagerToTemplate NVARCHAR(255) ,
AlertPagerCCTemplate NVARCHAR(255) ,
AlertPagerSubjectTemplate NVARCHAR(255) ,
AlertPagerSendSubjectOnly NVARCHAR(255) ,
-- Table to hold results of procedure to query server settings
INSERT INTO @FailSafeOp
EXEC MASTER.[dbo].[sp_MSgetalertinfo] @includeaddresses = 0
IF EXISTS ( SELECT 1
FROM @FailSafeOp AS fso
WHERE [fso].[AlertNULLafeOperator] IS NOT NULL )
SELECT @ChosenOperator = AlertFailSafeOperator
FROM @FailSafeOp AS fso
RAISERROR('No FailSafeOperator found. You should alter your SQL Agent settings to include one.',16,0,1) WITH NOWAIT
-- Assumes that the Operator "DBA_Team" exists and has an email address detail
SET @ChosenOperator = ISNULL(@ChosenOperator, N'DBA_Team' )
-- Output the results of whether there are Alerts with no assigned operator notification
SELECT [s].[name] ,
ISNULL([s3].[name], '| = - No operator assigned - = |') AS [Operator] ,
CASE WHEN [s3].[name] IS NULL
THEN 'EXEC [dbo].[sp_add_notification] @alert_name = N'
+ QUOTENAME([s].name, '''') + ', @operator_name = N'
+ QUOTENAME(@ChosenOperator, '''')
+ ', @notification_method = 1'
END AS [Add Notification TSQL] ,
WHEN 1 THEN 'Email'
WHEN 2 THEN 'Pager'
WHEN 3 THEN 'netsend'
WHEN 7 THEN 'All'
ELSE CONVERT(CHAR(30), [s2].[notification_method])
END AS [Notification Method]
FROM [dbo].[sysalerts] AS s
LEFT JOIN [dbo].[sysnotifications] AS s2 ON [s].[id] = [s2].[alert_id]
LEFT JOIN [dbo].[sysoperators] AS s3 ON [s2].[operator_id] = [s3].[id]
WHERE [s].[enabled] = 1
-- include this line if you want to only see problem alerts
AND [s3].[id] IS NULL
So this code creates a temporary table variable to hold operator information briefly; runs the undocumented sp_msgetalertinfo stored procedure to get failsafe operator data from the registry on the server; queries sysalerts, sysnotifications and sysoperators to check that all alerts are set to notify operators
Where there is no operator being notified then the [Add Notification TSQL] column will have the TSQL to create a notification to the failsafe operator, if there is one.
As always, understand this code, especially the way that you can’t rely on undocumented stored procedures to be consistent or reliable, before you run this on any of your servers, especially those that hold information that is precious to you.