Calculate Average Duration for Jobs Currently Running
/*
ACTIVE-JOB-AVERAGE-DURATION
*/
DECLARE @SESSION_ID INT;
SELECT
@SESSION_ID = MAX(session_id)
FROM msdb.dbo.syssessions;
;WITH CTE_JOBS_RUNNING AS (
SELECT
j.[name]
, a.[job_id]
, a.[start_execution_date]
, a.[last_executed_step_id]
, a.[last_executed_step_date]
FROM msdb.dbo.sysjobactivity a
JOIN msdb.dbo.sysjobs j ON j.job_id = a.job_id
WHERE a.session_id = @SESSION_ID
AND a.start_execution_date IS NOT NULL
AND stop_execution_date IS NULL
)
,
CTE_JOB_HISTORY AS (
SELECT
h.[job_id]
, msdb.dbo.agent_datetime(run_date, run_time) AS [start_time]
, DATEADD(
second
, [run_duration] / 10000 * 3600 + -- convert hours to seconds
([run_duration] % 10000) / 100 * 60 + -- convert minutes to seconds
([run_duration] % 10000) % 100 -- get seconds
, msdb.dbo.agent_datetime(run_date, run_time)) [end_time]
, [run_duration] / 10000 * 3600 + -- convert hours to seconds
([run_duration] % 10000) / 100 * 60 + -- convert minutes to seconds
([run_duration] % 10000) % 100 -- get seconds
AS [duration_seconds]
FROM CTE_JOBS_RUNNING a
JOIN msdb.dbo.sysjobhistory h
ON h.[job_id] = a.[job_id]
WHERE h.[step_id] = 0
AND h.[run_status] = 1
)
,
CTE_JOB_AVERAGE_DURATION AS (
SELECT
[job_id]
, COUNT(*) AS [execution_count]
, AVG([duration_seconds]) AS [average_duration]
FROM CTE_JOB_HISTORY
GROUP BY [job_id]
)
SELECT
j.*
, DATEDIFF(second, j.[start_execution_date], GETDATE()) AS [current_duration]
, d.[execution_count]
, d.[average_duration]
, DATEADD(
second
, d.[average_duration] - DATEDIFF(second, j.[start_execution_date], GETDATE())
, j.[start_execution_date]
) AS [estimated_completion]
FROM CTE_JOBS_RUNNING j
LEFT JOIN CTE_JOB_AVERAGE_DURATION d
ON d.[job_id] = j.[job_id]
Comments
Post a Comment