SQL Agent ジョブの失敗履歴と最終実行状態

コマンド

-- Job steps that failed / were cancelled in the last 24 hours:
SELECT j.name AS job_name, h.step_id, h.step_name,
       msdb.dbo.agent_datetime(h.run_date, h.run_time) AS run_at,
       STUFF(STUFF(RIGHT('000000' + CAST(h.run_duration AS VARCHAR(6)), 6), 3, 0, ':'), 6, 0, ':') AS duration_hhmmss,
       CASE h.run_status WHEN 0 THEN 'Failed' WHEN 1 THEN 'Succeeded' WHEN 2 THEN 'Retry'
                         WHEN 3 THEN 'Canceled' WHEN 4 THEN 'In progress' END AS status,
       h.sql_message_id, h.sql_severity,
       LEFT(h.message, 500) AS message
FROM   msdb.dbo.sysjobhistory h
  JOIN msdb.dbo.sysjobs j ON j.job_id = h.job_id
WHERE  h.run_status IN (0, 3)
  AND  msdb.dbo.agent_datetime(h.run_date, h.run_time) >= DATEADD(HOUR, -24, GETDATE())
ORDER BY run_at DESC;

-- Each job's last outcome, running now, next run:
SELECT j.name, j.enabled,
       CASE lr.run_status WHEN 0 THEN 'Failed' WHEN 1 THEN 'Succeeded' WHEN 3 THEN 'Canceled' END AS last_outcome,
       lr.run_at AS last_run_at,
       CASE WHEN a.start_execution_date IS NOT NULL AND a.stop_execution_date IS NULL THEN 'RUNNING' END AS running_now,
       a.next_scheduled_run_date
FROM   msdb.dbo.sysjobs j
  LEFT JOIN msdb.dbo.sysjobactivity a
         ON a.job_id = j.job_id AND a.session_id = (SELECT MAX(session_id) FROM msdb.dbo.syssessions)
  OUTER APPLY (SELECT TOP (1) h.run_status, msdb.dbo.agent_datetime(h.run_date, h.run_time) AS run_at
               FROM msdb.dbo.sysjobhistory h
               WHERE h.job_id = j.job_id AND h.step_id = 0
               ORDER BY h.instance_id DESC) lr
ORDER BY j.name;

-- Is the Agent service running:
SELECT servicename, status_desc, startup_type_desc FROM sys.dm_server_services;

概要

過去 24 時間に失敗またはキャンセルされたジョブステップをエラーメッセージとともに表示する。各ジョブの最終結果、現在実行中かどうか、次回のスケジュール実行、および Agent サービスの状態を表示する。

使用する場面

朝のチェック、バックアップ / メンテナンスジョブのアラート時、「夜間ジョブは実行されたか」の確認、またはフェイルオーバー後にジョブが正しいレプリカで実行されているかの確認に。

注意

msdb のジョブ履歴には上限がある (デフォルトは合計 1,000 行、ジョブあたり 100 行)。頻繁に実行されるジョブは古い失敗をすぐに押し出すため、Agent のプロパティで上限を引き上げる。message 列は 4,000 文字で切り詰められる。長い出力にはジョブステップに出力ファイルを定義する。Agent サービスが停止している場合、スケジュールされたジョブは実行されず、エラーも報告されない。読み取り専用クエリのため安全。