What is Agent doing right now? Running jobs, current steps and next run times from T-SQL

#Is Agent running?

SELECT servicename, status_desc, startup_type_desc, last_startup_time, service_account
FROM sys.dm_server_services;

Or, from msdb, when Agent last started:

SELECT TOP (1) agent_start_date FROM msdb.dbo.syssessions ORDER BY session_id DESC;

#Jobs running now, with current step

SELECT j.name AS job_name,
       a.start_execution_date,
       DATEDIFF(MINUTE, a.start_execution_date, GETDATE()) AS minutes_running,
       ISNULL(a.last_executed_step_id, 0) + 1 AS current_step_id,
       s.step_name AS current_step
FROM msdb.dbo.sysjobactivity a
JOIN msdb.dbo.sysjobs j ON j.job_id = a.job_id
LEFT JOIN msdb.dbo.sysjobsteps s
       ON s.job_id = a.job_id AND s.step_id = ISNULL(a.last_executed_step_id, 0) + 1
WHERE a.session_id = (SELECT MAX(session_id) FROM msdb.dbo.syssessions)
  AND a.start_execution_date IS NOT NULL
  AND a.stop_execution_date IS NULL
ORDER BY a.start_execution_date;

last_executed_step_id + 1 is the current step in a simple linear job. With "go to step N" flow it's an estimate.

#What the step's session is doing

T-SQL job steps connect with a program name that contains the job id in hex:

SELECT j.name AS job_name, es.session_id, r.status, r.command, r.wait_type, r.wait_time,
       r.blocking_session_id, r.cpu_time, r.logical_reads, t.text
FROM sys.dm_exec_sessions es
JOIN sys.dm_exec_requests r ON r.session_id = es.session_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
JOIN msdb.dbo.sysjobs j
  ON es.program_name LIKE 'SQLAgent - TSQL JobStep (Job ' + CONVERT(varchar(34), CONVERT(binary(16), j.job_id), 1) + '%'
WHERE es.program_name LIKE 'SQLAgent - TSQL JobStep%';

Blocking (blocking_session_id) and wait types are where long-running jobs usually reveal themselves.

#Next scheduled runs

SELECT TOP (25) j.name, s.name AS schedule_name,
       DATETIMEFROMPARTS(js.next_run_date / 10000, js.next_run_date / 100 % 100, js.next_run_date % 100,
                         js.next_run_time / 10000, js.next_run_time / 100 % 100, js.next_run_time % 100, 0) AS next_run
FROM msdb.dbo.sysjobschedules js
JOIN msdb.dbo.sysjobs j ON j.job_id = js.job_id
JOIN msdb.dbo.sysschedules s ON s.schedule_id = js.schedule_id
WHERE j.enabled = 1 AND s.enabled = 1 AND js.next_run_date > 0
ORDER BY next_run;

sysjobschedules.next_run_* is refreshed by Agent every 20 minutes or so. For an exact value, sysjobactivity.next_scheduled_run_date in the current session is also available.

#The documented procedure

EXEC msdb.dbo.sp_help_job @execution_status = 1;   -- 1 = executing

Returns the same idea in a fixed format; current_execution_step is included.

#Giving this to non-sysadmins

#Make it a quick check

Save the "running now" and "what is it waiting on" queries as SSMS snippets, or as a single procedure dbo.usp_AgentNow in your DBA database. During an incident, one call tells you whether jobs are part of the problem.

Tip

Always filter sysjobactivity to the latest session_id. Rows from earlier Agent sessions can show jobs as "running" that were killed by a restart.