When you take over a new SQL Server instance one of the things you might consider doing early on is finding SQL Agent jobs that have no email notification set up. I am of the opinion that if a job is worth creating, then it’s worth telling someone when it doesn’t do what it is supposed to do.
A quick, easy way to determine jobs that have no email operator is to connect to your SQL Server instance and run the code below.
USE msdb; GO SELECT J.name, J.notify_level_email, J.notify_email_operator_id FROM sysjobs AS J INNER JOIN sysjobschedules AS JS ON J.job_id = JS.job_id INNER JOIN sysschedules AS SS ON SS.schedule_id = JS.schedule_id WHERE J.enabled = 1 AND J.notify_level_email = 0 --never notify AND SS.enabled = 1; --it has a schedule that is enabled
For jobs that are not going to email anyone if they fail, the last two columns will return a zero. These are jobs that you will want to focus on and fix so that when they fail, someone can fix the problem.
Here is a link on all the goods available to you from dbo.sysjobs: