有很多地方可以設(shè)置定時任務(wù)，比如：Windows的計劃任務(wù)，linux下的crontab，各種開發(fā)工具里的timer組件。SQL Server也有它的定時任務(wù)組件 SQL Server Agent，基于它可以方便的部署各種數(shù)據(jù)庫相關(guān)的作業(yè)(job)。

一. 作業(yè)歷史紀錄

作業(yè)的歷史紀錄按時間采用FIFO原則，當累積的作業(yè)歷史紀錄達到上限時，就會刪除最老的紀錄。

1. 作業(yè)歷史紀錄數(shù)配置

所有作業(yè)總計紀錄條數(shù)默認為1000，最多為999999條；單個作業(yè)總計記錄條數(shù)默認為100，最多為999999條。有下面2種方式可以進行修改：

(1) SSMS/SQL Server Agent/屬性/歷史；

(2) 未記載的擴展存儲過程，SQL Server 2005及以后版本適用，以下腳本將記錄數(shù)設(shè)回默認值：

EXEC msdb.dbo.sp_set_sqlagent_PRoperties  @jobhistory_max_rows=-1, @jobhistory_max_rows_per_job=-1GO

2. 刪除作業(yè)歷史紀錄

(1) SSMS/SQL Server Agent/右擊作業(yè)文件夾或某個作業(yè)/查看歷史紀錄/清除

在SQL Server 2000中會一次清除所有作業(yè)歷史記錄，SQL Server 2005 及以后版本可以有選擇的清除某個作業(yè)/某個時間之前的歷史紀錄；

(2) SQL Server 2005及以后版本，提供了系統(tǒng)存儲過程如下：

--清除所有作業(yè)15天前的紀錄DECLARE @OldestDate datetimeSET @OldestDate = GETDATE()-15EXEC msdb.dbo.sp_purge_jobhistory     @oldest_date=@OldestDate--清除作業(yè)”Test”3天前的紀錄DECLARE @OldestDate datetimeDECLARE @JobName varchar(256)SET @OldestDate = GETDATE()-3SET @JobName = 'Test'EXEC msdb.dbo.sp_purge_jobhistory     @job_name=@JobName,     @oldest_date=@OldestDate

作業(yè)歷史紀錄數(shù)有上限，通常不需要手動去刪除。

3. 保留作業(yè)歷史紀錄

即便設(shè)置了歷史記錄上限到999999，如果作業(yè)很多，加之作業(yè)運行很頻繁，最終歷史記錄還是會被慢慢刪除掉。

如果想要保留某些作業(yè)歷史的記錄，可以打開作業(yè)屬性/步驟/編輯/高級，選擇將這個步驟的歷史記錄輸出到文件/自定義表中，如下圖：

二. 作業(yè)運行狀態(tài)

界面上可以通過: SSMS/SQL Server Agent/右擊作業(yè)文件夾或某個作業(yè)/查看歷史紀錄，如下用SQL 語句檢查作業(yè)狀態(tài)。

1. 作業(yè)上次運行狀態(tài)及時長

利用系統(tǒng)表msdb.dbo.sysjobhistory：

(1) 表中的run_status字段表示作業(yè)上次運行狀態(tài)，有0~3共4種狀態(tài)值，詳見幫助文檔，另外在2005的幫助文檔中寫到：sysjobhistory的run_status為4表示運行中，經(jīng)測試是錯誤的，在2008的幫助中已沒有4這個狀態(tài)；

(2) 表中run_duration字段表示作業(yè)上次運行時長，格式為HHMMSS，比如20000則表示運行了2小時。

如下腳本查看所有作業(yè)最后一次運行狀態(tài)及時長：

