Rotating Header Image

Jobs Jobs Jobs

If a job fails in production and nobody is there to see it, does it get noticed?

Deep huh? 🙂

Well if the job actually does something important of course it will get noticed. 

So the bigger question is:

What can you do to make sure that YOU know about it before anyone else does? 

Cause who likes surprise phone calls?

This is a multipart-answer so bare with me…

Step 1.  Setup a Policy Based Management condition to find SQL Server Agent Jobs with NO notification on Failure


Step 2.  Setup a Policy Based Management policy to check the condition created in Step 1


OK so this will do nothing more then tell you whether or not the SQL Server you are evaluating this policy against has jobs that do not have a notification on failure configured. 

What if my SQL Server Agent has 500 jobs? How do I find the ones without failure notifications configured?

Step 3. 

FROM msdb.dbo.sysjobs 
WHERE [enabled] = 1 AND notify_level_email NOT IN (1,2,3)

Fantastic 🙂 so what if you have 500 jobs and 499 of these jobs DO NOT have any notification on failure configured?

Step4.  Script to update all jobs to include a notification on failure


What this script will do is provide you with the commands to update ALL your jobs to notify BY EMAIL to the operator you provide in the @NotifyOperator variable

USE [msdb] 
DECLARE @NotifyOperator nvarchar(100) 
SELECT @NotifyOperator = 'DBA' 
SELECT 'EXEC msdb.dbo.sp_update_job @job_id=N''' + convert(nvarchar(50),job_id) + ''', @notify_level_email=2, @notify_email_operator_name=N''' + @NotifyOperator + '''' FROM msdb.dbo.sysjobs

Tweak this as you see fit:

  • Include a WHERE clause to exclude or include only certain jobs
  • Change additional notification options (page? net send? etc?)

 So there you go… you now have:

  • A check condition and policy to identify jobs that DO NOT have some level of notification
  • A script to identify which jobs those are
  • A script to blanket update all jobs to a particular notification level and settings.

Enjoy!! (Follow me on Twitter: @ColinStasiuk)

Post to Twitter Post to Delicious Post to Digg Post to StumbleUpon


  1. The display on the Note Edge consists of a sharp super AMOLED screen measuring 5.6 inches with a resolution of 1600 x 2560 pixels and a density of 524 pixels per inch.

  2. The prices are higher than the iPhone 6s and 6s Plus were when they launched (£539 and £619 respectively), but Apple has doubled the phones’ storage capacities from 16GB, 64GB, and 128GB to 32GB, 128GB and 256GB.

Leave a Reply

Twitter links powered by Tweet This v1.6.1, a WordPress plugin for Twitter.