Finding Jobs With No Email Notifications

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.  These will be jobs that could fail and they will never alert anyone. 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.

SQL Agent Jobs With No Email Notification

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;


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:

Next Steps to Take

  1. Run the above code against your SQL Servers.  If you have a Central Management Server, consider running the code there so you can see all the results across your entire environment at once.
  2. Evaluate the list of results returned and set up email operators as needed so you can be alerted if the job fails.
  3. If you have any questions, or need help with this, please contact me here.
  • […] email operators assigned. These will be jobs that no one will know whether they are working or not. This post will tell you about how to find these […]

  • […] the last post we found jobs that had no email notification. Once you find them, how do you fix this […]

  • >