查詢「作業(Job)」的執行記錄：使用「記錄檔檢視器」、預存程序 sp_help_jobhistory

適用版本：SQL Server 2005 與 2008

若要想知道「作業(Job)」執行完成後的紀錄，可以使用「記錄檔檢視器」，請參考下圖所示：

---

範例1：使用預存程序 sp_help_jobhistory 來查詢「作業(Job)」執行完成後的紀錄

--EX1. 以下是使用預存程序 sp_help_jobhistory 來查詢「作業(Job)」執行完成後的紀錄
-- 篩選一個月內的作業執行歷程紀錄

declare @tmp_sp_help_jobhistory table
(
 instance_id int null, job_id uniqueidentifier null, job_name sysname null, step_id int null, step_name sysname null, sql_message_id int null, 
 sql_severity int null, message nvarchar(4000) null, run_status int null, run_date int null, run_time int null, run_duration int null, 
 operator_emailed sysname null, operator_netsent sysname null, operator_paged sysname null, retries_attempted int null, server sysname null )

insert into @tmp_sp_help_jobhistory 
exec msdb.dbo.sp_help_jobhistory @mode='FULL';

WITH CTE_jobhistory
AS (
SELECT tshj.instance_id AS [InstanceID], tshj.sql_message_id AS [SqlMessageID], tshj.message AS [Message],tshj.step_id AS [StepID],
 tshj.step_name AS [StepName], tshj.sql_severity AS [SqlSeverity], tshj.job_id AS [JobID], tshj.job_name AS [JobName], tshj.run_status AS [RunStatus],
 CASE tshj.run_date WHEN 0 THEN NULL ELSE
  convert(datetime, stuff(stuff(cast(tshj.run_date as nchar(8)), 7, 0, '-'), 5, 0, '-') + N' ' + 
  stuff(stuff(substring(cast(1000000 + tshj.run_time as nchar(7)), 2, 6), 5, 0, ':'), 3, 0, ':'), 120) 
 END AS [RunDate],
 tshj.run_duration AS [RunDuration], tshj.operator_emailed AS [OperatorEmailed], tshj.operator_netsent AS [OperatorNetsent],
 tshj.operator_paged AS [OperatorPaged], tshj.retries_attempted AS [RetriesAttempted], tshj.server AS [Server], getdate() as [CurrentDate]
 FROM @tmp_sp_help_jobhistory as tshj)
 
SELECT ROW_NUMBER()OVER (ORDER BY InstanceID) 'RowNum' , JobName N'作業名稱', 
 CASE RunStatus 
  WHEN 0 THEN N'失敗'
  WHEN 1 THEN N'成功'
  WHEN 3 THEN N'取消'
  WHEN 4 THEN N'進行中'
  WHEN 5 THEN N'未知'
 END N'執行結果', 
 Message N'記錄訊息', RunDate N'啟動日期與時間', RunDuration N'經歷時間(秒)', OperatorEmailed N'已傳送電子郵件通知',SqlSeverity N'SQL嚴重性層級'
FROM CTE_jobhistory
WHERE StepID =1 AND RunDate >= dateadd(MM,-1,getdate()) -- 篩選一個月內的作業執行歷程紀錄
ORDER BY RunDate DESC
GO

請參考下圖所示：

---

範例2：檢視全部「作業」內的各個「步驟」之詳細紀錄

--EX2. 檢視全部「作業」內的各個「步驟」之詳細紀錄
-- 依據記錄的日期作為排序
SELECT ROW_NUMBER()OVER (ORDER BY his.run_date, his.run_time) 'RowNum',
 jb.name N'作業', his.step_name N'步驟', 
 CASE his.run_status 
  WHEN 0 THEN N'失敗'
  WHEN 1 THEN N'成功'
  WHEN 3 THEN N'取消'
  WHEN 4 THEN N'進行中'
  WHEN 5 THEN N'未知'
 END N'執行結果', 
 his.message N'訊息',
 CASE his.run_date WHEN 0 THEN NULL ELSE
  convert(datetime, stuff(stuff(cast(his.run_date as nchar(8)), 7, 0, '-'), 5, 0, '-') + N' ' + 
  stuff(stuff(substring(cast(1000000 + his.run_time as nchar(7)), 2, 6), 5, 0, ':'), 3, 0, ':'), 120) 
 END AS N'啟動日期',
 his.run_duration N'經歷時間(秒)',  his.operator_id_emailed N'已傳送電子郵件通知', his.sql_message_id N'錯誤訊息識別碼',
 his.sql_severity N'SQL嚴重性層級'
FROM msdb.dbo.sysjobs jb INNER JOIN msdb.dbo.sysjobhistory his
 ON jb.job_id = his.job_id
GO

請參考下圖所示：

---

這裡要提醒您的是：


依據預設值，「作業記錄大小上限」是：1,000 筆資料列。
每項作業的作業記錄最大資料列數是：100 筆資料列。


請參考下圖所示：

---

權限
依預設，只有系統管理員 (sysadmin) 固定伺服器角色的成員，才能夠執行這個預存程序。

其他使用者必須被授與 msdb 資料庫的下列其中一個 SQL Server Agent 固定資料庫角色。
SQLAgentUserRole
SQLAgentReaderRole
SQLAgentOperatorRole

如需有關這些角色權限的詳細資料，請參閱＜SQL Server Agent 固定資料庫角色＞。

---

參考資料：
sp_help_jobhistory (Transact-SQL)
http://msdn.microsoft.com/zh-tw/library/ms188025.aspx

SQL Server Agent 屬性 (記錄頁面)
http://technet.microsoft.com/zh-tw/library/ms189683.aspx

SQL Server Agent 固定資料庫角色
http://msdn.microsoft.com/zh-tw/library/ms188283.aspx