cancel
Showing results for 
Search instead for 
Did you mean: 

MS SQL Agent - failed jobs

MS SQL Agent - failed jobs

Template to monitor number of failed SQL Agent jobs.

Screen.png

Labels (1)
Comments

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

The query above actually returns disabled jobs, just need to add this...

----

join msdb.dbo.sysjobs sj

  on jh.job_id = sj.job_id

  AND sj.enabled = 1

join msdb.dbo.sysjobactivity ja

  on jh.job_id = ja.job_id

Very nice addition as I don't want to see disabled jobs or jobs without a schedule either!

Can someone please post the full working version of the sql query that removes the disabled tasks from the results.

Just... read above ?

I just wanted to clarify - you only need to add one line that is in bold in above snippet. All other lines have been added by the author for reference, so that you can find the right place to stick it in Hope helps someone

Great script - thanks for sharing!

Version history
Revision #:
1 of 1
Last update:
‎07-30-2015 03:20 AM
Updated by:
 
Contributors