Know failed and slow running jobs

The below script returns two resultsets
1. Jobs that failed today.
2. Jobs that are running slow than average execution time. The second reultset have one column Execution_Time_Normal as values “Yes” or “No”. This values is Yes for jobs that are running in normal execution time but is No for jobs that are taking 25 % time more on their average execution time. You can decide the precentage value for your jobs by replacing 25 with your value in line  “when ((today_execution_time-avg_execution_time)*100)/avg_execution_time > 25” in last SELECT statement.

 

use msdb

go

select distinct name from sysjobs inner join sysjobhistory on sysjobs.job_id = sysjobhistory.job_id

where sysjobhistory.run_date = year(getdate())*10000 + month(getdate())*100 + datepart("dd",getdate()) 

and sysjobhistory.run_status = 0

--currently  running jobs:

IF OBJECT_ID('tempdb..#tblResults') IS NOT NULL

drop table #tblResults

CREATE TABLE #tblResults 

(

job_id uniqueidentifier NOT NULL,

last_run_date int NOT NULL,

last_run_time int NOT NULL,

next_run_date int NOT NULL,

next_run_time int NOT NULL,

next_run_schedule_id int NOT NULL,

requested_to_run int NOT NULL, -- BOOL

request_source int NOT NULL,

request_source_id sysname COLLATE database_default NULL,

running int NOT NULL, -- BOOL

current_step int NOT NULL,

current_retry_attempt int NOT NULL,

job_state int NOT NULL

)

INSERT INTO #tblResults

EXEC master.dbo.xp_sqlagent_enum_jobs 1, ''

alter table #tblResults add      Avg_Execution_Time int null

alter table #tblResults add       Today_execution_time int null

go

update ttbl set ttbl.Avg_Execution_Time = ttmp.avg_time

from #tblResults ttbl inner join (select sysjobs.job_id , avg (case LEN(run_duration) 

when 3 then run_duration/100

when 4 then run_duration/100

when 5 then ((LEFT(convert(varchar(5),run_duration),1)*60) + (RIGHT(convert(varchar(5),run_duration),4)/100))

when 6 then (LEFT(run_duration,2)*60) + (RIGHT(run_duration,4)/100) end) avg_time

from sysjobs inner join sysjobhistory on sysjobs.job_id = sysjobhistory.job_id

inner join (select job_id from #tblResults where running = 1) tt on sysjobs.job_id = tt.job_id

where sysjobhistory.run_date > year(getdate())*10000 + (month(getdate())-1)*100 + datepart("dd",getdate()) 

and sysjobhistory.step_id =0

group by sysjobs.job_id ) ttmp on ttbl.job_id = ttmp.job_id

update ttbl set ttbl.Today_execution_time = 

((datepart("hh",getdate())* 60) + (datepart("n",getdate()))) -

case LEN(next_run_time) 

when 3 then next_run_time/100

when 4 then next_run_time/100

when 5 then ((LEFT(convert(varchar(5),next_run_time),1)*60) + (RIGHT(convert(varchar(5),next_run_time),4)/100))

when 6 then (LEFT(next_run_time,2)*60) + (RIGHT(next_run_time,4)/100) end

from #tblResults ttbl

where running = 1

SELECT tj.name, case 

when ((today_execution_time-avg_execution_time)*100)/avg_execution_time > 25 then 'No'

else 'Yes' end as Execution_Time_Normal,

Today_execution_time,Avg_Execution_Time,tr.next_run_date as current_run_date,tr.next_run_time as current_run_time, tr.last_run_date, tr.last_run_time,*

FROM #tblResults tr inner join msdb.dbo.sysjobs tj on tr.job_id = tj.job_id

where running = 1

order by tr.last_run_date