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
相关产品推荐
相关产品推荐

