2012年5月4日 星期五

Transact-SQL 資料庫排程作業 (SQL Server Jobs)


在設計、開發系統時,若需要利用程式來管理 SQL Server 排程及其相關作業,可藉由 SQL 指令來達到這個目的。本文即針對 SQL Server 的排程作業指令進行說明,文後並提供一實作範例,以期讀者能對這類指令有更清楚的瞭解。



NOTE  SQL Server Express 不支援排程功能 (SQL Server Agent 無法啟用);若您使用的為 Express 版而又有作業排程的需求,可參考筆者之前所發佈的幾篇文章:SQL Server 2008 Express 自動化管理 - 前言SQL Server 2008 Express 自動化管理 (一) 定期備份SQL Server 2008 Express 自動化管理 (二) 定期刪除資料SQL Server 2008 Express 自動化管理 (三) 異常警示
以下開始說明:


0) 利用 "SQL Server 組態管理員" 啟動 SQL Server Agent

1) 作業項目
建立新的作業項目

exec msdb.dbo.sp_add_job @job_name    = 'My Jobs', 
                         @enabled     = 1,                    -- 0: 不啟用  1:啟用
                         @description = 'Just for testing...'

select * from msdb.dbo.SysJobs
刪除作業項目:將一併刪除所有與該作業項目相關的資料;排程 (見第 4 節) 不會被刪除,但排程與該作業項目的關聯 (見第 5 節) 將被刪除。
exec msdb.dbo.sp_delete_job @job_name = N'My Jobs' 

select * from msdb.dbo.SysJobs

2) 作業伺服器 - 要在哪一台伺服器執行作業項目 (若省略 @server_name 參數,則視為在執行此指令的 SQL Server 本機執行。
與作業項目建立關聯
exec msdb.dbo.sp_add_jobserver @job_name    = 'My Jobs',
                               @server_name = 'Your SQL Server Name...'

select * from msdb.dbo.SysJobServers
刪除此關聯
exec msdb.dbo.sp_delete_jobserver @job_name  = 'My Jobs',
                                  @server_name = 'Your SQL Server Name...'

select * from msdb.dbo.SysJobServers

3) 執行步驟 - 每個作業項目可包含多個執行步驟,當該項目開始執行時,會依照步驟的序號 (ID) 進行作業。
新增步驟:系統會依照 sp_add_jobsteps 的執行順序賦予序號;若想在已存在的步驟中插入新的步驟,可指定序號參數 (@step_id) 來達到目的。舉例說明,先在作業項目 My Jobs 加入兩個步驟 (不指定 @step_id),此處務必設定 @on_success_action 參數,否則只會執行第一個步驟 (詳見 MSDN 官網)
exec msdb.dbo.sp_add_jobstep @job_name  = 'My Jobs',
                             @step_name = 'My Jobs Step. A',
                             @on_success_action = 3,            -- 成功執行後,繼續執行下一步驟。
                             @command   = 'Your Command Codes...'

exec msdb.dbo.sp_add_jobstep @job_name  = 'My Jobs',
                             @step_name = 'My Jobs Step. B',
                             @on_success_action = 1,            -- 成功執行後,結束該作業項目。
                             @command   = 'Your Command Codes...'

select * from msdb.dbo.SysJobSteps
插入新步驟,並且指定 @step_id = 2,結果會將此步驟安插在原來的步驟 2, 3 之間 。
exec msdb.dbo.sp_add_jobstep @job_name  = 'My Jobs',     
                             @step_id   = 2,
                             @step_name = 'My Jobs Step. A-1',
                             @on_success_action = 3,
                             @command   = 'Your Command Codes...'

select * from msdb.dbo.SysJobSteps
刪除步驟:欲刪除 "My Jobs" 中的某個步驟時,可參考以下程式 (此例將刪除第二個作業步驟 (step_id = 2))。
declare @prg_job_id   binary(16)

select @prg_job_id = job_id
  from msdb.dbo.sysjobs 
 where name = N'My Jobs'

delete from msdb.dbo.SysJobSteps
 where job_id = @prg_job_id
   and step_id = 2

select * from msdb.dbo.SysJobSteps

4) 排程 - 當達到排程所設定的時間條件時,將開始執行此排程所對應的作業項目 (至於如何設定二者的關聯,請見下節 5. 排程與作業項目)。
新增排程:假設將排程設定為 "每個月第一天的 00:00:00" 執行
exec msdb.dbo.sp_add_schedule @schedule_name = 'My Scheduler',
                              @freq_type     = 16,
                              @freq_interval = 1,
                              @freq_recurrence_factor  = 1,
                              @active_start_time = 000000
                              