if OBJECT_ID('tempdb..#tmp_job') is not null    drop table #tmp_job--只取最后一次結(jié)果select job_id,       run_status,       CONVERT(varchar(20),run_date) run_date,       CONVERT(varchar(20),run_time) run_time,       CONVERT(varchar(20),run_duration) run_duration  into #tmp_job  from msdb.dbo.sysjobhistory jh1 where jh1.step_id = 0   and (select COUNT(1) from msdb.dbo.sysjobhistory jh2         where jh2.step_id = 0           and (jh1.job_id = jh2.job_id)          and (jh1.instance_id <= jh2.instance_id))=1--排除syspolicy_purge_history這個系統(tǒng)作業(yè)select a.name job_name,       case b.run_status when 0 then 'Failed'                         when 1 then 'Succeeded'                         when 2 then 'Retry'                         when 3 then 'Canceled'       else 'Unknown'        end as job_status,       LEFT(run_date,4)+'-'+SUBSTRING(run_date,5,2)+'-'+RIGHT(run_date,2)       +SPACE(1)       +LEFT(RIGHT(1000000+run_time,6),2)+':'            +SUBSTRING(RIGHT(1000000+run_time,6),3,2)+':'            +RIGHT(RIGHT(1000000+run_time,6),2) as job_started_time,       +LEFT(RIGHT(1000000+run_duration,6),2)+':'            +SUBSTRING(RIGHT(1000000+run_duration,6),3,2)+':'            +RIGHT(RIGHT(1000000+run_duration,6),2) as job_duration  from msdb.dbo.sysjobs a   left join    #tmp_job b     on a.job_id=b.job_id  where a.name not in ('syspolicy_purge_history')   and a.enabled = 1 order by b.run_status asc,a.name,b.run_duration desc

2. 作業(yè)當前運行狀態(tài)及時長

什么時候可能要檢查作業(yè)的當前狀態(tài)？

(1) 需要關(guān)閉SQL Server或SQL Server Agent服務(wù)時；

(2) 等到當前作業(yè)完成，有后續(xù)動作；

(3) 純粹只是查看當前作業(yè)運行到哪個步驟等等。

通過SSMS/SQL Server Agent/右擊作業(yè)文件夾或某個作業(yè)/查看歷史紀錄，看到的作業(yè)歷史記錄存放在：

select * from msdb.dbo.sysjobhistory

需要注意的是：至少作業(yè)已完成第一步運行，sysjobhistory表中才會有作業(yè)歷史紀錄，若當前作業(yè)沒有完成任何一個步驟，那表里就不會有本次運行紀錄。所以作業(yè)當前狀態(tài)用有時無法通過sysjobhistory查看，尤其是作業(yè)只有1個步驟且運行時間很長時。

2.1. SQL Server 2005及以后版本

(1) 當前運行狀態(tài)：系統(tǒng)存儲過程msdb.dbo.sp_help_job，返回所有作業(yè)的運行狀態(tài)(current_execution_status)，共7種狀態(tài)值，詳見幫助文檔。查看所有作業(yè)狀態(tài)如下：

exec msdb..sp_help_job

(2) 當前運行時長：系統(tǒng)存儲過程sp_help_job無法獲得作業(yè)運行時長，可通過新增的系統(tǒng)表sysjobactivity來查看。查看正在運行的作業(yè)如下：

select a.name,       b.start_execution_date,       DATEDIFF(MI,b.start_execution_date,GETDATE()) as job_duration  from msdb..sysjobs a inner join msdb..sysjobactivity b    on a.job_id = b.job_id where b.start_execution_date is not null   and b.stop_execution_date is null 

以下腳本結(jié)合sp_help_job和sysjobactivity，得到作業(yè)的當前狀態(tài)及時長：

exec sp_configure 'show advanced options',1RECONFIGUREexec sp_configure 'Ad Hoc Distributed Queries',1RECONFIGUREif OBJECT_ID('tempdb..#jobinfo') is not null    drop table #jobinfo    select * into #jobinfofrom openrowset('sqloledb', 'server=(local);trusted_connection=yes','exec msdb.dbo.sp_help_job')select a.name,       j.current_execution_status,       b.start_execution_date,       DATEDIFF(MI,b.start_execution_date,GETDATE()) as job_duration_minute  from msdb..sysjobs a inner join msdb..sysjobactivity b    on a.job_id = b.job_id inner join #jobinfo j    on a.job_id = j.job_id     where b.start_execution_date is not null   and b.stop_execution_date is null 

2.2. SQL Server 2000沿用過來的方法

在SQL Server 2000時，沒有sysjobactivity這個系統(tǒng)表，通常借助sysprocesses監(jiān)視作業(yè)的當前運行狀態(tài)及時長。

