{"id":1012,"date":"2022-10-31T13:55:44","date_gmt":"2022-10-31T18:55:44","guid":{"rendered":"https:\/\/brentec.ca\/blog\/?p=1012"},"modified":"2022-11-21T13:56:39","modified_gmt":"2022-11-21T18:56:39","slug":"t-sql-script-showing-why-sql-server-operators-may-not-receive-notifications","status":"publish","type":"post","link":"https:\/\/brentec.ca\/blog\/2022\/10\/t-sql-script-showing-why-sql-server-operators-may-not-receive-notifications\/","title":{"rendered":"T-SQL script showing why SQL Server operators may not receive notifications"},"content":{"rendered":"

Problem<\/strong><\/span>: One or more of your operators are not getting notifications from SQL Server agent alerts or jobs.<\/span><\/p>\n

Solution<\/strong><\/span>: 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.<\/span><\/p>\n

\r\nSELECT so.id AS [operator_id]\r\n, so.[name] AS [operator_name]\r\n, so.[enabled] AS [operator_enabled]\r\n, sa.id AS [alert_id]\r\n, sa.[name] AS [alert_name]\r\n, sa.[enabled] AS [alert_enabled]\r\nFROM msdb.dbo.sysoperators so\r\nJOIN msdb.dbo.sysnotifications sn\r\nON so.id = sn.operator_id\r\nJOIN msdb.dbo.sysalerts sa\r\nON sn.alert_id = sa.id\r\nWHERE so.[enabled] = 0\r\nOR sa.[enabled] = 0\r\nORDER BY so.id\r\n<\/pre>\n
After running the query above, your output will be similar to the following:<\/span><\/p>\n
\"\"<\/a>
 Figure 1 – Example output from our query above.<\/span><\/figcaption><\/figure>\n
Your results will vary, but what is most important to notice in the result set are the columns,\u00a0operator_enabled<\/em> and\u00a0alert_enabled<\/em>, more specifically, if any of those values read zero (0), i.e., disabled<\/strong>.<\/span><\/p>\n
We can see from Figure 1<\/strong> 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<\/em>), and regardless if some or all of the alerts are enabled, User2 will not receive any notifications either for any alerts whatsoever.<\/span><\/p>\n
So, how do we resolve disabled operators or disabled alerts via T-SQL?<\/span><\/p>\n
To update any operator within SQL Server, we use the system stored procedure sp_update_operator<\/a> within the MSDB database. An example is shown below that enables a given named operator.<\/span><\/p>\n
\r\nUSE msdb\r\nGO\r\n-- replace <operator> with the name of one from your instance.\r\nEXECUTE dbo.sp_update_operator @name = N'<operator>', @enabled = 1;\r\nGO\r\n<\/pre>\n
Updating an alert within SQL Server is accomplished by using the system stored procedure, sp_update_alert<\/a>, also found within the MSDB database. Shown below is an example of how to enable a given named alert.<\/span><\/p>\n
\r\nUSE MSDB\r\nGO\r\n-- replace <alert name> with name of one from your instance.\r\nEXECUTE dbo.sp_update_alert @name = N'<alert name>', @enabled = 1;\r\nGO\r\n<\/pre>\n
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.<\/span><\/p>\n
Here are some links to the MSDB system tables referenced earlier. msdb.dbo.sysoperators<\/a>, msdb.dbo.sysnotifications<\/a>, and msdb.dbo.sysalerts<\/a>.<\/span><\/p>\n
\n
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>, a product I\u2019ve been working on during spare time to assist in determining problem areas of SQL Server agent instances.<\/span><\/p>\n
\n","protected":false},"excerpt":{"rendered":"
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 …<\/p>\n
  T-SQL script showing why SQL Server operators may not receive notifications<\/span> Read More »<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"site-sidebar-layout":"default","site-content-layout":"default","ast-global-header-display":"","ast-main-header-display":"","ast-hfb-above-header-display":"","ast-hfb-below-header-display":"","ast-hfb-mobile-header-display":"","site-post-title":"","ast-breadcrumbs-content":"","ast-featured-img":"","footer-sml-layout":"","theme-transparent-header-meta":"default","adv-header-id-meta":"","stick-header-meta":"","header-above-stick-meta":"","header-main-stick-meta":"","header-below-stick-meta":"","footnotes":""},"categories":[3,56,45],"tags":[],"_links":{"self":[{"href":"https:\/\/brentec.ca\/blog\/wp-json\/wp\/v2\/posts\/1012"}],"collection":[{"href":"https:\/\/brentec.ca\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/brentec.ca\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/brentec.ca\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/brentec.ca\/blog\/wp-json\/wp\/v2\/comments?post=1012"}],"version-history":[{"count":32,"href":"https:\/\/brentec.ca\/blog\/wp-json\/wp\/v2\/posts\/1012\/revisions"}],"predecessor-version":[{"id":1059,"href":"https:\/\/brentec.ca\/blog\/wp-json\/wp\/v2\/posts\/1012\/revisions\/1059"}],"wp:attachment":[{"href":"https:\/\/brentec.ca\/blog\/wp-json\/wp\/v2\/media?parent=1012"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/brentec.ca\/blog\/wp-json\/wp\/v2\/categories?post=1012"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/brentec.ca\/blog\/wp-json\/wp\/v2\/tags?post=1012"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}