SQL Server Job Execution History: The T-SQL Query Guide

Every DBA eventually needs to answer "did last night's job actually succeed?" — and SQL Server Agent doesn't make that trivially easy. The data exists, but it's stored in a shape that resists a quick glance. This is a practical reference for running a proper SQL Server job execution history query, understanding what the codes mean, and knowing where this manual approach stops being enough.

Where SQL Server Keeps Job History (and Why It's Awkward to Read)

Job execution history lives in msdb.dbo.sysjobhistory, a system table that SQL Server Agent writes to every time a job runs. It writes one row per step, plus a separate summary row for the job as a whole, identified by step_id = 0. A four-step job can generate five rows per run: four step-level entries and one job-level rollup.

That structure trips people up, but the bigger annoyance is date and time formatting. run_date and run_time are stored as integers — run_date as YYYYMMDD and run_time as HHMMSS with no leading zeros — rather than as a proper datetime. run_duration follows the same pattern, packed as HHMMSS representing elapsed time, not an actual duration type. So a raw SELECT * FROM msdb.dbo.sysjobhistory returns technically complete but practically unreadable data: dates like 20240315 and durations like 132 (meaning 1 minute, 32 seconds) sitting in plain integer columns. You need to convert both before the table tells you anything useful at a glance. Full column definitions and the official run_status code list are documented in Microsoft's sysjobhistory reference.

The Query: Job History With Readable Dates, Durations, and Status

The fix is a join between sysjobs and sysjobhistory, using the built-in agent_datetime function to reconstruct a real datetime from run_date and run_time, and a bit of string math to turn run_duration into hours, minutes, and seconds. Here's a sql agent job history query that covers the essentials, filtered to job-level rows only:

SELECT
    j.name AS job_name,
    h.run_status,
    agent_datetime(h.run_date, h.run_time) AS run_datetime,
    STUFF(STUFF(RIGHT('000000' + CAST(h.run_duration AS VARCHAR(6)), 6), 3, 0, ':'), 6, 0, ':') AS run_duration_hms,
    h.message
FROM msdb.dbo.sysjobhistory h
JOIN msdb.dbo.sysjobs j
    ON h.job_id = j.job_id
WHERE h.step_id = 0
ORDER BY run_datetime DESC;

The step_id = 0 filter keeps this a job execution history query rather than a step-by-step log dump — it reflects the outcome of the entire job, not one step inside it. Drop the filter and you'll see every step's individual result too, useful for diagnosing where a job failed, but noisy if you just want a summary of what ran and when.

Decoding run_status: Failed, Succeeded, Retry, Canceled, In Progress

run_status is an integer, and the five possible values are fixed and documented:

• 0 — Failed
• 1 — Succeeded
• 2 — Retry
• 3 — Canceled
• 4 — In Progress

Filtering for failures is just a WHERE clause added to the query above:

WHERE h.step_id = 0
  AND h.run_status = 0
ORDER BY run_datetime DESC;

This is the query most DBAs actually want first thing in the morning — a clean sql server job failed history list, sorted newest first, without wading through successful runs to find the one that broke.

Using sp_help_jobhistory Instead of Querying Tables Directly

If you'd rather not hand-write joins, SQL Server ships a stored procedure for this exact purpose. sp_help_jobhistory wraps the same underlying data with friendlier parameters. A practical sp_help_jobhistory example:

EXEC msdb.dbo.sp_help_jobhistory
    @job_name = 'Nightly ETL Load',
    @mode = 'FULL',
    @run_status = 0;

@mode = 'FULL' returns extended columns (including the operator email fields and retry counts) beyond the default output, and @run_status filters by the same 0–4 codes covered above. Full parameter syntax is in Microsoft's sp_help_jobhistory documentation. If you'd rather skip T-SQL entirely, SSMS offers the same data through Job Activity Monitor → right-click a job → View History, a GUI approach that Database.Guide's roundup of methods covers alongside the table and stored-procedure options — useful for checking a single job ad hoc rather than scripting a report.

Where This Approach Falls Short

Querying sysjobhistory works, but has real limits worth planning around. History isn't kept forever — SQL Server Agent caps retention by row count and age, and administrators routinely run sp_purge_jobhistory to clean old entries, so a job that failed three months ago may simply be gone by the time someone asks about it. Long error messages get truncated at 4000 characters in the message column, which can cut off exactly the stack trace detail you need. And the run_date/run_time integer formatting means every ad hoc query needs the same conversion logic rebuilt or copy-pasted.

The bigger structural issue is that this is entirely pull-based. Nothing in sysjobhistory pages anyone. SQL Server Agent job monitoring through this table only produces an answer when someone actively runs the query, opens SSMS, or checks a scheduled report — there's no push notification wired into the table itself. If a critical job fails at 2 a.m. and nobody looks until 9, that's seven hours of silent failure with a perfectly accurate row sitting in msdb the whole time.

When You Need Push Alerts Instead of Pull Queries

That gap is exactly where pull-based history querying stops being sufficient for anything time-sensitive. Teams running HTTP-triggered jobs, hybrid pipelines that mix SQL Agent with app-level schedulers, or cron-based tasks alongside SQL jobs need something that notices failure the moment it happens, not the next time someone remembers to check.

That's the specific job Cronevra does: cron job monitoring with automatic job failure alerts and execution history, built for jobs triggered over HTTP rather than tied to SQL Agent's internal scheduler — no hand-rolled T-SQL, no dashboard to remember to open. If your scheduled work spans a SQL job kicked off by an API call, a nightly script, and a few app-level cron tasks, that's a mix sysjobhistory alone can't cover. Check Cronevra's pricing if you're ready to stop querying for failures and start getting told about them.

Frequently Asked Questions

How do I see why a SQL Server job failed last night?

Query msdb.dbo.sysjobhistory filtered to step_id = 0 and run_status = 0, joined to sysjobs for the job name, and check the message column for the error text. If the message looks truncated, check the step-level rows (step_id > 0) for the specific step that failed, since the job-summary row's message can be shorter than the step's actual error output.

What's the difference between sysjobhistory and sp_help_jobhistory?

sysjobhistory is the raw system table you query directly with your own joins and formatting; sp_help_jobhistory is a stored procedure that wraps the same data with built-in filtering parameters like @job_name, @mode, and @run_status. The stored procedure is faster for ad hoc checks; direct table queries are better when you need custom formatting or a repeatable report.

Why does sysjobhistory show a row with step_id 0?

The step_id = 0 row is the job-level summary, reflecting the outcome of the entire job run rather than any single step. Every other row with step_id > 0 represents an individual step's result, so a four-step job produces four step rows plus one summary row per execution.

How long does SQL Server keep job history by default?

SQL Server Agent caps history by both total row count and per-job row count, purging older entries automatically once those caps are hit, and DBAs also run sp_purge_jobhistory manually to clear old rows. There's no fixed time-based retention out of the box — a job that runs frequently can lose its older history faster than a job that runs rarely.

Can I get SQL Server job failures pushed to Slack or email automatically?

Not from sysjobhistory itself — it's a passive table with no built-in push mechanism, so someone has to run a query or open SSMS to see a failure. Tools like Cronevra close that gap for HTTP-triggered and cron-based jobs by sending automatic failure alerts and keeping execution history without manual querying.

What do the run_status numbers in sysjobhistory mean?

run_status is an integer from 0 to 4: 0 is Failed, 1 is Succeeded, 2 is Retry, 3 is Canceled, and 4 is In Progress. These codes apply to both the job-summary row and individual step rows, so the same value set covers both levels of detail.