SQL Server Agent 作業步驟重試機制完整實作：解決死鎖自動重跑

應用場景

分區表修改分區函數/配置時發生死鎖 (錯誤 1205)，需自動重試步驟解決暫時性衝突。

核心設定參數

在 sp_add_jobstep 中設定兩個關鍵參數：

• @retry_attempts - 重試次數 (範例設 5 次)
• @retry_interval - 重試間隔分鐘數 (範例設 1 分鐘)

完整作業建立腳本

USE [msdb]; GO
BEGIN TRANSACTION
DECLARE @ReturnCode INT, @jobId BINARY(16);

-- 建立作業
EXEC @ReturnCode = msdb.dbo.sp_add_job 
    @job_name = N'測試作業重試機制',
    @enabled = 1, @owner_login_name = N'sa', @job_id = @jobId OUTPUT;

-- 步驟 1: 插入測試資料 (成功不重試)
EXEC msdb.dbo.sp_add_jobstep @job_id = @jobId, @step_name = N'插入資料',
    @step_id = 1, @subsystem = N'TSQL', @command = N'EXEC dbo.sp_Insert',
    @on_success_action = 3, @on_fail_action = 2, @retry_attempts = 0;

-- 步驟 2: 更新並模擬失敗 (失敗重試 5 次)
EXEC msdb.dbo.sp_add_jobstep @job_id = @jobId, @step_name = N'更新重試',
    @step_id = 2, @subsystem = N'TSQL', @command = N'EXEC dbo.sp_UpdateCount',
    @on_success_action = 1, @on_fail_action = 2, 
    @retry_attempts = 5, @retry_interval = 1;  -- 關鍵設定

-- 設定起始步驟與排程
EXEC msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1;
EXEC msdb.dbo.sp_add_jobschedule @job_id = @jobId, @name = N'每10分鐘',
    @freq_type = 4, @freq_interval = 1, @freq_subday_type = 4, @freq_subday_interval = 10;
COMMIT TRANSACTION;

模擬失敗的存儲過程設計

CREATE PROCEDURE dbo.sp_UpdateCount
AS
BEGIN
    BEGIN TRANSACTION AltPS;
    BEGIN TRY
        UPDATE TestJobCounts SET Counts = Counts + 1;
        -- 故意拋出嚴重度 13 錯誤模擬失敗
        RAISERROR (N'模擬死鎖錯誤', 13, 55);
    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION AltPS;
        -- 即使失敗仍更新計數器 (驗證重試次數)
        UPDATE TestJobCounts SET Counts = Counts + 1 
        WHERE Id = (SELECT MAX(Id) FROM TestJobCounts);
        -- 重新拋出錯誤觸發作業重試
        RAISERROR (N'模擬死鎖錯誤', 13, 55);
    END CATCH
    IF @@TRANCOUNT > 0 COMMIT TRANSACTION AltPS;
END

驗證結果分析

• 執行中狀態：作業監視器顯示「重試間隔」倒數
• 最終失敗後：Counts 欄位值 = 6 (1 次初始 + 5 次重試)
• 成功情況：若中間某次成功，立即停止重試，回報作業成功

適用場景與限制