Home / Databases / SQL Server

DatabasesCheat sheetIntermediate

A map of the msdb tables behind SQL Server Agent: jobs, steps, schedules, activity and history

Which msdb table holds what for SQL Server Agent, how they join, and ready-to-use queries for the questions you'll ask most. Jobs with steps, schedules per job, running jobs, last outcome per step, operators and alerts.

Deepak Bhardwaj3 min read

Applies toSQL Server 2016–2025, Azure SQL Managed Instance

The tables

Table Holds Key
sysjobs One row per job: name, owner, enabled, category, notification settings job_id
sysjobsteps Steps: command, subsystem, database, flow, retries, proxy, output file job_id, step_id
sysschedules Schedule definitions (can be shared) schedule_id
sysjobschedules Job ↔ schedule link plus next_run_date/time job_id, schedule_id
sysjobhistory Run results; step_id = 0 is the job outcome instance_id
sysjobactivity Current/last run per job per Agent session session_id, job_id
syssessions One row per Agent service start session_id
sysjobstepslogs Step output when "Log to table" is set step_uid
syscategories Job, alert and operator categories category_id
sysoperators Operators (email/pager/net send) id
sysalerts Alerts: severity, message id, performance condition, job to run id
sysnotifications Which operator gets which alert alert_id, operator_id
sysproxies, sysproxysubsystem, sysproxylogin Proxies, allowed subsystems, who can use them proxy_id 
syscategories ─┐
sysoperators ──┤
               sysjobs ──< sysjobsteps >── sysproxies
                  │  └──< sysjobschedules >── sysschedules
                  ├──< sysjobhistory
                  └──< sysjobactivity >── syssessions
sysalerts ──< sysnotifications >── sysoperators

Jobs with their steps

SELECT j.name, s.step_id, s.step_name, s.subsystem, s.database_name,
       s.on_success_action, s.on_fail_action, s.retry_attempts, s.output_file_name
FROM msdb.dbo.sysjobs j JOIN msdb.dbo.sysjobsteps s ON s.job_id = j.job_id
ORDER BY j.name, s.step_id;

Flow action codes: 1 = quit with success, 2 = quit with failure, 3 = go to next step, 4 = go to step on_*_step_id.

Schedules per job in plain words

sysschedules stores freq_type (1 once, 4 daily, 8 weekly, 16 monthly, 32 monthly relative, 64 on Agent start, 128 when idle), freq_interval (meaning depends on type; for weekly it's a day bitmask: 1 = Sun, 2 = Mon, 4 = Tue ...) and freq_subday_type (1 at time, 2 seconds, 4 minutes, 8 hours).

SELECT j.name, s.name AS schedule, s.freq_type, s.freq_interval,
       s.freq_subday_type, s.freq_subday_interval, s.active_start_time,
       js.next_run_date, js.next_run_time
FROM msdb.dbo.sysjobs j
JOIN msdb.dbo.sysjobschedules js ON js.job_id = j.job_id
JOIN msdb.dbo.sysschedules s ON s.schedule_id = js.schedule_id;

For readable descriptions, see the schedule report post.

Running right now

SELECT j.name, a.start_execution_date,
       DATEDIFF(MINUTE, a.start_execution_date, GETDATE()) AS minutes_running,
       a.last_executed_step_id
FROM msdb.dbo.sysjobactivity a
JOIN msdb.dbo.sysjobs j ON j.job_id = a.job_id
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;

Always filter on the latest session_id, or you'll see "running" jobs left over from before an Agent restart.

Last outcome of every step

WITH last AS (
  SELECT h.*, ROW_NUMBER() OVER (PARTITION BY h.job_id, h.step_id ORDER BY h.instance_id DESC) rn
  FROM msdb.dbo.sysjobhistory h)
SELECT j.name, l.step_id, l.step_name, l.run_status, l.run_date, l.run_time, LEFT(l.message, 300) AS message
FROM last l JOIN msdb.dbo.sysjobs j ON j.job_id = l.job_id
WHERE l.rn = 1 ORDER BY j.name, l.step_id;

Alerts and who they notify

SELECT a.name, a.severity, a.message_id, a.enabled, a.delay_between_responses,
       o.name AS operator, o.email_address
FROM msdb.dbo.sysalerts a
LEFT JOIN msdb.dbo.sysnotifications n ON n.alert_id = a.id
LEFT JOIN msdb.dbo.sysoperators o ON o.id = n.operator_id;

Caution

Read these tables freely, but change jobs only through the sp_add_* / sp_update_* procedures. Direct UPDATEs on msdb tables bypass Agent's cache, so Agent may not see the change until it restarts.

Related fixes