Technical Article

Job Run Duration


This script gives you an idea on how long the processing time is for your SQL jobs. It will depend on how many records are being retained on the sysjobhistory table (scripts defaults to the last 30 days--may not find any history at all).
One may find it useful when there are so many sql jobs and need to find some stats. More useful when combined with List SQL Server Jobs scripts.

Below are the returned column with a little explanation:
  Job Name    --  Name of the Scheduled Job
  Last_RunDate -- Last execution datetime
  Last_RunStatus -- status for the last run
  Last_RunDuration -- format is hh:mm:ss
  Avg Duration -- average exec time; format in hh:mm:ss
  Max Duration -- maximum exec time; format in hh:mm:ss
  Min Duration -- minimum exec time; format in hh:mm:ss
  From Date    -- oldest job run sampled
  Sampling     -- number of job history collected (run
                  status doesnt matter)

set nocount on
use msdb
declare @num_days int
declare @first_day datetime
,@last_day datetime
declare @first_num int

if @num_days is null
set @num_days=30

set @last_day = getdate()
set @first_day = dateadd(dd, -@num_days, @last_day)

select @first_num= cast(year(@first_day) as char(4))
+replicate('0',2-len(month(@first_day)))+ cast(month(@first_day) as varchar(2))
+replicate('0',2-len(day(@first_day)))+ cast(day(@first_day) as varchar(2))

h.step_name,  --extra
'run_date'= cast(h.run_date as varchar(8)),
'run_time'= replicate('0',6-len(h.run_time))+cast(h.run_time as varchar(6)),
'run_datetime' = left(cast(h.run_date as varchar(8)),4)+'/'
+substring(cast(h.run_date as varchar(8)),5,2)+'/'
+right(cast(h.run_date as varchar(8)),2)+' '
+left(replicate('0',6-len(h.run_time))+cast(h.run_time as varchar(6)),2)+':'
+substring(replicate('0',6-len(h.run_time))+cast(h.run_time as varchar(6)),3,2)+':'
+right(replicate('0',6-len(h.run_time))+cast(h.run_time as varchar(6)),2),
run_duration = cast(h.run_duration as varchar(20)),
run_duration_conv = case 
when (len(cast(h.run_duration as varchar(20))) < 3)  
then cast(h.run_duration as varchar(6))
WHEN (len(cast(h.run_duration as varchar(20))) = 3)  
then LEFT(cast(h.run_duration as varchar(6)),1) * 60   --min
+ RIGHT(cast(h.run_duration as varchar(6)),2)  --sec
WHEN (len(cast(h.run_duration as varchar(20))) = 4)  
then LEFT(cast(h.run_duration as varchar(6)),2) * 60   --min
+ RIGHT(cast(h.run_duration as varchar(6)),2)  --sec
WHEN (len(cast(h.run_duration as varchar(20))) >= 5)  
then (Left(cast(h.run_duration as varchar(20)),len(h.run_duration)-4)) * 3600   --hour
+(substring(cast(h.run_duration as varchar(20)) , len(h.run_duration)-3, 2)) * 60--min
+ Right(cast(h.run_duration as varchar(20)) , 2)--sec
into #temp_jobhistory
from msdb..sysjobhistory h, msdb..sysjobs j
where h.job_id=j.job_id 
and h.run_date >= @first_num
and h.step_id=0

select j.job_id
,'Sampling'=(select count(*) from #temp_jobhistory h where h.job_id=j.job_id)
,'fromRunDate' = (select min(run_date) from #temp_jobhistory h where h.job_id=j.job_id)
,'run_duration_max'=(select max(run_duration_conv) from #temp_jobhistory h where h.job_id=j.job_id)
,'run_duration_min'=(select min(run_duration_conv) from #temp_jobhistory h where h.job_id=j.job_id)
,'run_duration_avg'=(select avg(run_duration_conv) from #temp_jobhistory h where h.job_id=j.job_id)
,'Last_RunDate'=(select max(run_datetime) from #temp_jobhistory h where h.job_id=j.job_id)
,'Last_RunStatus'= null --(select run_status from #temp_jobhistory h where h.job_id=j.job_id)
into #temp_runhistory
from msdb..sysjobs j

update #temp_runhistory 
set Last_RunStatus = j.run_status
from #temp_jobhistory j
where #temp_runhistory.job_id=j.job_id
and #temp_runhistory.Last_RunDate=j.run_datetime
and j.run_datetime=(select max(run_datetime) from #temp_jobhistory j1
where j1.job_id=#temp_runhistory.job_id)

select name as 'Job Name',
'Last_RunStatus'=case Last_RunStatus
when 0 then 'Failed'
when 1 then 'Succeeded'
when 2 then 'Retry'
when 3 then 'Canceled'
when 4 then 'In progress'
'Last_RunDuration'= cast(Last_RunDuration/3600 as varchar(10))
+':'+replicate('0',2-len((Last_RunDuration % 3600)/60))+cast((Last_RunDuration % 3600)/60 as varchar(2))
+':'+replicate('0',2-len((Last_RunDuration % 3600) %60))+cast((Last_RunDuration % 3600)%60 as varchar(2)),
'Avg Duration (hh:mm:ss)' = cast(run_duration_avg/3600 as varchar(10))
+':'+replicate('0',2-len((run_duration_avg % 3600)/60))+cast((run_duration_avg % 3600)/60 as varchar(2))
+':'+replicate('0',2-len((run_duration_avg % 3600) %60))+cast((run_duration_avg % 3600)%60 as varchar(2)),
'Max Duration (hh:mm:ss)' = cast(run_duration_max/3600 as varchar(10))
+':'+replicate('0',2-len((run_duration_max % 3600)/60))+cast((run_duration_max % 3600)/60 as varchar(2))
+':'+replicate('0',2-len((run_duration_max % 3600) %60))+cast((run_duration_max % 3600)%60 as varchar(2)),
'Min Duration (hh:mm:ss)' = cast(run_duration_min/3600 as varchar(10))
+':'+replicate('0',2-len((run_duration_min % 3600)/60))+cast((run_duration_min % 3600)/60 as varchar(2))
+':'+replicate('0',2-len((run_duration_min % 3600) %60))+cast((run_duration_min % 3600)%60 as varchar(2)),
fromRunDate as 'From Date'
 from #temp_runhistory
order by name

drop table #temp_runhistory
drop table #temp_jobhistory


4.9 (10)




4.9 (10)