select * from msdb.dbo.SysSchedules
(*) sp_add_schedule 參數設定資料,請見: [MSDN 官方網站] sp_add_schedule

刪除排程
exec msdb.dbo.sp_delete_schedule @schedule_name = 'My Scheduler',
                                 @force_delete  = 1

select * from msdb.dbo.SysSchedules

當系統發現存在多個同名稱的排程時,執行上述指令會出現錯誤訊息,此時請利用以下程式進行刪除作業 (假設排程名稱為 My Scheduler)。
declare @same_schedule_name_counter   int,
        @prg_schedule_id              int,
        @i                            int

select @same_schedule_name_counter = count(*)
  from msdb.dbo.SysSchedules
 where name = 'My Scheduler'
 
select @i = 1 
 
while @i <= @same_schedule_name_counter
 begin
    select top 1 @prg_schedule_id = schedule_id
      from msdb.dbo.SysSchedules
     where name = 'My Scheduler'
 
    exec msdb.dbo.sp_delete_schedule @schedule_id = @prg_schedule_id,
                                     @force_delete  = 1
    select @i = @i + 1
 end

select * from msdb.dbo.SysSchedules

5) 排程與作業項目的關聯
建立關聯
exec msdb.dbo.sp_attach_schedule @job_name      = 'My Jobs',
                                 @schedule_name = 'My Scheduler'

select * from msdb.dbo.SysJobSchedules
卸除關聯
exec msdb.dbo.sp_detach_schedule @job_name      = 'My Jobs',
                                 @schedule_name = 'My Scheduler'

select * from msdb.dbo.SysJobSchedules

6) 查看作業項目狀況
select sja.*
  from msdb.dbo.SysJobActivity sja,
       msdb.dbo.SysJobs sj
 where sja.job_id = sj.job_id
   and sj.name = 'My Jobs'

7) 查看作業項目執行紀錄 - 若執行失敗,相關的訊息也會記錄於 msdb.dbo.SysJobHistory 的 message 欄位。
select jh.*
  from msdb.dbo.SysJobHistory jh,
       msdb.dbo.SysJobs j
 where j.job_id = jh.job_id
   and j.name = 'My Jobs'

實作範例
-- 建立測試 Database, Table -----------------------------------------
create database TEST_DB
go

create table TEST_DB.dbo.Job (c1 int)
go
 
 
-- 建立作業項目: My Jobs --------------------------------------------
exec msdb.dbo.sp_add_job @job_name    = 'My Jobs', 
                         @enabled     = 1,                   
                         @description = 'Just for testing...'
 
select * from msdb.dbo.SysJobs
go

 
-- 設定 欲執行此作業項目的伺服器 ------------------------------------
exec msdb.dbo.sp_add_jobserver @job_name    = 'My Jobs',
                               @server_name = 'Your SQL Server Name...'

select * from msdb.dbo.SysJobServers
go


-- 建立步驟 ---------------------------------------------------------
exec msdb.dbo.sp_add_jobstep @job_name  = 'My Jobs',
                             @step_name = 'My Jobs Step. A',
                             @on_success_action = 3,
                             @command   = 'insert into TEST_DB.dbo.Job values(1)'

exec msdb.dbo.sp_add_jobstep @job_name  = 'My Jobs',
                             @step_name = 'My Jobs Step. B',
                             @on_success_action = 1,
                             @command   = 'insert into TEST_DB.dbo.Job values(2)'

select * from msdb.dbo.SysJobSteps
go


-- 建立排程 ---------------------------------------------------------
-- 設定每月 4 日上午 11:40 執行
exec msdb.dbo.sp_add_schedule @schedule_name = 'My Scheduler',
                              @freq_type     = 16,
                              @freq_interval = 4,
                              @freq_recurrence_factor = 1,
                              @active_start_time = 114000
                              
select * from msdb.dbo.SysSchedules
go


-- 將排程指定給作業項目 ---------------------------------------------
exec msdb.dbo.sp_attach_schedule @job_name      = 'My Jobs',
                                 @schedule_name = 'My Scheduler'

select * from msdb.dbo.SysJobSchedules
go


-- 狀態查詢 ---------------------------------------------------------
-- 作業項目 .....................................
select sja.*
  from msdb.dbo.SysJobActivity sja,
       msdb.dbo.SysJobs sj
 where sja.job_id = sj.job_id
   and sj.name = 'My Jobs'

-- 執行紀錄 .....................................
select jh.*
  from msdb.dbo.SysJobHistory jh,
       msdb.dbo.SysJobs j
 where j.job_id = jh.job_id
   and j.name = 'My Jobs'