SQL Server SQL查询作业活动监视器的状态

oyt4ldly 于 2023-01-20 发布在其他

关注(0)|答案(3)|浏览(255)

我正在尝试编写查询以查找SQL代理作业的当前状态。此字段位于作业活动监视器中的“状态”列下。请参见下面的屏幕截图：

我使用的是this site上的查询（查询也在下面），这些查询非常好，但是它返回的状态是一个已经运行的作业的状态。
有人知道查询当前状态与作业结束时的状态的方法吗？在上面的示例中，我希望返回“Executing 1（Run Query）"。“Run Query”是作业步骤1的名称。

SELECT 
    [sJOB].[job_id] AS [JobID]
    , [sJOB].[name] AS [JobName]
    , CASE 
        WHEN [sJOBH].[run_date] IS NULL OR [sJOBH].[run_time] IS NULL THEN NULL
        ELSE CAST(
                CAST([sJOBH].[run_date] AS CHAR(8))
                + ' ' 
                + STUFF(
                    STUFF(RIGHT('000000' + CAST([sJOBH].[run_time] AS VARCHAR(6)),  6)
                        , 3, 0, ':')
                    , 6, 0, ':')
                AS DATETIME)
      END AS [LastRunDateTime]
    , CASE [sJOBH].[run_status]
        WHEN 0 THEN 'Failed'
        WHEN 1 THEN 'Succeeded'
        WHEN 2 THEN 'Retry'
        WHEN 3 THEN 'Canceled'
        WHEN 4 THEN 'Running' -- In Progress
      END AS [LastRunStatus]
    , STUFF(
            STUFF(RIGHT('000000' + CAST([sJOBH].[run_duration] AS VARCHAR(6)),  6)
                , 3, 0, ':')
            , 6, 0, ':') 
        AS [LastRunDuration (HH:MM:SS)]
    , [sJOBH].[message] AS [LastRunStatusMessage]
    , CASE [sJOBSCH].[NextRunDate]
        WHEN 0 THEN NULL
        ELSE CAST(
                CAST([sJOBSCH].[NextRunDate] AS CHAR(8))
                + ' ' 
                + STUFF(
                    STUFF(RIGHT('000000' + CAST([sJOBSCH].[NextRunTime] AS VARCHAR(6)),  6)
                        , 3, 0, ':')
                    , 6, 0, ':')
                AS DATETIME)
      END AS [NextRunDateTime]
FROM 
    [msdb].[dbo].[sysjobs] (NOLOCK) AS [sJOB]
    LEFT JOIN (
                SELECT
                    [job_id]
                    , MIN([next_run_date]) AS [NextRunDate]
                    , MIN([next_run_time]) AS [NextRunTime]
                FROM [msdb].[dbo].[sysjobschedules] (NOLOCK)
                GROUP BY [job_id]
            ) AS [sJOBSCH]
        ON [sJOB].[job_id] = [sJOBSCH].[job_id]
    LEFT JOIN (
                SELECT 
                    [job_id]
                    , [run_date]
                    , [run_time]
                    , [run_status]
                    , [run_duration]
                    , [message]
                    , ROW_NUMBER() OVER (
                                            PARTITION BY [job_id] 
                                            ORDER BY [run_date] DESC, [run_time] DESC
                      ) AS RowNumber
                FROM [msdb].[dbo].[sysjobhistory] (NOLOCK)
                WHERE [step_id] = 0
            ) AS [sJOBH]
        ON [sJOB].[job_id] = [sJOBH].[job_id]
        AND [sJOBH].[RowNumber] = 1
WHERE [sJOB].[job_id] = '527BA180-A5D9-4492-98F0-705889EBCFC4'
ORDER BY [JobName]

sql-server

来源：https://stackoverflow.com/questions/39152478/sql-querying-job-activity-monitor-for-status

3条答案

按热度按时间

eoxn13cs1#

保存状态信息的表实际上是一个名为xp_sqlagent_enum_jobs的扩展存储过程。它通过sp_help_job（文档号here）进行访问。如果不带参数调用sp_help_job，它将包括current_execution_status和current_execution_step列，这是您要查找的列。
如果您对sp_help_job进行一些深入研究，您会发现xp_sqlagent_enum_jobs是使用以下参数调用的：

IF ((@@microsoftversion / 0x01000000) >= 8) -- SQL Server 8.0 or greater
    INSERT INTO @xp_results
    EXECUTE master.dbo.xp_sqlagent_enum_jobs @can_see_all_running_jobs, @job_owner, @job_id
ELSE
    INSERT INTO @xp_results
    EXECUTE master.dbo.xp_sqlagent_enum_jobs @can_see_all_running_jobs, @job_owner

您还可以进一步复制临时表的结构并自己调用它，这样您就可以挑选结果了：

declare @job_name sysname = 'fill in your jobname here';
declare @job_id uniqueidentifier = (select top 1 job_id from msdb..sysjobs where name = @job_name);
declare @job_owner sysname = (SELECT SUSER_SNAME());
declare @xp_results TABLE (
    job_id                UNIQUEIDENTIFIER NOT NULL,
    last_run_date         INT              NOT NULL,
    last_run_time         INT              NOT NULL,
    next_run_date         INT              NOT NULL,
    next_run_time         INT              NOT NULL,
    next_run_schedule_id  INT              NOT NULL,
    requested_to_run      INT              NOT NULL, -- BOOL
    request_source        INT              NOT NULL,
    request_source_id     sysname          COLLATE database_default NULL,
    running               INT              NOT NULL, -- BOOL
    current_step          INT              NOT NULL,
    current_retry_attempt INT              NOT NULL,
    job_state             INT              NOT NULL);

INSERT INTO @xp_results
    EXECUTE master.dbo.xp_sqlagent_enum_jobs 1, @job_owner, @job_id

SELECT sj.name,
    case xpr.job_state when 1 then 'Executing: ' + cast(sjs.step_id as nvarchar(2)) + ' (' + sjs.step_name + ')'
        when 2  then 'Waiting for thread'
        when 3 then 'Between retries'
        when 4  then 'Idle'
        when 5  then 'Suspended'
        when 7  then 'Performing completion actions'
    end as [status]
FROM @xp_results                          xpr
inner join msdb..sysjobs sj on xpr.job_id = sj.job_id
LEFT OUTER JOIN msdb.dbo.sysjobsteps sjs ON ((xpr.job_id = sjs.job_id) AND (xpr.current_step = sjs.step_id)),
    msdb.dbo.sysjobs_view                sjv
WHERE (sjv.job_id = xpr.job_id)

请注意，如果只将@job_name保留为空，则它将针对所有作业返回。

赞(0）回复(0）举报 2023-01-20

ve7v8dk22#

我通常使用此查询来检查作业运行状态，您正在查看此查询吗？它也来自msdn

SELECT sj.Name, 
CASE
    WHEN sja.start_execution_date IS NULL THEN 'Not running'
    WHEN sja.start_execution_date IS NOT NULL AND sja.stop_execution_date IS NULL THEN 'Running'
    WHEN sja.start_execution_date IS NOT NULL AND sja.stop_execution_date IS NOT NULL THEN 'Not running'
END AS 'RunStatus'
FROM msdb.dbo.sysjobs sj
JOIN msdb.dbo.sysjobactivity sja
ON sj.job_id = sja.job_id
WHERE session_id = (
SELECT MAX(session_id) FROM msdb.dbo.sysjobactivity);

赞(0）回复(0）举报 2023-01-20

nue99wik3#

使用msdb执行dbo. sp帮助作业
execution_status是SP中的一个参数。
数值描述