SQL Agent Queries

 -- Get jobs currently running

DECLARE @RUN_DATE DATE = GETDATE();

SELECT

j.[name]

, a.*

FROM msdb.dbo.sysjobactivity a

JOIN msdb.dbo.sysjobs j ON j.job_id = a.job_id

WHERE a.start_execution_date > @RUN_DATE

AND stop_execution_date IS NULL

ORDER BY 1

--

-- Get any completed steps for jobs currently running jobs

--

-- run_status values:

-- 0 = Failed

-- 1 = Succeeded

-- 2 = Retry

-- 3 = Canceled

-- 4 = In Progress

DECLARE 

@RUN_DATE DATE = GETDATE()

, @RUN_DATE_INT INT = FORMAT(GETDATE(), 'yyyyMMdd');

;WITH CTE_ACTIVE_JOBS AS (

SELECT

a.[job_id]

FROM msdb.dbo.sysjobactivity a

JOIN msdb.dbo.sysjobs j ON j.job_id = a.job_id

WHERE a.start_execution_date > @RUN_DATE

AND stop_execution_date IS NULL

AND last_executed_step_id IS NOT NULL

)

SELECT 

msdb.dbo.agent_datetime(run_date, run_time) 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) / 60 AS [MINUTES_DURATION] -- get seconds

, h.*

FROM msdb.dbo.sysjobhistory h

JOIN CTE_ACTIVE_JOBS a

ON a.[job_id] = h.[job_id]

WHERE h.run_date = @RUN_DATE_INT

-- get average duration (seconds) for successful executions

SELECT

[job_id]

, COUNT(*) AS [EXECUTION_COUNT]

, AVG([run_duration]) AS [AVERAGE_DURATION]

FROM msdb.dbo.sysjobhistory

WHERE [step_id] = 0

AND [run_status] = 1

GROUP BY [job_id]

ORDER BY 3 DESC