一、說明
在SQL Server 的作業中，有些時候我們希望步驟在執行失敗後可以重復執行多幾次，而我產生這個念頭的是因為我在執行某個步驟（這個步驟對分區表進行修改分區方案、分區函數）的時候出現死鎖了，如下面描述：
消息1205，級別13，狀態55，第1 行
事務(進程ID 115)與另一個進程被死鎖在鎖資源上，並且已被選作死鎖犧牲品。請重新運行該事務。
我想到解決這個問題的方法是重復執行多幾次這個步驟（經測試執行幾次可行），所以這裡就講講如果讓數據庫作業完成這一動作。
二、測試實驗
(一) 測試方案：
我 們在作業中設置了簡單的兩個步驟，步驟1往表中插入一條數據，步驟2更新這條記錄，讓計數器+1，這就測試了成功時候，說明重復次數是沒用的；對步驟2進 行修改，讓步驟2拋出一個異常，因為我們需要對表進行計數器+1，所以我們這裡就拋出一個異常，撲捉到這個異常，進行計數器+1後，再把這個異常拋出去， 拋第一個異常是模擬執行失敗的，拋第二個異常就是讓作業在失敗情況下重復執行，因為使用TRY和CATCH後第一個異常就給捕獲了。
(二) 創建表：
CREATE TABLE [dbo].[TestJobCounts](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Counts] [int] NULL,
[UpdateTime] [datetime] NULL,
CONSTRAINT [PK_TestJobCounts] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
(三) 創建步驟1需要執行的存儲過程：
-- =============================================
-- Author: <Viajar>
-- Create date: <2011.09.14>
-- Description: <插入數據>
-- =============================================
CREATE PROCEDURE [dbo].[sp_Insert]
AS
BEGIN
INSERT INTO [TestBackupDB].[dbo].[TestJobCounts]
([Counts]
,[UpdateTime])
VALUES
(0
,getdate())
END
(四) 創建步驟2需要執行的存儲過程：
-- =============================================
-- Author: <Viajar>
-- Create date: <2011.09.14>
-- Description: <更新數據>
-- =============================================
CREATE PROCEDURE [dbo].[sp_UpdateCount]
AS
BEGIN
BEGIN TRANSACTION AltPS
BEGIN TRY
UPDATE [TestBackupDB].[dbo].[TestJobCounts]
SET [Counts] = [Counts]+1
RAISERROR (N'This is message %s %d.', -- Message text.
13, -- Severity,
55, -- State,
N'number', -- First argument.
5); -- Second argument.
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
PRINT 'Being ROLLBACK'
ROLLBACK TRANSACTION AltPS--回滾
UPDATE [TestBackupDB].[dbo].[TestJobCounts]
SET [Counts] = [Counts]+1
WHERE Id = (select max(id) from [TestJobCounts])
PRINT '失敗'
RAISERROR (N'This is message %s %d.', -- Message text.
13, -- Severity,
55, -- State,
N'number', -- First argument.
5); -- Second argument.
END CATCH
IF @@TRANCOUNT > 0
BEGIN
PRINT 'Being COMMIT'
COMMIT TRANSACTION AltPS;--執行
PRINT '成功'
END
END
(五) 創建作業：
USE [msdb]
GO
/****** 對象: Job [測試作業執行次數] 腳本日期: 09/14/2011 11:36:51 ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** 對象: JobCategory [[Uncategorized (Local)]]] 腳本日期: 09/14/2011 11:36:51 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategorIEs WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
END
DECLARE @jobId BINARY(16)
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'測試作業執行次數',
@enabled=1,
@notify_level_eventlog=0,
@notify_level_email=0,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N'無描述。',
@category_name=N'[Uncategorized (Local)]',
@owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** 對象: Step [插入一條數據] 腳本日期: 09/14/2011 11:36:52 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'插入一條數據',
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=3,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'exec dbo.sp_Insert',
@database_name=N'TestBackupDB',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** 對象: Step [執行多次存儲過程] 腳本日期: 09/14/2011 11:36:52 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'執行多次存儲過程',
@step_id=2,
@cmdexec_success_code=0,
@on_success_action=1,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=5,
@retry_interval=1,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'exec dbo.sp_UpdateCount',
@database_name=N'TestBackupDB',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'執行時間',
@enabled=1,
@freq_type=4,
@freq_interval=1,
@freq_subday_type=4,
@freq_subday_interval=10,
@freq_relative_interval=0,
@freq_recurrence_factor=0,
@active_start_date=20110914,
@active_end_date=99991231,
@active_start_time=0,
@active_end_time=235959
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
附圖一：設置步驟之間的關系：
（圖1）
附圖二：設置步驟2失敗時重試的次數以及每次重試的時間間隔，這裡需要注意的是：1，如果步驟2執行成功，是不會執行重試的，直接就退出報告成功的作業了；2，這裡重試次數設置為5，那麼一共執行的次數就會是6次，因為要加上本身執行那一次；
（圖2）
三、測試效果
(一) 執行中，注意狀態中的重試間隔：
（圖3）
(二) 執行完，這是重復執行了5次依然失敗的效果圖：
（圖4）
(三) 查看表，發現Counts的數值是6，說明本身執行1次，外加重試執行5次：
（圖5）
