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;
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:

https://msdn.microsoft.com/en-us/library/ms189817.aspx

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.