MS SQL Agent - failed jobs

Template to monitor number of failed SQL Agent jobs.

Screen.png

  • Thank you Petr - this was just what I was looking for.

    I took the liberty of adding some lines to the query when assigning the monitor to our servers - I wanted to make sure that I only saw warnings for failed jobs that :

    1. Was enabled

    2. Had an active schedule

    The updated statement looks like this :

    /*
    Tested on the following versions of SQL Server : 2005, 2008, 2008R2, 2012
    Jobs must meet the following criteria to be displayed:
    - must be enabled
    - must have enabled schedules
    */
    DECLARE @msg VARCHAR(max)
    DECLARE @count INT

    SET @msg = ''
    SET @count = 0

    SELECT @msg = @msg + '"' + sj.name + '", ', @count = @count + 1
    from msdb.dbo.sysjobhistory jh
    join msdb.dbo.sysjobs sj
      on jh.job_id = sj.job_id
    join msdb.dbo.sysjobactivity ja
      on jh.job_id = ja.job_id
      and msdb.dbo.agent_datetime(jh.run_date, jh.run_time) = ja.run_requested_date
    join msdb.dbo.sysjobschedules sjs
      on jh.job_id = sjs.job_id
      and next_run_date <> 0
    where jh.step_id = 0
    /*
    when jh.run_status = 0 then 'Failed'
    when jh.run_status = 1 then 'Succeeded'
    when jh.run_status = 2 then 'Retry'
    when jh.run_status = 3 then 'Canceled'
    when jh.run_status = 5 then 'Unknown'
    */
    and  jh.run_status <> 1
    group by sj.name

    IF @count = 0
    SET @msg = 'No failed jobs found'
    ELSE
    SET @msg = 'Following job(s) failed: ' + SUBSTRING(@msg, 0, LEN(@msg))

    SELECT @count as cnt, @msg as msg