CODEWITHSUNDEEP
sqlt-sqlsql-job
user3138788
user3138788

Reputation: 53

SQL - How to find currently running job steps through TSQL

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.

Upvotes: 3

Views: 23304

Answers (4)

Kirubel Hailu
Kirubel Hailu

Reputation: 1

To find out which step a job is currently running, you first need to identify if the job is running. If the job is running, you can use the following query to identify the last step that successfully ran and the next step after that.

For example, let's say Job1 has 3 steps: Step1, Step2, and Step3. If the last successful step of the job is Step2, then the job must be on Step3. The query will return the name of Step3.

    DECLARE @job_name VARCHAR(128) = 'Job1';

WITH cte AS (
    SELECT ROW_NUMBER() OVER (ORDER BY run_date DESC, run_time DESC) AS row_num,
           step_name,
           run_status
    FROM msdb.dbo.sysjobhistory
    WHERE job_id = (SELECT job_id FROM msdb.dbo.sysjobs WHERE name = @job_name)
    AND run_status = 1
)
SELECT TOP 1 step_name
FROM cte
WHERE row_num = 2;

Upvotes: 0

Fabio Bruna
Fabio Bruna

Reputation: 99

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

Upvotes: 2

Rajiv Singh
Rajiv Singh

Reputation: 1078

DECLARE @StepCount INT
SELECT @StepCount = COUNT(1)
FROM msdb.dbo.sysjobsteps
WHERE job_id = '0523333-5C24-1526-8391-AA84749345666' --JobID


SELECT
         [JobName]
        ,[JobStepID]
        ,[JobStepName]
        ,[JobStepStatus]
        ,[RunDateTime]
        ,[RunDuration]
    FROM
    (
        SELECT 
                j.[name] AS [JobName]
            ,Jh.[step_id] AS [JobStepID]
            ,jh.[step_name] AS [JobStepName]
            ,CASE 
                WHEN jh.[run_status] = 0 THEN 'Failed'
                WHEN jh.[run_status] = 1 THEN 'Succeeded'
                WHEN jh.[run_status] = 2 THEN 'Retry (step only)'
                WHEN jh.[run_status] = 3 THEN 'Canceled'
                WHEN jh.[run_status] = 4 THEN 'In-progress message'
                WHEN jh.[run_status] = 5 THEN 'Unknown'
                ELSE 'N/A'
                END AS [JobStepStatus]
            ,msdb.dbo.agent_datetime(run_date, run_time) AS [RunDateTime]
            ,CAST(jh.[run_duration]/10000 AS VARCHAR)  + ':' + CAST(jh.[run_duration]/100%100 AS VARCHAR) + ':' + CAST(jh.[run_duration]%100 AS VARCHAR) AS [RunDuration]
            ,ROW_NUMBER() OVER 
            (
                PARTITION BY jh.[run_date]
                ORDER BY jh.[run_date] DESC, jh.[run_time] DESC
            ) AS [RowNumber]
        FROM 
            msdb.[dbo].[sysjobhistory] jh
            INNER JOIN msdb.[dbo].[sysjobs] j
                ON jh.[job_id] = j.[job_id]
        WHERE 
            j.[name] = 'ProcessCubes' --Job Name
            AND jh.[step_id] > 0
            AND CAST(RTRIM(run_date) AS DATE) = CAST(GETDATE() AS DATE) --Current Date
    ) A
    WHERE 
        [RowNumber] <= @StepCount
        AND [JobStepStatus] = 'Failed'

Upvotes: 1

Naveen Kumar
Naveen Kumar

Reputation: 1541

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

msdb.dbo.sp_help_job @execution_status = 1

Upvotes: 4

Related Questions