Please Consider Subscribing
Recently we have been facing couple of issues with regard to the way the jobs have been scheduled. To investigate this, we needed a query that can identify the execution time of jobs to see if they overlap. In addition, we needed to see which steps within the job are taking unusually long to complete as well as information about jobs that are slow overall. There are plenty of scripts already available on the Internet that gives it information but this query summarizes it in a way that the same query can be used for high level as well as detailed level information. In addition, we have used a new logic to calculate the duration rather than do a substring or character manipulation logic. The query uses sysjobsteps table to identify the execution time of the last run. By replacing sysjobsteps with sysjobhistory we would be able to achieve a historical aggregate over multiple runs with some slight manipulation to the query.
WITH cte
AS (
SELECT j.name
,j.enabled
,sjs.step_id
,sjs.step_name
,'LastState' = CASE
WHEN last_run_outcome = 0
THEN 'Failed'
WHEN last_run_outcome = 1
THEN 'Succeeded'
WHEN last_run_outcome = 2
THEN 'Retry'
WHEN last_run_outcome = 3
THEN 'Canceled'
WHEN last_run_outcome = 5
THEN 'Unknown'
ELSE 'No Status'
END
,[dbo].[agent_datetime](sjs.last_run_date, sjs.last_run_time) AS LastExecTimestamp
,sjs.last_run_duration
,'DurationInSec' = datediff(SECOND, [dbo].[agent_datetime](last_run_date, '000000'), ([dbo].[agent_datetime](last_run_date, last_run_duration)))
FROM msdb..sysjobs j
LEFT OUTER JOIN msdb..sysjobsteps sjs ON j.job_id = sjs.job_id
)
-- uncomment for detailed execution time at the step level
--select * from cte
-- uncomment for aggregated execution time at the job level
SELECT name AS JobName
,sum(DurationInSec) AS SumDurationSec
,Min(DurationInSec) AS MinDurationSec
,MAX(DurationInSec) AS MaxDurationSec
,count(*) AS NoOfSteps
FROM cte
GROUP BY name
ORDER BY sum(DurationInSec) DESC