Where to find date & time of currently running SQL agent job STEP?

Question

I want to query a view or table for the currently running SQL agent job steps and when did they start.

I've tried queries below but it gives me JOB datetime rather then step datetime.

select top 100 * from msdb.dbo.sysjobsteps
select top 100 * from msdb.dbo.sysjobstepslogs
select top 100 * from msdb.dbo.sysjobhistory 
exec master.dbo.xp_sqlagent_enum_jobs 1  , garbage -- gives me currently running job step

Screen Shot of desired values below from SQL Activity Monitor.

Monah · Answer 1 · 2016-09-14T11:45:24.437

0

try the following

SELECT
    ja.job_id as JobId,
    j.name AS JobName,
    ja.start_execution_date as StartDn,      
    ISNULL(last_executed_step_id,0)+1 AS CurrentStepId
FROM msdb.dbo.sysjobactivity ja 
LEFT JOIN msdb.dbo.sysjobhistory jh ON ja.job_history_id = jh.instance_id
INNER JOIN msdb.dbo.sysjobs j ON ja.job_id = j.job_id
INNER JOIN msdb.dbo.sysjobsteps js ON ja.job_id = js.job_id AND ISNULL(ja.last_executed_step_id,0)+1 = js.step_id
INNER JOIN msdb.dbo.syssessions r ON r.session_id = ja.session_id
WHERE start_execution_date is not null
AND stop_execution_date is null;

You might check this article for further information

hope this will help you

edited Sep 14 '16 at 11:45

answered Sep 14 '16 at 11:39

Monah

6,714
6
22
52

Thank you Hadi. The query returns date for job start time and or 1st step. I want to query when either of the steps started/finished. – BI Dude Sep 14 '16 at 12:09
remove the `stop_execution_date is null` it should give you all the history – Monah Sep 14 '16 at 12:16
I have tried that as well but it gives me Job's datetime for some historic executions. – BI Dude Sep 14 '16 at 12:32

score 0 · Answer 2 · answered Jul 02 '19 at 12:35

/* Hank Freeman */
use master
go
SELECT
    ja.job_id,
    j.name AS job_name,
    ja.start_execution_date, 
    --2019-06-10 18:54:31.000
    getdate() as 'NOW',
    '0'+Cast(Datepart(hh,(getdate() -  ja.start_execution_date)) as char(2)) + ':' +
    Cast(Datepart(n,(getdate() -  ja.start_execution_date))  as char(2)) + ':'  +
    '0'+Cast(Datepart(ss,(getdate() -  ja.start_execution_date)) as char(2))    as 'Duration',
    Cast(DATEDIFF(n,ja.start_execution_date,getdate())  as char(5))     as 'Duration_Mins',
    ISNULL(last_executed_step_id,0)+1 AS cur_exec_step_id,
    Js.step_name
FROM msdb.dbo.sysjobactivity ja 
LEFT JOIN msdb.dbo.sysjobhistory jh 
    ON ja.job_history_id = jh.instance_id
JOIN msdb.dbo.sysjobs j 
    ON ja.job_id = j.job_id
JOIN msdb.dbo.sysjobsteps js
    ON ja.job_id = js.job_id
    AND ISNULL(ja.last_executed_step_id,0)+1 = js.step_id
WHERE ja.session_id = (
    SELECT TOP 1 session_id 
    FROM msdb.dbo.syssessions 
    ORDER BY session_id DESC
    )
AND start_execution_date is not null
AND stop_execution_date is null
order by 2
;

Where to find date & time of currently running SQL agent job STEP?

2 Answers2