SQL Server agent jobs without notification using T-SQL

This article will describe how to get a list of SQL Server agent jobs without notifications using T-SQL .

If you are managing a SQL Server environment, you must regularly use SQL Server Agent. It is the backbone of the SQL server. QL Server Agent allows DBAs to manage SQL jobs and schedule SQL tasks.

Once we configure a job, getting notified about job status on emails is essential. We may want email alerts on job failure, success, or completion. You can look at the job list and configure the email notifications.

If you have not configured a job notification, it won’t send an email. Therefore, the following query helps to find out SQL Server agent jobs without email notifications.

USE [msdb]
GO
 
SELECT 'SQL Agent jobs without notification:' AS [Message]
 
SELECT j.[name] AS [JobName]
FROM [dbo].[sysjobs] j
LEFT JOIN [dbo].[sysoperators] o 
ON (j.[notify_email_operator_id] = o.[id])
WHERE j.[enabled] = 1
   AND j.[notify_level_email] NOT IN (1, 2, 3)
GO

Here the notify_level_email value corresponds to the followings:

1 = When the job succeeds

2 = When the job fails

3 = Whenever the job completes (regardless of the job outcome)