Twitter

Follow SQLQuill on Twitter

Subscribe

MN Pass

How to: Find the Job Step Duration History of a Scheduled Job – TSQL Style

I’ve got a new step in a job that I’m concerned about the duration.  Here’s a script to get the duration of a specific step of a job.  Note I found that whole conversion of the Run_DateTime column from Ramblings of a DBA – Tara Kizer – kudos, Tara.

Declare @JobName varchar(255),
@StepName varchar(255)
Set @JobName = '<<Enter Job Name Here>>'
Set @StepName = '<<Enter Job Step Name Here>>'

Select top 1000 run_datetime = CONVERT(DATETIME, RTRIM(run_date)) +
(run_time * 9 + run_time % 10000 * 6 + run_time % 100 * 10) / 216e4,
run_duration = Left(RIGHT('000000' + CONVERT(varchar(6), run_duration), 6), 2)+':'
+ Substring(RIGHT('000000' + CONVERT(varchar(6), run_duration), 6), 3, 2)+':'
+ Right('00' + CONVERT(varchar(6), run_duration), 2),
SJ.name,
SJH.step_name
from msdb.dbo.sysjobs SJ
Join msdb.dbo.sysjobhistory SJH on SJH.job_id = SJ.job_id
where SJ.name = @JobName
and SJH.step_name = @StepName
Order by run_datetime desc  --Use this to find the most recent
--Order by run_duration desc  --Use this to find the slowest