SQL Server代理作业执行状态检查陷入无限循环的问题问询

问题背景 #

开发了一套用于检查SQL Server代理作业执行状态的流程，需求是确保指定作业当日最后一次运行已完成（状态为成功或失败），并根据运行结果决定执行下游作业或终止流程。流程原本会等待1分钟后循环检查所有指定作业，直至全部完成，但实际运行时，作业活动监视器显示所有作业已完成，查询却陷入无限循环。

原代码问题分析 #

BEGIN

    DECLARE @noofjob INT;
    DECLARE @failedjob VARCHAR(MAX);
    DECLARE @jobnames TABLE (name VARCHAR(255)); -- 存储作业名称
    DECLARE @jobname VARCHAR(MAX) = 'child_job_1,child_job_2,child_job_3,child_job_4,child_job_5';

    -- 将SQL Agent作业历史复制到临时表
        SELECT  jh.[instance_id],
        jh.[job_id],
        j.[name],
        j.[description],
        jh.[step_id],
        jh.[step_name],
        jh.[sql_message_id],
        jh.[sql_severity],
        jh.[message],
        jh.[run_status],
        CASE WHEN jh.[run_status] = 1 THEN 'Succeeded'
             WHEN jh.[run_status] = 0 THEN 'Failed'
             WHEN jh.[run_status] = 2 THEN 'Canceled'
             ELSE 'Unknown'
         END AS [run_status_description],
        jh.[run_date],
        jh.[run_time],
        msdb.dbo.agent_datetime(jh.run_date, jh.run_time) AS 'run_date_time',
        jh.[run_duration],
        jh.[operator_id_emailed],
        jh.[operator_id_netsent],
        jh.[operator_id_paged],
        jh.[retries_attempted],
        jh.[server]
   INTO #jobhistory
   FROM [msdb].[dbo].[sysjobhistory] jh
   JOIN [msdb].[dbo].[sysjobs] j 
     ON j.[job_id] = jh.[job_id]
    
    -- 将作业名称插入表变量
    INSERT INTO @jobnames(name)
    SELECT value FROM STRING_SPLIT(@jobname, ',');

    -- 统计作业数量
    SELECT @noofjob = COUNT(*) FROM @jobnames;

    WHILE 1 = 1
    BEGIN
        -- 若临时表存在则删除
        DROP TABLE IF EXISTS #lastrun;

        -- 获取最新运行信息
        SELECT name, MAX(run_date_time) AS latest_run
        INTO #lastrun
        FROM #jobhistory
        WHERE run_date_time > CAST(GETDATE() AS DATE)
        AND name IN (SELECT name FROM @jobnames)
        GROUP BY name;

        -- 检查所有作业是否在过去6小时内运行过
        IF @noofjob != (
            SELECT COUNT(DISTINCT name)
            FROM #jobhistory
            WHERE run_date_time > DATEADD(HOUR, -6, GETDATE())
            AND name IN (SELECT name FROM @jobnames)
        )
        BEGIN
            -- 等待1分钟后再次检查
            WAITFOR DELAY '00:01:00';
        END
        ELSE
        BEGIN
            -- 查找失败的作业
            SET @failedjob = (
                SELECT STRING_AGG(name, ',')
                FROM (
                    SELECT DISTINCT s.name
                    FROM #jobhistory S
                    JOIN #lastrun lr
                        ON S.name = lr.name
                        AND S.run_date_time = lr.latest_run
                    WHERE S.run_status != 1 -- '1'表示成功
                ) AS FailedJobs
            );

            -- 检查是否有失败作业
            IF LEN(@failedjob) > 0
            BEGIN
                RAISERROR ('存储过程执行因作业失败而停止: %s', 16, 1, @failedjob);
                THROW 50010, '错误', 1;
            END
            ELSE
            BEGIN
                -- 等待30秒后进行下一次检查
                WAITFOR DELAY '00:00:30';
            END

            -- 满足所有条件后退出循环
            BREAK;
        END
    END
END

导致无限循环的核心问题 #

1. 静态数据快照：流程启动时就将sysjobhistory的数据导入#jobhistory临时表，后续循环不会更新该表。如果作业是在流程启动后完成的，临时表中不会有最新的运行记录，导致一直判定作业未完成。
2. 检查条件不匹配需求：用“过去6小时内运行过”作为判断依据，但需求是“当日最后一次运行已完成”，若作业在当日早于6小时前运行完成，会被误判为未运行；同时未考虑作业是否正在运行的状态。
3. 冗余等待逻辑：成功后额外等待30秒再退出循环，属于无效操作。

