To find the SQL Server Agent Jobs duration including each step in the job, the below script may be useful. Tested on SQL Server 2016.
Companies SQL Server instance may experience some performance issue due to long running jobs. As such, the primary investigation is to get the duration of jobs from SQL Server Agent. If we identity, any specific jobs running long time, then we may concentrate on that specific job information with a period of time and that history of job duration will give an idea what went wrong. It assists for further investigation.
SELECT SERVERPROPERTY('servername') as [ServerName/SQLInstanceName], SERVERPROPERTY('ComputerNamePhysicalNetBIOS') as [ComputerName/NodeName], SERVERPROPERTY('IsClustered') as IsClustered , j.name as 'JobName', s.step_id as 'Step', -- This will give RunDateTime and Duration in minutes time in each step of the particular job s.step_name as 'StepName', msdb.dbo.agent_datetime(run_date, run_time) as 'RunDateTime', -- This gives Date and Time ((run_duration/10000*3600 + (run_duration/100)%100*60 + run_duration%100 + 31 ) / 60) AS 'RunDurationMinutes', STUFF(STUFF(RIGHT(REPLICATE('0', 6) + CAST(run_time as varchar(6)), 6), 3, 0, ':'), 6, 0, ':') 'run_time', -- This gives only the time, i.e. Hours when the job ran STUFF(STUFF(STUFF(RIGHT(REPLICATE('0', 8) + CAST(run_duration as varchar(8)), 8), 3, 0, ':'), 6, 0, ':'), 9, 0, ':') 'run_duration (DD:HH:MM:SS)' FROM msdb.dbo.sysjobs j INNER JOIN msdb.dbo.sysjobsteps s ON j.job_id = s.job_id INNER JOIN msdb.dbo.sysjobhistory h ON s.job_id = h.job_id AND s.step_id = h.step_id AND h.step_id <> 0 WHERE j.enabled = 1 --Only Enabled Jobs and j.name = 'DTA Purge and Archive (BizTalkDTADb)' -- You need to comment if you want to look for all the jobs. /* and msdb.dbo.agent_datetime(run_date, run_time) BETWEEN '21/07/2017' and '21/07/2017' --Uncomment for date range queries */ order by JobName, RunDateTime desc GO