T-SQL script showing why SQL Server operators may not receive notifications

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.

Here are some links to the MSDB system tables referenced earlier. msdb.dbo.sysoperators, msdb.dbo.sysnotifications, and msdb.dbo.sysalerts.

---

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.

---