自動重新啟動資料庫工作排程

咱家的 MIS 因為上游資料來源(或程式)延遲或失敗，user 都上工了也只能癡癡地等上游資料跑完後，再手動執行 SQL JOB... 或者更慘的是，假日被急摳來處理...
連累小弟轄下的系統資料不夠新鮮以致無法作業，只好吃下問題單 ="=... 接著安排人跟催甚麼時候補好資料庫。
為了能安心休假只好下海找解套......

小弟我只會寫程式解決問題所以 SQL 很弱，不想常常去碰資料庫，連重新啟動 JOB 都覺得痛苦。
可是 MIS 在 JOB 內搞一堆 SQL 跑來跑去，只好配合在這個模式下找出不讓系統開發人員痛苦的未來。

原來用個簡單的 MS SQL 涵式，就可以解決這個惱人的問題了！
SQL 2000 : sp_update_jobschedule
SQL 2005 : sp_update_schedule (建議) or sp_update_jobschedule (為相容 2000 而殘留的)

只要兩個簡單的要素即可高枕無憂
1. 要能判斷是否需重新執行的方法。
2. 要建立一個執行一次的 job schedule。

你的 JOB 應該有兩個 Schedule 如下，第一個是你正常應該要啟動的、一個是當有問題的時候需要被啟動的(disable)，這個 shcedule 在以下範例我命名為 reschedule。


接著你要依資料庫版本查出重新啟動 schedule 的 key，以下指令請 use msdb 後執行。
如果是 SQL 2005 以上圖為例，ID 506 就是關鍵值 @schedule_id。
如果是 SQL 2000 則需呼叫 exec sp_help_job @job_name = '[JOB NAME]'，以上圖為例 @job_name = 'ADM_LETTERS_COUNT (2009-11-20)'。取得 @job_id = '499235D9-4EB1-45C5-A66D-1D4D53BA1C98'

然後我以如果資料更新失敗，則一個小時後重新執行一次為例，如下：

-- 主要資料處理 SQL (建議規劃防重複執行判斷)
INSERT ADM_LETTERS_COUNT SELECT ...

-- 第一要素：設計判斷資料更新失敗的邏輯
declare @adm_data_date_diff int
set @adm_data_date_diff = (select datediff(day, max(ADMLC_DATADATE), getdate()) from ADM_LETTERS_COUNT)

-- 第二要素：判斷需要重新啟動則安排一小時候執行 (以下範例會跑到當天凌晨才停止，建議可再設計停跑時間。)
if(@adm_data_date_diff > 1) begin
declare @today int, @new_datetime datetime, @new_time int
set @today = convert(int, convert(char(8), getdate(), 112))
set @new_datetime = dateadd(hour, 1, getdate())
set @new_time = datepart(hour, @new_datetime)*10000+datepart(minute, @new_datetime)*100
use msdb
/* for SQL 2005 new function
exec sp_update_schedule @schedule_id = 506, @enabled = 1, @active_start_date = @today, @active_start_time = @new_time
--*/
exec sp_update_jobschedule
@job_id = '499235D9-4EB1-45C5-A66D-1D4D53BA1C98',
@name = 'reschedule', @enabled = 1,
@active_start_date = @today,
@active_start_time = @new_time
end

為了易懂以上簡化程式碼，其他加強的機制可以再繼續自行應用。

以下簡單舉例防重複執行判斷並不難，只要把第一要素套上來即可，如下...

declare @adm_data_date_diff int
set @adm_data_date_diff = (select datediff(day, max(ADMLC_DATADATE), getdate()) from ADM_LETTERS_COUNT)

if(@adm_data_date_diff > 1) begin
INSERT ADM_LETTERS_COUNT SELECT ...
end

set @adm_data_date_diff = (select datediff(day, max(ADMLC_DATADATE), getdate()) from ADM_LETTERS_COUNT)

if(@adm_data_date_diff > 1) begin
declare @today int, @new_datetime datetime, @new_time int
...(略)...
exec sp_update_jobschedule @job_id ...
end