SQL - 如何通过TSQL查找当前正在运行的作业步骤

Question

SQL - 如何通过TSQL查找当前正在运行的作业步骤

3

我正在编写一个查询，以查找SQL中当前正在运行的作业（我知道我们可以在作业活动监视器中查看它，但我需要在TSQL中执行）。尽管我可以查询sysjobactivity表以查找当前正在运行的作业，但它没有告诉我正在运行哪个作业步骤（因为我的作业可能有多个步骤）。

我使用的查询：

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

请帮我查找作业当前正在进行的步骤ID和步骤名称。

- user3138788

可能是一个重复的问题：如何确定作业的状态？ - user170442

4个回答

2

试试这个：

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

此外，我使用此查询来查看正在运行的SSIS作业的步骤结果。但是，这只显示已完成的步骤，而不是正在运行的步骤。我仍然需要找到一种SQL来查看当前正在运行的步骤并将其与此合并。

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

- Fabio Bruna

1

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'

- Rajiv Singh

0

要找出工作目前正在运行的步骤，首先需要确定该工作是否正在运行。如果工作正在运行，则可以使用以下查询来确定上一次成功运行的步骤以及其后的下一步。

例如，假设Job1有3个步骤：Step1、Step2和Step3。如果工作的最后一个成功步骤是Step2，那么工作必须在Step3上。查询将返回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;

- Kirubel Hailu

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Naveen Kumar · Accepted Answer

我认为下面的脚本可以帮助获取当前执行步骤的SQL作业，请尝试使用以下内容。

msdb.dbo.sp_help_job @execution_status = 1