Sql-server – How to get a run duration in minutes for each individual step in a single Job

jobssql serversql-server-2012t-sql

The Run_Duration in sysjobhistory of the the complete job. Is there a way to get this? [msdb].[dbo].[sysjobactivity] only has the last step.

Best Answer

This should get you started...

select 
    jobs.name
    ,jobs.description
    ,steps.step_id
    ,steps.step_name
    ,steps.last_run_outcome
    ,last_run_time = stuff(stuff(right('00000' + cast(steps.last_run_time as varchar),6),3,0,':'),6,0,':')
    ,last_run_duration = stuff(stuff(right('00000' + cast(steps.last_run_duration as varchar),6),3,0,':'),6,0,':')
from [msdb].[dbo].[sysjobs] jobs
inner join [msdb].[dbo].[sysjobsteps] steps on
steps.job_id = jobs.job_id
order by jobs.job_id, steps.step_id
Related Question