select j.name,        p.status as current_execution_status,        p.last_batch as start_execution_date,       ISNULL(DATEDIFF(MI, p.last_batch, GETDATE()), 0) as job_duration_minute  from msdb.dbo.sysjobs j, master..sysprocesses p where p.program_name like 'SQLAgent - TSQL JobStep (Job%'   and substring((cast(j.job_id as varchar(36))),7,2) +       substring((cast(j.job_id as varchar(36))),5,2) +       substring((cast(j.job_id as varchar(36))),3,2) +       substring((cast(j.job_id as varchar(36))),1,2) +       substring((cast(j.job_id as varchar(36))),12,2) +       substring((cast(j.job_id as varchar(36))),10,2) +       substring((cast(j.job_id as varchar(36))),17,2) +       substring((cast(j.job_id as varchar(36))),15,2) +       substring((cast(j.job_id as varchar(36))),20,4) +       substring((cast(j.job_id as varchar(36))),25,12)            = substring((cast(p.program_name as varchar(75))),32,32)

sysprocesses里獲得的作業(yè)編號跟sysjobs里是不一致的，所以上面進行了轉(zhuǎn)換，通常只轉(zhuǎn)換job_id的前8位字符也行，如下腳本做了job_id的簡化轉(zhuǎn)換，并檢查作業(yè)已運行超過30分鐘：

declare @MaxMinutes int    set @MaxMinutes = 30 select j.name,        p.status as current_execution_status,        p.last_batch as start_execution_date,       ISNULL(DATEDIFF(MI, p.last_batch, GETDATE()), 0) as job_duration_minute  from msdb..sysjobs j inner join master..sysprocesses p    on substring(left(cast(j.job_id as varchar(36)),8),7,2) +       substring(left(cast(j.job_id as varchar(36)),8),5,2) +       substring(left(cast(j.job_id as varchar(36)),8),3,2) +       substring(left(cast(j.job_id as varchar(36)),8),1,2) = substring(p.program_name,32,8) where p.program_name like 'SQLAgent - TSQL JobStep (Job%'   and ISNULL(DATEDIFF(MI, p.last_batch, GETDATE()), 0) > @MaxMinutes

還有種比較笨的方法，在要監(jiān)視的所有作業(yè)中增加一個步驟，如 : select GETDATE() 放在第一步，這樣在sysjobhistory中就會有步驟1的運行紀錄了，以此為起點，可以計算已運行時長。如果有很多已經(jīng)部署的job，這確實不是個好辦法。

又或者，在每個作業(yè)最后一步，放一個檢查的步驟，這樣所有狀態(tài)時長全都監(jiān)視到了，問題是如果作業(yè)運行時間過長，最后的檢查步驟根本無法被運行到。

三. 作業(yè)狀態(tài)告警

作業(yè)在完成后，自己有狀態(tài)檢查和告警機制，通常選擇郵件告警，如下圖：

但這僅限對作業(yè)最終運行狀態(tài)監(jiān)視：

(1) 沒有運行結(jié)束的作業(yè)無法告警，或者說對作業(yè)的運行時長沒有監(jiān)視；

(2) 如果作業(yè)在某個中間步驟設(shè)置了：失敗后繼續(xù)下一步，后續(xù)的作業(yè)步驟都成功，那么作業(yè)最終狀態(tài)不會顯示會失敗，不會觸發(fā)告警，如下腳本檢查每個作業(yè)的所有步驟最后一次運行狀態(tài)：

if OBJECT_ID('tempdb..#tmp_job_step') is not null    drop table #tmp_job_stepselect jh1.job_id,       jh1.step_id,       jh1.run_status,       CONVERT(varchar(20),jh1.run_date) run_date,       CONVERT(varchar(20),jh1.run_time) run_time,       CONVERT(varchar(20),jh1.run_duration) run_duration  into #tmp_job_step  from msdb.dbo.sysjobhistory jh1 where (select COUNT(1) from msdb.dbo.sysjobhistory jh2         where (jh1.job_id = jh2.job_id and jh1.step_id = jh2.step_id)          and (jh1.instance_id <= jh2.instance_id))=1select a.name job_name,       s