4. SQL Server資料庫狀态監控 - 作業狀态

一. 作業曆史紀錄數；

二. 作業運作狀态；

三. 作業狀态告警；

有很多地方可以設定定時任務，比如：Windows的計劃任務，Linux下的crontab，各種開發工具裡的timer元件。SQL Server也有它的定時任務元件 SQL Server Agent，基于它可以友善的部署各種資料庫相關的作業(job)。

一. 作業曆史紀錄

作業的曆史紀錄按時間采用FIFO原則，當累積的作業曆史紀錄達到上限時，就會删除最老的紀錄。

1. 作業曆史紀錄數配置

所有作業總計紀錄條數預設為1000，最多為999999條；單個作業總計記錄條數預設為100，最多為999999條。有下面2種方式可以進行修改：

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

(2) 未記載的擴充存儲過程，SQL Server 2005及以後版本适用，以下腳本将記錄數設回預設值：

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

2. 删除作業曆史紀錄

(1) SSMS/SQL Server Agent/右擊作業檔案夾或某個作業/檢視曆史紀錄/清除

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

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

--清除所有作業15天前的紀錄
DECLARE @OldestDate datetime
SET @OldestDate = GETDATE()-15
EXEC msdb.dbo.sp_purge_jobhistory 
    @oldest_date=@OldestDate

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

作業曆史紀錄數有上限，通常不需要手動去删除。

3. 保留作業曆史紀錄

即便設定了曆史記錄上限到999999，如果作業很多，加之作業運作很頻繁，最終曆史記錄還是會被慢慢删除掉。

如果想要保留某些作業曆史的記錄，可以打開作業屬性/步驟/編輯/進階，選擇将這個步驟的曆史記錄輸出到檔案/自定義表中，如下圖：

二. 作業運作狀态

界面上可以通過: SSMS/SQL Server Agent/右擊作業檔案夾或某個作業/檢視曆史紀錄，如下用SQL 語句檢查作業狀态。

1. 作業上次運作狀态及時長

利用系統表msdb.dbo.sysjobhistory：

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

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

如下腳本檢視所有作業最後一次運作狀态及時長：

if OBJECT_ID('tempdb..#tmp_job') is not null
    drop table #tmp_job

--隻取最後一次結果
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這個系統作業
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. 作業目前運作狀态及時長

什麼時候可能要檢查作業的目前狀态？

(1) 需要關閉SQL Server或SQL Server Agent服務時；

(2) 等到目前作業完成，有後續動作；

(3) 純粹隻是檢視目前作業運作到哪個步驟等等。

通過SSMS/SQL Server Agent/右擊作業檔案夾或某個作業/檢視曆史紀錄，看到的作業曆史記錄存放在：

select * from msdb.dbo.sysjobhistory

需要注意的是：至少作業已完成第一步運作，sysjobhistory表中才會有作業曆史紀錄，若目前作業沒有完成任何一個步驟，那表裡就不會有本次運作紀錄。是以作業目前狀态用有時無法通過sysjobhistory檢視，尤其是作業隻有1個步驟且運作時間很長時。

2.1. SQL Server 2005及以後版本

(1) 目前運作狀态：系統存儲過程msdb.dbo.sp_help_job，傳回所有作業的運作狀态(current_execution_status)，共7種狀态值，詳見幫助文檔。檢視所有作業狀态如下：

exec msdb..sp_help_job

(2) 目前運作時長：系統存儲過程sp_help_job無法獲得作業運作時長，可通過新增的系統表sysjobactivity來檢視。檢視正在運作的作業如下：

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

以下腳本結合sp_help_job和sysjobactivity，得到作業的目前狀态及時長：

exec sp_configure 'show advanced options',1
RECONFIGURE
exec sp_configure 'Ad Hoc Distributed Queries',1
RECONFIGURE

if OBJECT_ID('tempdb..#jobinfo') is not null
    drop table #jobinfo
    
select * into #jobinfo
from 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這個系統表，通常借助sysprocesses監視作業的目前運作狀态及時長。

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裡獲得的作業編号跟sysjobs裡是不一緻的，是以上面進行了轉換，通常隻轉換job_id的前8位字元也行，如下腳本做了job_id的簡化轉換，并檢查作業已運作超過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

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

又或者，在每個作業最後一步，放一個檢查的步驟，這樣所有狀态時長全都監視到了，問題是如果作業運作時間過長，最後的檢查步驟根本無法被運作到。

三. 作業狀态告警

作業在完成後，自己有狀态檢查和告警機制，通常選擇郵件告警，如下圖：

但這僅限對作業最終運作狀态監視：

(1) 沒有運作結束的作業無法告警，或者說對作業的運作時長沒有監視；

(2) 如果作業在某個中間步驟設定了：失敗後繼續下一步，後續的作業步驟都成功，那麼作業最終狀态不會顯示會失敗，不會觸發告警，如下腳本檢查每個作業的所有步驟最後一次運作狀态：

if OBJECT_ID('tempdb..#tmp_job_step') is not null
    drop table #tmp_job_step

select 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))=1

select a.name job_name,
       s.step_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_step b 
    on a.job_id=b.job_id
 inner join msdb.dbo.sysjobsteps s
    on b.job_id = s.job_id and b.step_id = s.step_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

小結

SQL Server Agent作業自身的告警機制，有時并不夠用，是以還需要部署另外的作業，來檢查其他所有作業的運作狀況，大緻步驟如下：

(1) 部署資料庫郵件；

(2) 部署作業：定時檢查其他所有作業/步驟狀态，發郵件告警；

作業運作時長可以在這一并檢查，有時一些作業運作了很多天沒結束還沒人知道，也可以考慮放在性能監控裡，和其他資料庫請求一起監控。但是對于時長，通常需要有個性能基線，如果沒有的話直接和曆史最大值相比也是不錯的選擇。

4. SQL Server資料庫狀态監控 - 作業狀态

繼續閱讀

僞審批流實作邏輯

驗證6174猜想（枚舉法）---Python

流川楓的巨冷笑話，不看後悔一輩子！

神舟電腦學校電腦硬體知識第一章

《把時間當朋友》作者李笑來經典語錄（特幽默）

美國作業難倒中國老爸

笑話來的

PCB治具設計、制造和管理

OS --written test1

OS-written test2

資料庫圖檔的存取

制作一個小小的遊戲

馬哥教育N36第十三周作業

馬哥教育N36第十一周作業

馬哥教育N36第十八周作業

馬哥教育N36第十六周作業