You need to enable JavaScript to run this app. SQL Server代理作业连续失败时@maxDate变量的动态设置方法
优惠活动
大模型
产品
解决方案
定价
更多

SQL Server代理作业连续失败时@maxDate变量的动态设置方法

SQL Server作业连续失败天数自适应的@maxDate计算方案

核心逻辑设计

  • 保留原有基础规则:无失败场景下,@maxDate为当前日期减1个月再减1天,对应基础减天数为1
  • 连续失败计数规则:仅统计昨天及之前的作业运行记录,从昨天向前回溯,直到遇到第一条作业第一步(step_id=1)运行成功的记录,中间的总天数即为连续失败天数
  • 最终计算公式:@maxDate = DATEADD(dd, -(1 + 连续失败天数), DATEADD(mm, -1, GETDATE()))
  • 容错限制:设置最大回溯天数阈值,避免作业长期失败时无限回溯补数,可根据业务数据周期调整该阈值

完整存储过程实现代码

CREATE PROCEDURE usp_UpsertData
AS
BEGIN
    BEGIN TRY
        BEGIN TRAN
            DECLARE 
                @maxDate DATE,
                @Yesterday DATE = DATEADD(dd, -1, CAST(GETDATE() AS DATE)), -- 仅统计到昨天的运行状态,当日正在执行的作业不纳入统计
                @LastSuccessDate DATE,
                @FailedDays INT = 0,
                @MaxBacktrackDays INT = 30; -- 最大连续失败回溯天数,可按需调整(如只需补7天则设为7)
            
            -- 查询指定作业第一步最近一次成功运行的日期
            SELECT TOP 1 
                @LastSuccessDate = CAST(CONVERT(VARCHAR(8), h.run_date, 112) AS DATE)
            FROM msdb.dbo.sysjobhistory h
            INNER JOIN msdb.dbo.sysjobs s
                ON h.job_id = s.job_id
            WHERE 
                s.name = 'LoadDataIntoSQLTable'
                AND h.step_id = 1 -- 与原有逻辑保持一致,仅判断第一步运行状态
                AND h.run_status = 1 -- 1代表作业运行成功
                AND CAST(CONVERT(VARCHAR(8), h.run_date, 112) AS DATE) <= @Yesterday
            ORDER BY h.run_date DESC; -- 取最新的成功运行记录
            
            -- 计算连续失败天数
            SET @FailedDays = CASE
                -- 从未查询到成功记录时,取最大回溯天数
                WHEN @LastSuccessDate IS NULL THEN @MaxBacktrackDays
                ELSE 
                    -- 避免实际失败天数超过最大回溯阈值
                    CASE 
                        WHEN DATEDIFF(dd, @LastSuccessDate, @Yesterday) > @MaxBacktrackDays 
                        THEN @MaxBacktrackDays
                        ELSE DATEDIFF(dd, @LastSuccessDate, @Yesterday)
                    END
            END;
            
            -- 动态生成maxDate
            SET @maxDate = DATEADD(dd, -(1 + @FailedDays), DATEADD(mm, -1, GETDATE()))
        COMMIT TRAN
    END TRY
    BEGIN CATCH
        DECLARE @errorMsg NVARCHAR(MAX),
                @errorSeverity INT
        IF @@TRANCOUNT > 0
            ROLLBACK TRANSACTION
        SELECT @errorMsg = ERROR_MESSAGE(), @errorSeverity = ERROR_SEVERITY()
        RAISERROR(@errorMsg, @errorSeverity, 1)
    END CATCH
END

逻辑验证示例

  • 场景1:昨日作业运行成功,连续失败天数为0,11月24日运行时@maxDate为10月23日,符合原有基础规则
  • 场景2:昨日失败、前日成功,连续失败天数为1,@maxDate为10月22日,兼容原有单天失败的适配逻辑
  • 场景3:昨日、前日连续失败、大前天成功,连续失败天数为2,@maxDate为10月21日,满足多天连续失败的适配需求

可选调整:如果需要将未运行的日期排除在失败计数外,可额外添加日期连续性校验逻辑,常规每日运行的作业场景下,未运行等同于数据未加载,计入补数逻辑更合理。

内容的提问来源于stack exchange,提问作者Aritra Sarkar

相关产品推荐
方舟 Agent Plan

超全模态模型 × Harness 升级,最新支持 Deepseek-V4.1-Flash、GLM-5.3 系列、Doubao-Seedream-5.0-pro、Kimi-K3 (部分), 限时 9.9 元起

最近更新时间:2026.11.16 05:27:03