Daily DBA Health Check - SQL Agent Jobs failed

One of the things we DBAs look for any failed jobs. Although each failed job will send notification, at times depending on where you are working, we may or may not get failed job alerts. To double check, all jobs finished fine, run the script below to see if there are any jobs with last run status as 'failed'.

I haven't worked out a PowerShell script for this yet which will send an HTML email alert of all the jobs that failed every morning. The second part of the script is to check the jobs which are running currently. You may notice a few jobs which are not supposed to be running (for example they are nightly jobs and should have been finished when you run this script in the morning).

Run this on a center management server or as multi-server query on your registered servers.

--Check jobs with last run status as 'failed'
use msdb
go
select h.server as [Server],
j.[name] as [Name],
h.message as [Message],
h.run_date as LastRunDate, 
h.run_time as LastRunTime
from sysjobhistory h
inner join sysjobs j on h.job_id = j.job_id
where j.enabled = 1 
and h.instance_id in
(select max(h.instance_id)
from sysjobhistory h group by (h.job_id))
and h.run_status = 0

go

--Check jobs with status as 'executing'
exec msdb..sp_help_job @execution_status = 1

go