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

CMS: List Jobs not Writing to the Windows Event Log

Using CMS, we can get a list of all the SQL Agent Jobs that aren't writing to the Windows Event Log upon failure, and set them to do so.

-- Check for Jobs that aren't writing to the Event Log. 
declare @JobName sysname

-- 0 - Never 
-- 1 - On success 
-- 2 - On failure 
-- 3 - Always 
declare @NotifyLevelEventLog int
set @NotifyLevelEventLog = 2

declare cur cursor fast_forward for
    select name
      from msdb..sysjobs
     where enabled = 1
       and notify_level_eventlog = 0
       and name <> 'syspolicy_purge_history'
       and name <> 'MySpecialJob'
       and @@servername not in ('MYSERVER1', 'MYSERVER2')
  order by name

open cur
fetch next from cur into @JobName

while @@fetch_status = 0
begin
    print 'Setting notify_level_eventlog on server ' + @@servername + ', Job "' + @JobName + 
          '" to ' + cast(@NotifyLevelEventLog as nvarchar(50))
    exec msdb..sp_update_job @job_name = @JobName, @notify_level_eventlog = @NotifyLevelEventLog
    fetch next from cur into @JobName
end

close cur
deallocate cur

Display ETA and Percent Completed for BACKUP or RESTORE Commands

This code will show you the estimated time of completion and percent completed for currently running BACKUP or RESTORE commands.

-- Shows both backups and restores.
select session_id                                      as 'spid'
     , command                                         as 'Command'
     , start_time                                      as 'StartTime'
     , (total_elapsed_time / 1000) / 60                as 'ElapsedMins'
     , cast(percent_complete as int)                   as '% Done'
     , convert(nvarchar,
               dateadd(ms, estimated_completion_time,
               current_timestamp), 120)                as 'ETA'
  from sys.dm_exec_requests
 where command like '%BACKUP DATABASE%'
    or command like '%RESTORE DATABASE%'

CMS: List all Jobs and Their Owners on All Servers

Using CMS, we can get a list of all the Jobs on all servers that are not owned the way we would like. The code below is just an example of how you can exclude by server name, job name, and owner name. SQL Server 2005+ only. Note that this example excludes the 'sa' user, as usually it's considered an allowable owner.

-- Exclude SQL Server 2000 servers, and some specific servers.
set nocount on

if '8' <> convert(nvarchar(1), serverproperty('ProductVersion'))
and @@servername not in ('MYDEVSERVER', 'MYTESTSERVER')

     select j.name   as JobName
          , l.name   as JobOwner
       from msdb..sysjobs   j
  left join sys.syslogins   l
         on j.owner_sid = l.sid
      where j.enabled = 1
        and j.name <>       'syspolicy_purge_history'
        and j.name not like 'CDW\_%'
        and j.name not like 'dtexecRemote%'
        and l.name <>       'sa'
        and l.name <>       'MYDOMAIN\MY_PREFERRED_OWNER'   -- Change this.
   order by JobName