Problem: One or more of your operators are not getting notifications from SQL Server agent alerts or jobs.
Solution: This could be for a number of reasons, but more than likely it is because an operator or alert is disabled, or both. Let’s use the query below and determine what is causing the underlying problem and how to resolve them.
SELECT so.id AS [operator_id] , so.[name] AS [operator_name] , so.[enabled] AS [operator_enabled] , sa.id AS [alert_id] , sa.[name] AS [alert_name] , sa.[enabled] AS [alert_enabled] FROM msdb.dbo.sysoperators so JOIN msdb.dbo.sysnotifications sn ON so.id = sn.operator_id JOIN msdb.dbo.sysalerts sa ON sn.alert_id = sa.id WHERE so.[enabled] = 0 OR sa.[enabled] = 0 ORDER BY so.id
After running the query above, your output will be similar to the following:
Your results will vary, but what is most important to notice in the result set are the columns, operator_enabled and alert_enabled, more specifically, if any of those values read zero (0), i.e., disabled.
We can see from Figure 1 that User1 is enabled, but all of the alerts for that user are disabled, therefore that user will not receive any notifications for those named alerts. User2 on the other-hand is disabled (i.e., operator_enabled = 0), and regardless if some or all of the alerts are enabled, User2 will not receive any notifications either for any alerts whatsoever.
So, how do we resolve disabled operators or disabled alerts via T-SQL?
To update any operator within SQL Server, we use the system stored procedure sp_update_operator within the MSDB database. An example is shown below that enables a given named operator.
USE msdb GO -- replace <operator> with the name of one from your instance. EXECUTE dbo.sp_update_operator @name = N'<operator>', @enabled = 1; GO
Updating an alert within SQL Server is accomplished by using the system stored procedure, sp_update_alert, also found within the MSDB database. Shown below is an example of how to enable a given named alert.
USE MSDB GO -- replace <alert name> with name of one from your instance. EXECUTE dbo.sp_update_alert @name = N'<alert name>', @enabled = 1; GO
So the next time operators within one of your SQL Server instances are not receiving notifications, use these scripts to identify and resolve the issue.
Did you find this useful? Did it help you solve an immediate need within your SQL Server agent estate? If so, you might want to check out SQL Agent Insight, a product I’ve been working on during spare time to assist in determining problem areas of SQL Server agent instances.