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?
SELECT * 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
*** MAKE SURE YOU UNDERSTAND WHAT THIS IS DOING BEFORE RUNNING THE OUTPUT ***
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)