SQL - How to find currently running job steps through TSQL

Question

I am writing a query to find currently running job in SQL (I know we can view it in Job Active Monitor, but I've a need to do in TSQL). Though I can query sysjobactivity table to find currently running job, it's nowhere telling what job step is running (because my job might have more than 1 step).

Query I used:

SELECT s.name AS [JOB_NAME],
       '' AS [STEP_ID], 
       '' AS STEP_NAME, 
       'Processing' AS STATUS, 
       sja.run_requested_date AS START_TIME, 
       null AS END_DATE,
       convert(varchar, (getdate() - sja.run_requested_date), 8) AS Duration 
  FROM sysjobactivity sja, sysjobs s
 WHERE sja.job_id = s.job_id
   AND sja.run_requested_date >  getdate() - 1
   AND sja.stop_execution_date IS NULL

Please help me finding the step ID & Step name in which the job is currently progressing.

Answer 1

I think below script help to get SQL Jobs with current execution step, try this

msdb.dbo.sp_help_job @execution_status = 1

Answer 2

Try this:

SELECT distinct
      cast([sJOB].[job_id] as varchar(max)) AS execution_id 
    , [sJSTP].[step_name] AS executable_name
    , [sJOB].[name] AS package_name
    , CASE [sJSTP].[run_date]
        WHEN 0 THEN NULL
        ELSE
            CAST(
                CAST([sJSTP].[run_date] AS CHAR(8))
                + ' '
                + STUFF(
                    STUFF(RIGHT('000000' + CAST([sJSTP].[run_time] AS VARCHAR(6)),  6)
                        , 3, 0, ':')
                    , 6, 0, ':')
                AS DATETIME)
      END AS start_time,
      dateadd(ss, run_duration,   CASE [sJSTP].[run_date]
        WHEN 0 THEN NULL
        ELSE
            CAST(
                CAST([sJSTP].[run_date] AS CHAR(8))
                + ' '
                + STUFF(
                    STUFF(RIGHT('000000' + CAST([sJSTP].[run_time] AS VARCHAR(6)),  6)
                        , 3, 0, ':')
                    , 6, 0, ':')
                AS DATETIME)
      END) end_time
--    , [sJSTP].[run_duration] [looptijd in minuten]
    , CASE [sJSTP].[run_status] 
        WHEN 0 THEN 'Failed'
        WHEN 1 THEN 'Success'
        WHEN 2 THEN 'Retry'
        WHEN 3 THEN 'Cancelled'
        WHEN 5 THEN 'Unknown'
      END AS execution_result_description
FROM
    [msdb].[dbo].[sysjobhistory] AS [sJSTP]
    INNER JOIN [msdb].[dbo].[sysjobs] AS [sJOB]
        ON [sJSTP].[job_id] = [sJOB].[job_id]
    inner join [msdb].[dbo].[sysjobsteps] steps
        ON [sJSTP].[job_id] = [steps].[job_id]
where [sJSTP].[run_date] <> 0
 and CASE [sJSTP].[run_date]
        WHEN 0 THEN NULL
        ELSE
            CAST(
                CAST([sJSTP].[run_date] AS CHAR(8))
                + ' '
                + STUFF(
                    STUFF(RIGHT('000000' + CAST([sJSTP].[run_time] AS VARCHAR(6)),  6)
                        , 3, 0, ':')
                    , 6, 0, ':')
                AS DATETIME)
      END  between dateadd(hh, -20, getdate()) and getdate()
 and [sJSTP].[step_name] not in ('(Job outcome)')
 order by start_time desc

Additionally I use this query to see step result from a running SSIS job. However, this only shows you the finished steps, not the running ones. I still have to find an SQL to see the currently running step and merge it with this one.

select distinct
    cast(e.execution_id as varchar(max)),
    e.executable_name,
    e.package_name,
  CONVERT(datetime, es.start_time) AS start_time
, CONVERT(datetime, es.end_time) AS end_time
, datediff(mi, es.start_time, es.end_time) [running time]
, case es.execution_result
    when 0 then 'Success'
    when 1 then 'Failed'
    when 2 then 'Completion'
    when 3 then 'Cancelled'
    else cast(es.execution_result as varchar(max)) end  as execution_result_description
from ssisdb.catalog.executables e
left join ssisdb.catalog.executable_statistics es
on  e.executable_id = es.executable_id
 and e.execution_id = es.execution_id
order by 6 desc