snippetsqlMinor

How do I get a run duration in minutes for each individual step in a single job?

Submitted by: @import:stackexchange-dba·Mar 10, 2026·

Viewed 0 times

eachindividualgetstepfordurationhowminutessinglejob

Problem

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

Solution

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

Code Snippets

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

Context

StackExchange Database Administrators Q#207748, answer score: 5

Revisions (0)

No revisions yet.