Historial de fallos de jobs de SQL Agent y estado de la última ejecución

Comando

-- 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;

Qué hace

Muestra los pasos de jobs que fallaron o se cancelaron en las últimas 24 horas con su mensaje de error; el último resultado de cada job, si se está ejecutando ahora y su próxima ejecución programada; y el estado del servicio Agent.

Cuándo usarlo

En la revisión matutina, ante una alerta de un job de backup/mantenimiento, para "¿se ejecutó el job nocturno?" o tras un failover para verificar que los jobs se ejecutan en la réplica correcta.

Precaución

El historial de jobs en msdb es limitado (por defecto 1,000 filas en total, 100 por job); los jobs que se ejecutan con frecuencia desplazan rápidamente los fallos antiguos, así que aumente el límite en las propiedades de Agent. La columna message se trunca a 4,000 caracteres; defina un archivo de salida en el paso del job para salidas largas. Si el servicio Agent está detenido, no se ejecuta ningún job programado y ninguno reporta error. Consulta de solo lectura; segura.