利用msdb.dbo.sysjobactivity資料表，檢視SQL Server Agent Job執行活動狀態

2015-06-03

4935
0
[SQL]Lab
2015-07-07

利用msdb.dbo.sysjobhistory資料表，檢視SQL Server Agent Job執行活動狀態

相信多數DBA一早到班後都會先檢查前一晚的Agent JOB是否有成功執行，我的做法就是去Query JOB的歷史

紀錄，看看有無作業失敗的訊息，但這樣判斷作業是否成功還是有盲點存在。

某個星期五下午我修改了一個作業執行的SP(也沒改甚麼，只是在某個Query的Where多Filter一個欄位而已)，

然後就過周末了。直到星期一上班後，我依照往例檢查有無作業失敗的JOB，此時並無任何異常發現。然而我依

稀想起星期五的事情，因此特別看一下該JOB的歷史紀錄。發現該JOB在星期六凌晨執行後就沒有執行紀錄(該JOB

應每日凌晨執行一次)。

我覺得很奇怪所以檢查一下所有Agent的Session，果然發現有一個Agent Session還在處理該JOB(也就是說該JOB

跑了2天多還在跑)。打開Dashboard也看見CPU使用率是持續保持在10%左右(讓我想起楊志強老師提過，如果CPU

長時間維持在某一個使用率，就有可能是有Session死掉造成)，因此我先kill該Session來結束該JOB，並針對問題解決。

然而我反思以前都是看JOB執行結果來判斷，但萬一遇見這種JOB卡住跑不完也沒作業紀錄產出的狀況改怎麼解決呢?

因此先拜google大神看看，果然蒐出一個關鍵的Table。在msdb中有一個dbo.sysjobactivity的資料表是專門記錄JOB

活動狀態，本文就針對此表稍作介紹分享。

msdb.dbo.sysjobactivity這一張表會記錄所有JOB的活動狀態。每當SQL Agent重啟時，SQL Server就會在該表針對

所有JOB 新增一筆相對應的紀錄，接下來該JOB的每一次執行都會異動到該筆紀錄，如下圖所示，我們在重啟SQL Agent

後，該名為Test的JOB會在msdb.dbo.sysjobactivity新增新的紀錄出來(下圖紅色圈選處，這張圖是我後製產出，為何會

有三筆新紀錄而不是一筆，其實是因為我重啟了三次Agent造成，本LAB中我們只要觀察最新的那一筆，也就是Session_id

欄位為107273那一筆)。新增的紀錄大多數欄位預設值都為NULL。

我執行一次Test JOB後看看接下來該筆資料會有何變化。

當我們執行過一次JOB後，該筆session_id為107273的run_requested_date欄位、start_execution_date欄位、

stop_execution_date欄位都有紀錄該JOB的執行時間起迄，而job_history_id則可以搭配msdb.dbo.sysjobhistory

資料表來瞭解執行情況。

然而如果發生JOB持續執行而卡死的情況在該Session_id的資料呈現為何呢?如下圖所示，我用while 1=1來讓該JOB無窮

迴圈的執行，藉此觀察該筆紀錄的變化。

如下圖所示，Test這一個JOB持續在執行，此時我們觀察Session_id為107273這筆資料。我們發現除了run_requested_date

欄位、start_execution_date欄位被改為JOB執行時間，其他像stop_execution_date欄位及job_history_id欄位都被異

動為NULL。

當我們將Test JOB停掉後再觀察該筆紀錄，如下圖所示stop_execution_date欄位及job_history_id欄位都又被更新資料內容。

根據上述實驗後，我們可以根據該資料表特性去寫出判斷目前Agent是否有JOB在執行的Query了。

這樣就能協助我們發現是否有JOB執行過久的狀況。

語法如下 :


SELECT
    job.Name, job.job_ID
    ,job.Originating_Server
    ,activity.run_requested_Date
    ,datediff(minute, activity.run_requested_Date, getdate()) AS Elapsed
FROM
    msdb.dbo.sysjobs_view job
        INNER JOIN msdb.dbo.sysjobactivity activity
        ON (job.job_id = activity.job_id)
WHERE
    run_Requested_date is not null
    AND start_execution_date is not null
    AND stop_execution_date is null
    AND activity.session_id=(select max(session_id) from msdb.dbo.syssessions)

參考資料來源

https://msdn.microsoft.com/zh-tw/library/ms190484.aspx