Exploring SQL Server Agent Job Metadata in MSDB
SQL Server Agent manages scheduled tasks, often referred to as jobs. Understanding how these jobs are structured and executed involves querying several system tables in the msdb database. Below, we provide a breakdown of key tables and a useful query to explore job step metadata.
Key System Tables in MSDB
1. dbo.sysjobs
This table stores metadata about each SQL Server Agent job, such as the job ID, name, and owner. Every scheduled task configured in SQL Server Agent is recorded here.
2. dbo.sysjobsteps
Each job can contain multiple steps, and this table holds detailed information about every step within each job, including the type of operation it performs, the command it executes, and its success or failure behavior.
3. dbo.sysproxies
This table defines SQL Server Agent proxy accounts. Proxy accounts allow specific job steps to run under different security contexts, which is useful for accessing external resources securely.
Query: Retrieve Detailed Job Step Information
The following SQL query provides a detailed breakdown of each step within a specific job. It shows the job and step IDs, step names, execution type, associated proxy account, command being executed, and the configured actions on success or failure.
-->> Job Step Details
SELECT
sJOB.job_id AS JobID,
sJOB.name AS JobName,
sJSTP.step_uid AS StepID,
sJSTP.step_id AS StepNo,
sJSTP.step_name AS StepName,
CASE sJSTP.subsystem
WHEN 'ActiveScripting' THEN 'ActiveX Script'
WHEN 'CmdExec' THEN 'Operating system (CmdExec)'
WHEN 'PowerShell' THEN 'PowerShell'
WHEN 'Distribution' THEN 'Replication Distributor'
WHEN 'Merge' THEN 'Replication Merge'
WHEN 'QueueReader' THEN 'Replication Queue Reader'
WHEN 'Snapshot' THEN 'Replication Snapshot'
WHEN 'LogReader' THEN 'Replication Transaction-Log Reader'
WHEN 'ANALYSISCOMMAND' THEN 'SQL Server Analysis Services Command'
WHEN 'ANALYSISQUERY' THEN 'SQL Server Analysis Services Query'
WHEN 'SSIS' THEN 'SQL Server Integration Services Package'
WHEN 'TSQL' THEN 'Transact-SQL script (T-SQL)'
ELSE sJSTP.subsystem
END AS StepType,
sPROX.name AS RunAs,
sJSTP.database_name AS Database,
sJSTP.command AS ExecutableCommand,
CASE sJSTP.on_success_action
WHEN 1 THEN 'Quit the job reporting success'
WHEN 2 THEN 'Quit the job reporting failure'
WHEN 3 THEN 'Go to the next step'
WHEN 4 THEN 'Go to Step: ' + QUOTENAME(CAST(sJSTP.on_success_step_id AS VARCHAR(3))) + ' ' + sOSSTP.step_name
END AS OnSuccessAction,
sJSTP.retry_attempts AS RetryAttempts,
sJSTP.retry_interval AS [RetryInterval (Minutes)],
CASE sJSTP.on_fail_action
WHEN 1 THEN 'Quit the job reporting success'
WHEN 2 THEN 'Quit the job reporting failure'
WHEN 3 THEN 'Go to the next step'
WHEN 4 THEN 'Go to Step: ' + QUOTENAME(CAST(sJSTP.on_fail_step_id AS VARCHAR(3))) + ' ' + sOFSTP.step_name
END AS OnFailureAction
FROM msdb.dbo.sysjobsteps AS sJSTP
INNER JOIN msdb.dbo.sysjobs AS sJOB
ON sJSTP.job_id = sJOB.job_id
LEFT JOIN msdb.dbo.sysjobsteps AS sOSSTP
ON sJSTP.job_id = sOSSTP.job_id AND sJSTP.on_success_step_id = sOSSTP.step_id
LEFT JOIN msdb.dbo.sysjobsteps AS sOFSTP
ON sJSTP.job_id = sOFSTP.job_id AND sJSTP.on_fail_step_id = sOFSTP.step_id
LEFT JOIN msdb.dbo.sysproxies AS sPROX
ON sJSTP.proxy_id = sPROX.proxy_id
WHERE sJOB.name = 'your database name'
ORDER BY JobName, StepNo;
When to Use This Query
This script is especially helpful when auditing jobs, troubleshooting execution flows, or reviewing complex step logic. The classification of StepType, RunAs, and success/failure actions allows for a detailed understanding of how SQL Server Agent is configured to execute tasks.
