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 j.name   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 j.name not like 'CDW\_%' escape '\'
     and j.name not like 'dtexecRemote\_temp\_job\_%' escape '\'
     and j.name <> 'syspolicy_purge_history'
     and j.name <> 'MySpecialJob'
     and @@servername not in ('MYSERVER1', 'MYSERVER2')
order by j.name

1 comments:

giaonhan247 said...

Thanks for sharing, nice post! Post really provice useful information!

Giaonhan247 chuyên dịch vụ vận chuyển hàng đi mỹ cũng như dịch vụ ship hàng mỹ từ dịch vụ nhận mua hộ hàng mỹ từ website nổi tiếng Mỹ là mua hàng amazon về VN uy tín, giá rẻ.

Post a Comment