T-SQL script to show SQL Server agent jobs that are enabled but do not have notifications

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 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).

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: if this query returns any results, get notifications configured on them pronto! 😉

select *
from msdb.dbo.sysjobs
where enabled = 1
  and notify_level_email = 0 
  and notify_level_netsend = 0
  and notify_level_page = 0
  and notify_level_eventlog = 0
order by [name]

Explanation: Notifications within SQL Server Agent can be accomplished by sending an operator an email, a page, or a netsend 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.

External Links: The MSDB table dbo.sysjobs stores the information for each scheduled jobs to be executed by the SQL Server Agent.

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.

select *
from msdb.dbo.sysjobs
where enabled = 1
  and notify_level_email <> 0 
  or notify_level_netsend <> 0
  or notify_level_page <> 0
  or notify_level_eventlog <> 0
order by [name]

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.