SQL Agent Job Failures and Duration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

Script Library

SQL Agent Job Failures and Duration

Author

Topic

nguyen
Starting Member

8 Posts

Posted - 2003-11-06 : 18:45:29

/*
This SP has 2 functions.
a) if @method='duration' gives the average run duration in minutes for successful jobs
b) if @method='failures' displays failures/cancels/still executing jobs
It defaults to today's date. Specify @xdate for a different date
-- Louis Nguyen
*/

CREATE PROCEDURE UtilityJobsHistory
(
@method varchar(100)='duration'
,@xdate datetime=null
)
AS
set nocount on
set transaction isolation level read uncommitted

if @method='duration' begin

select @xdate=isnull(@xdate,getdate())

/*run_duration is in HHMMSS format; drop SS*/
/*run_staus: 1 complete 2 retry*/
/*step_id: 0 is final job outcome*/
/*run_date: yyyymmdd format*/

/*today's performance*/
select a.name,minutes=avg((b.run_duration / 100)/100*60 + (b.run_duration / 100)%100)
into #today
from msdb..sysjobs as a
join msdb..sysjobhistory as b
on a.job_id=b.job_id
where run_status in ('1','2') and step_id=0 and run_date =convert(varchar,@xdate,112)
group by a.name

/*7 day average performance*/
/*populate #D with dates in yyyymmdd format*/
create table #D (run_date varchar(50))
declare @idate datetime set @idate=@xdate
while @idate>dateadd(day,-7,@xdate) begin
insert into #D
select run_date=convert(varchar,@idate,112)
select @idate=dateadd(day,-1,@idate)
end

/*Avg7Days*/
select a.name,minutes=avg((b.run_duration / 100)/100*60 + (b.run_duration / 100)%100)
into #avg7Days
from msdb..sysjobs as a
join msdb..sysjobhistory as b
on a.job_id=b.job_id
join #D as c
on b.run_date = c.run_date
where run_status in ('1','2') and step_id=0
group by a.name

/*output*/
select name=cast(a.name as varchar(35)),OneDayAvg=a.minutes,SevenDayAvg=b.minutes
from #today as a
join #avg7days as b
on a.name=b.name
order by a.name

return end

if @method='failures' begin

select @xdate=isnull(@xdate,getdate())

select status=case run_status when 0 then 'FAILED' when 3 then 'CANCELED' when 4 then 'EXECUTING' end
,name=cast(a.name as varchar(35)),step_name
,time=replace(convert(varchar,@xdate,107),' ','')+' '+right('0000'+cast(b.run_time/100 as varchar),4)
,b.message
from msdb..sysjobs as a
join msdb..sysjobhistory as b
on a.job_id=b.job_id
where run_status in ('0','3','4') and run_date=convert(varchar,@xdate,112)
order by run_status,a.name

return end

nguyen
Starting Member

8 Posts

Posted - 2004-10-05 : 17:31:00

This is an updated, cleaner copy of the SP. Specify @method='duration' to see average duration in minutes. Specify @method='failures' to see job failures. Specify @xdate to see a different date.

-----------------------------------------------------
ALTER PROCEDURE UtilityJobsHistory
(
@method varchar(100)='duration',
@xdate datetime=null
)
AS
SET ANSI_WARNINGS OFF
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

IF @method='duration' begin

-- get @numdays as we have weekly jobs, adhoc jobs ..., increment @startdate by one and dec @enddate by one to get full days
DECLARE @numdays int, @startdate datetime, @enddate datetime
SELECT
@numdays=datediff(day,dateadd(day,+1,min(run_date)),dateadd(day,-1,max(run_date)))+1,
@startdate=dateadd(day,+1,min(run_date)),
@enddate=dateadd(day,-1,max(run_date))
FROM (
SELECT cast(rtrim(T1.run_date) as datetime) as run_date
FROM msdb.dbo.sysjobhistory T1
INNER JOIN msdb.dbo.sysjobs T2 ON T1.job_id = T2.job_id and step_id=0 -- step_id=0 is the final job outcome
) T

SELECT name as Job,
str(occurrences/@numDays,10,1) as RunsPerDay,
str(duration/occurrences,10,1) as MinsPerJob,
str(duration/@numDays,10,1) as MinsPerDay
FROM (
SELECT T2.name,
cast(count(*) as dec) as occurrences,
cast(sum(
(run_duration / 100)/100*60 + (run_duration / 100)%100 -- run_duration is in hhmmss crazy format
) as dec) as duration
FROM msdb.dbo.sysjobhistory T1
INNER JOIN msdb.dbo.sysjobs T2
ON T1.job_id = T2.job_id and step_id=0
and cast(rtrim(T1.run_date) as datetime) between @startdate and @enddate
GROUP BY T2.name
) T
ORDER BY MinsPerDay desc

RETURN END

IF @method='failures' BEGIN

SELECT @xdate=isnull(@xdate,getdate())

SELECT status=case run_status when 0 then 'FAILED' when 3 then 'CANCELED' end
,name=cast(a.name as varchar(35)),step_name
,time=replace(convert(varchar,@xdate,107),' ','')+' '+right('0000'+cast(b.run_time/100 as varchar),4)
,b.message
FROM msdb..sysjobs as a JOIN msdb..sysjobhistory as b
ON a.job_id=b.job_id
WHERE run_status in ('0','3') and run_date=convert(varchar,@xdate,112)
ORDER BY run_status,a.name

RETURN END

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources