Monitoring SQL Server Agent Jobs: A Deep Dive into Job Steps with T-SQL

SQL Server Agent Jobs are the backbone of automation in SQL Server environments—whether it’s backing up databases, performing ETL operations, or maintaining indexes. As environments grow, so do the number of jobs. Keeping track of what each job does, which database it affects, and whether it is enabled or disabled becomes essential.

In this blog, we’ll walk through a powerful T-SQL script that extracts all job steps, their commands, execution logic, associated databases, and status across all SQL Server Agent jobs.

The Challenge

Often, DBAs are asked:

• Which job steps are targeting a specific database?
• What T-SQL or commands are being executed in each step?
• Which jobs are currently enabled or disabled?
• What happens if a job step succeeds or fails?

Instead of clicking through the SQL Server Management Studio (SSMS) GUI for every job, wouldn’t it be great to query this all at once?

The Solution: A Comprehensive T-SQL Query

Below is a script you can run on your SQL Server instance to get full visibility into every job step configured in the SQL Server Agent.

USE msdb;
GO

SELECT
    j.job_id,
    j.name AS JobName,
    CASE j.enabled
        WHEN 1 THEN 'Enabled'
        ELSE 'Disabled'
    END AS JobStatus,
    s.step_id,
    s.step_name,
    s.database_name,
    s.subsystem,
    s.command,
    s.on_success_action,
    s.on_fail_action,
    s.server,
    CASE s.on_success_action
        WHEN 1 THEN 'Quit with success'
        WHEN 2 THEN 'Quit with failure'
        WHEN 3 THEN 'Go to next step'
        WHEN 4 THEN 'Go to step: ' + CAST(s.on_success_step_id AS VARCHAR)
        ELSE 'Other'
    END AS OnSuccessActionDescription,
    CASE s.on_fail_action
        WHEN 1 THEN 'Quit with success'
        WHEN 2 THEN 'Quit with failure'
        WHEN 3 THEN 'Go to next step'
        WHEN 4 THEN 'Go to step: ' + CAST(s.on_fail_step_id AS VARCHAR)
        ELSE 'Other'
    END AS OnFailActionDescription
FROM
    msdb.dbo.sysjobs j
INNER JOIN
    msdb.dbo.sysjobsteps s ON j.job_id = s.job_id
ORDER BY
    j.name,
    s.step_id;

Column Breakdown

Here’s what each column tells you: