SQL Agent jobs that failed in the last 7 days, what is running now, and the last outcome of every job. Part of my SQL Server health check: run Health Check 00 for a one-page summary, then the numbered scripts for the detail behind each area. Read-only, changes nothing. Tested on SQL Server 2017 and 2025.

-- 05_Agent_Jobs.sql - failed jobs in the last 7 days, what is running now, and each job's last outcome. Read-only.
SET NOCOUNT ON;
SELECT j.name AS job, h.step_id, h.step_name, msdb.dbo.agent_datetime(h.run_date, h.run_time) AS run_at,
       STUFF(STUFF(RIGHT('000000' + CONVERT(varchar(6), h.run_duration), 6), 5, 0, ':'), 3, 0, ':') AS duration,
       LEFT(h.message, 1000) AS message
FROM msdb.dbo.sysjobhistory AS h JOIN msdb.dbo.sysjobs AS j ON j.job_id = h.job_id
WHERE h.run_status = 0 AND msdb.dbo.agent_datetime(h.run_date, h.run_time) > DATEADD(DAY, -7, GETDATE())
ORDER BY run_at DESC;

SELECT j.name AS running_now, ja.start_execution_date, DATEDIFF(MINUTE, ja.start_execution_date, GETDATE()) AS minutes
FROM msdb.dbo.sysjobactivity AS ja JOIN msdb.dbo.sysjobs AS j ON j.job_id = ja.job_id
WHERE ja.session_id = (SELECT MAX(session_id) FROM msdb.dbo.syssessions)
  AND ja.start_execution_date IS NOT NULL AND ja.stop_execution_date IS NULL;

SELECT j.name AS job, j.enabled, SUSER_SNAME(j.owner_sid) AS owner,
       CASE js.last_run_outcome WHEN 0 THEN 'Failed' WHEN 1 THEN 'Succeeded' WHEN 3 THEN 'Cancelled' WHEN 5 THEN 'Unknown' END AS last_outcome,
       CASE WHEN js.last_run_date > 0 THEN msdb.dbo.agent_datetime(js.last_run_date, js.last_run_time) END AS last_run,
       o.name AS notifies_operator
FROM msdb.dbo.sysjobs AS j
LEFT JOIN msdb.dbo.sysjobservers AS js ON js.job_id = j.job_id
LEFT JOIN msdb.dbo.sysoperators AS o ON o.id = j.notify_email_operator_id
ORDER BY j.name;