{"id":657,"date":"2022-07-14T21:52:22","date_gmt":"2022-07-15T02:52:22","guid":{"rendered":"https:\/\/brentec.ca\/blog\/?p=657"},"modified":"2022-07-17T23:13:55","modified_gmt":"2022-07-18T04:13:55","slug":"t-sql-script-to-show-sql-server-agent-jobs-that-are-enabled-but-do-not-have-notifications","status":"publish","type":"post","link":"https:\/\/brentec.ca\/blog\/2022\/07\/t-sql-script-to-show-sql-server-agent-jobs-that-are-enabled-but-do-not-have-notifications\/","title":{"rendered":"T-SQL script to show SQL Server agent jobs that are enabled but do not have notifications"},"content":{"rendered":"

Problem<\/strong><\/span>: SQL Server Agent jobs on your server are enabled, they are scheduled, and run as planned, but do not have notifications. For mission critical jobs, not having a job notify users can be costly.<\/p>\n

Why is this a problem?<\/strong><\/span> No DBA wants to have bad optics around themselves or their team. It is crucial that all scheduled jobs have notifications configured, so the correct individual or team is aware of a job failure, success or completion in a timely manner. Whether they are ETL jobs, or backup jobs (and you should have restore jobs too; backups are only good if the data can be recovered), having notifications in place could mean the difference between having those reports available to the end customer on-time, and\/or, your backups hitting their target recovery point objectives (RPOs).<\/p>\n

The following script can be run on your SQL Server instance to determine which jobs are enabled but do not have any notifications assigned to them. Pro Tip<\/strong>: if this query returns any results, get notifications configured on them pronto! \ud83d\ude09<\/p>\n

\n
\n
\r\nselect *\r\nfrom msdb.dbo.sysjobs\r\nwhere enabled = 1\r\n  and notify_level_email = 0 \r\n  and notify_level_netsend = 0\r\n  and notify_level_page = 0\r\n  and notify_level_eventlog = 0\r\norder by [name]\r\n<\/pre>\n<\/div>\n<\/div>\n
Explanation<\/strong><\/span>: Notifications within SQL Server Agent can be accomplished by sending an operator an email<\/strong>, a page<\/strong>, or a netsend<\/strong> message (though this option doesn’t appear in my lab version of SQL Server 2019; it is a rather outdated method and some security teams have netsend blocked on their network for obvious reasons), or a combination of any of them. Additionally, SQL Server Agent can write events to the Windows Application event log and if you have additional software that scans a server’s event logs, such as Microsoft’s System Center Operations Manager (SCOM), it too can send these events via email to a user or group of users as a means of notification.<\/p>\n
External Links<\/strong><\/span>: The MSDB table\u00a0dbo.sysjobs<\/a>\u00a0stores the information for each scheduled jobs to be executed by the SQL Server Agent.<\/p>\n
Alternatively, you can use the following T-SQL to show all SQL Server Agent jobs that have at least one of the four different notification types configured.<\/p>\n
\r\nselect *\r\nfrom msdb.dbo.sysjobs\r\nwhere enabled = 1\r\n  and notify_level_email <> 0 \r\n  or notify_level_netsend <> 0\r\n  or notify_level_page <> 0\r\n  or notify_level_eventlog <> 0\r\norder by [name]\r\n<\/pre>\n
Did you find this useful? Do you have a better way to achieve the desired result using T-SQL? Let me know in the comments below. Cheers.<\/p>\n","protected":false},"excerpt":{"rendered":"
Problem: SQL Server Agent jobs on your server are enabled, they are scheduled, and run as planned, but do not have notifications. For mission critical jobs, not having a job notify users can be costly. Why is this a problem? No DBA wants to have bad optics around themselves or their team. It is crucial …<\/p>\n
  T-SQL script to show SQL Server agent jobs that are enabled but do not have 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":"","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\/657"}],"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=657"}],"version-history":[{"count":24,"href":"https:\/\/brentec.ca\/blog\/wp-json\/wp\/v2\/posts\/657\/revisions"}],"predecessor-version":[{"id":700,"href":"https:\/\/brentec.ca\/blog\/wp-json\/wp\/v2\/posts\/657\/revisions\/700"}],"wp:attachment":[{"href":"https:\/\/brentec.ca\/blog\/wp-json\/wp\/v2\/media?parent=657"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/brentec.ca\/blog\/wp-json\/wp\/v2\/categories?post=657"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/brentec.ca\/blog\/wp-json\/wp\/v2\/tags?post=657"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}