Wednesday, September 17, 2014

CMS: List All Jobs that Do Not Notify on Failure

Using CMS, we can get a list of all the (scheduled and enabled) Jobs on all the servers that have no email (nor page, nor netsend) notification set up for Failure. This can be useful to find Jobs that are silently failing, and even Jobs that are no longer in use.

-- Find scheduled and enabled Jobs that aren't notifying on failure. 
set nocount on

-- notify_level_email
-- 0 - Never
-- 1 - On success
-- 2 - On failure
-- 3 - Always
  select   as 'Job Name'
    from msdb..sysjobs           j
    join msdb..sysjobschedules   js
      on j.job_id = js.job_id
   where j.enabled = 1
     and j.notify_level_email   not in (2, 3)
     and j.notify_level_page    not in (2, 3)
     and j.notify_level_netsend not in (2, 3)
     and not like 'CDW\_%' escape '\'
     and not like 'dtexecRemote\_temp\_job\_%' escape '\'
     and <> 'syspolicy_purge_history'
     and <> 'MySpecialJob'
     and @@servername not in ('MYSERVER1', 'MYSERVER2')
order by


Post a Comment