优化后的解决方案 #

核心改进点 #

• 实时查询系统表，避免静态数据快照的滞后问题
• 结合sysjobactivity判断作业是否正在运行，确保状态检查准确
• 严格匹配“当日最后一次运行已完成”的需求
• 增加循环超时机制，防止极端情况下的无限循环

优化代码 #

BEGIN
    DECLARE @jobname VARCHAR(MAX) = 'child_job_1,child_job_2,child_job_3,child_job_4,child_job_5';
    DECLARE @jobnames TABLE (name VARCHAR(255));
    DECLARE @failedjobs VARCHAR(MAX);
    DECLARE @check_count INT = 0;
    DECLARE @max_checks INT = 60; -- 最多检查60次（1小时），防止无限循环
    DECLARE @current_date DATE = CAST(GETDATE() AS DATE);

    -- 拆分作业名称到表变量
    INSERT INTO @jobnames(name)
    SELECT value FROM STRING_SPLIT(@jobname, ',');

    WHILE @check_count < @max_checks
    BEGIN
        SET @check_count += 1;

        -- 检查所有指定作业的状态：当日有完成记录，且无正在运行的实例
        WITH JobStatus AS (
            SELECT
                j.name,
                -- 当日最后一次运行的状态
                MAX(CASE WHEN msdb.dbo.agent_datetime(jh.run_date, jh.run_time) >= @current_date THEN jh.run_status END) AS last_run_status,
                -- 判断是否有正在运行的作业实例
                CASE WHEN ja.start_execution_date IS NOT NULL AND ja.stop_execution_date IS NULL THEN 1 ELSE 0 END AS is_running
            FROM msdb.dbo.sysjobs j
            LEFT JOIN msdb.dbo.sysjobhistory jh 
                ON j.job_id = jh.job_id
                AND msdb.dbo.agent_datetime(jh.run_date, jh.run_time) >= @current_date
            LEFT JOIN msdb.dbo.sysjobactivity ja 
                ON j.job_id = ja.job_id
                AND ja.session_id = (SELECT TOP 1 session_id FROM msdb.dbo.syssessions ORDER BY agent_start_date DESC)
            WHERE j.name IN (SELECT name FROM @jobnames)
            GROUP BY j.name, ja.start_execution_date, ja.stop_execution_date
        )
        SELECT
            @failedjobs = STRING_AGG(name, ',')
        FROM JobStatus
        WHERE
            -- 未找到当日运行记录，或作业仍在运行
            (last_run_status IS NULL OR is_running = 1)
            -- 或最后一次运行失败/取消
            OR last_run_status NOT IN (1, 0);

        -- 所有作业已完成且无失败
        IF @failedjobs IS NULL
        BEGIN
            PRINT '所有指定作业当日运行已完成且全部成功';
            BREAK;
        END
        ELSE
        BEGIN
            -- 检查是否存在未完成（未运行/正在运行）的作业
            IF EXISTS (SELECT 1 FROM JobStatus WHERE last_run_status IS NULL OR is_running = 1)
            BEGIN
                PRINT '部分作业未完成，等待1分钟后重试...';
                WAITFOR DELAY '00:01:00';
            END
            ELSE
            BEGIN
                -- 作业已完成但存在失败/取消
                RAISERROR ('存储过程执行因作业失败/取消而停止: %s', 16, 1, @failedjobs);
                THROW 50010, '作业执行异常', 1;
            END
        END
    END

    -- 达到最大检查次数仍未完成
    IF @check_count >= @max_checks
    BEGIN
        RAISERROR ('超过最大检查次数，作业仍未全部完成', 16, 1);
        THROW 50011, '检查超时', 1;
    END
END

代码说明 #

1. 实时状态查询：每次循环直接查询sysjobs、sysjobhistory和sysjobactivity，确保获取最新的作业运行状态。
2. 状态判断逻辑：
   • last_run_status：获取作业当日最后一次运行的状态（成功1/失败0/取消2）
   • is_running：通过sysjobactivity判断作业是否正在运行（最新会话中启动未停止）
3. 超时保护：设置@max_checks限制最大检查次数，避免极端情况下的无限循环。
4. 精准分支处理：区分“作业未完成（未运行/正在运行）”和“作业已完成但失败”两种情况，分别处理。

内容的提问来源于stack exchange，提问作者pbj