Duration of SQL Jobs – steps

The follownig list the duration of the steps of jobs.

For an overall duration, see http://www.cookingsql.com/2017/02/duration-of-sql-jobs/

 


SELECT TOP (100) PERCENT
 msdb.dbo.sysjobs.name AS JobName,
 msdb.dbo.sysjobsteps.step_id AS Step,
 msdb.dbo.agent_datetime(msdb.dbo.sysjobhistory.run_date,
 msdb.dbo.sysjobhistory.run_time) AS RunDateTime,
 (msdb.dbo.sysjobhistory.run_duration / 10000 * 3600 + msdb.dbo.sysjobhistory.run_duration / 100 % 100 * 60) 
                      + msdb.dbo.sysjobhistory.run_duration % 100 AS RunDurationSeconds,
 msdb.dbo.sysjobsteps.step_name AS StepName,
 msdb.dbo.sysjobsteps.command AS StepCommand, 
 msdb.dbo.sysjobsteps.subsystem AS StepType, 
 msdb.dbo.sysjobsteps.database_name AS DBName, 
 CASE SysJobSteps.last_run_outcome 
		WHEN 0 THEN 'Failed' 
		WHEN 1 THEN 'Succeeded' 
		ELSE 'Other' END AS StepResult

FROM         msdb.dbo.sysjobs 
INNER JOIN msdb.dbo.sysjobsteps ON msdb.dbo.sysjobs.job_id = msdb.dbo.sysjobsteps.job_id 
LEFT OUTER JOIN msdb.dbo.sysjobhistory ON msdb.dbo.sysjobsteps.job_id = msdb.dbo.sysjobhistory.job_id 
								AND msdb.dbo.sysjobsteps.step_id = msdb.dbo.sysjobhistory.step_id 
								AND msdb.dbo.sysjobhistory.step_id <> 0
WHERE   
    msdb.dbo.sysjobs.enabled = 1
AND (msdb.dbo.sysjobs.name NOT LIKE '%Backups.%') 
AND (msdb.dbo.sysjobs.name NOT LIKE 'syspolicy%') 
AND (msdb.dbo.agent_datetime(msdb.dbo.sysjobhistory.run_date, 0) = DATEADD(dd, - 1, CAST(GETDATE() AS date)))

ORDER BY RunDateTime


 

 

Leave a Reply

Your email address will not be published. Required fields are marked *