Home / Guides / Check SQL Agent job failures on many servers

Check SQL Server Agent job failures across many servers

Backups, index maintenance and ETL loads run as SQL Server Agent jobs, and on a Monday morning you want one answer: which of them failed, on which server, and why. SSMS shows the history one job at a time. This guide covers where the history lives, a query that lists the failures, the permissions a monitoring login needs (we tested them), PowerShell and dbatools for many servers, how long the history is kept, and where these methods stop.

Short answer.

Every run is recorded in msdb.dbo.sysjobhistory: one row per step and one row with step_id = 0 for the job's own outcome. run_status = 0 means failed, 3 canceled. Join it to msdb.dbo.sysjobs for the job name, and turn the integer run_date and run_time columns into a date. For many servers, run that query in a loop with Invoke-Sqlcmd, or use dbatools' Get-DbaAgentJobHistory -OutcomeType Failed.

A login that is not sysadmin needs SELECT on those two tables, or SQLAgentReaderRole in msdb and sp_help_jobhistory. By default SQL Server keeps only 1,000 history rows per instance and 100 per job, so a busy server forgets quickly.

Where SQL Server Agent keeps job history

SQL Server Agent writes the result of every job run into the msdb database. The columns that matter:

step_id: the step number, and 0 for the row with the outcome of the whole job (its step name is (Job outcome)).
run_status: 0 Failed, 1 Succeeded, 2 Retry, 3 Canceled, 4 In progress.
run_date and run_time: when the job or step started, as integers (20261113 and 160512 for 16:05:12), in the server's local time.
run_duration: also an integer in HHmmss form, so 130 means 1 minute 30 seconds, not 130 seconds.
sql_message_id, sql_severity, message: the error number, its severity and the text, on the step row.

Two things we saw when we made jobs fail on purpose on a test server. First, the job outcome row only says "The job failed ... The last step to run was step 1"; the real error (here, error 8134, divide by zero) is on the step row, so read both. Second, a step can fail while the job succeeds: a step set to "Go to the next step" on failure leaves a failed step row and a job outcome of Succeeded. A check that looks only at step_id = 0 misses that one. A step that is set to retry also wrote a run_status = 2 (Retry) row before its final Failed row.

Find failed jobs on one instance

Failed and canceled jobs and steps from the last 24 hours, newest first, with real start and end times (the date conversion is the one in Microsoft's example for sysjobhistory):

USE [msdb];
-- Failed and canceled jobs and steps in the last 24 hours, newest first
DECLARE @Since datetime = DATEADD(HOUR, -24, GETDATE());   -- the server's local time

SELECT  j.name AS job_name,
        h.step_id,                                         -- 0 = the job's own outcome row
        h.step_name,
        x.start_time,
        DATEADD(SECOND, x.duration_s, x.start_time) AS end_time,
        x.duration_s,
        CASE h.run_status WHEN 0 THEN 'Failed'   WHEN 1 THEN 'Succeeded' WHEN 2 THEN 'Retry'
                          WHEN 3 THEN 'Canceled' WHEN 4 THEN 'In progress' END AS outcome,
        h.sql_message_id,
        h.sql_severity,
        h.retries_attempted,
        h.message
FROM dbo.sysjobhistory AS h
JOIN dbo.sysjobs       AS j ON j.job_id = h.job_id
CROSS APPLY (SELECT DATETIMEFROMPARTS(h.run_date / 10000, h.run_date % 10000 / 100, h.run_date % 100,
                                      h.run_time / 10000, h.run_time % 10000 / 100, h.run_time % 100, 0) AS start_time,
                    (h.run_duration / 10000) * 3600 + (h.run_duration % 10000 / 100) * 60
                    + h.run_duration % 100 AS duration_s) AS x
WHERE h.run_status IN (0, 3)                               -- 0 = Failed, 3 = Canceled
  AND h.run_date >= CONVERT(int, CONVERT(char(8), @Since, 112))
  AND x.start_time >= @Since
ORDER BY x.start_time DESC, h.instance_id DESC;

Many scripts use msdb.dbo.agent_datetime(run_date, run_time) instead. It exists in msdb but is not documented on Microsoft Learn, so this guide does the arithmetic itself.

The last outcome of every job

To see which jobs are failing right now, take each job's newest outcome row. A job with NULL has no history: it never ran, or its rows were already removed (see the retention section below).

USE [msdb];
-- The last outcome of every job, failed ones first
WITH last_run AS (
    SELECT h.job_id, h.run_status, h.run_date, h.run_time, h.message,
           ROW_NUMBER() OVER (PARTITION BY h.job_id ORDER BY h.instance_id DESC) AS rn
    FROM dbo.sysjobhistory AS h
    WHERE h.step_id = 0                                    -- job outcome rows only
)
SELECT  j.name AS job_name,
        j.enabled,
        CASE l.run_status WHEN 0 THEN 'Failed' WHEN 1 THEN 'Succeeded'
                          WHEN 3 THEN 'Canceled' END AS last_outcome,   -- NULL = no history kept
        DATETIMEFROMPARTS(l.run_date / 10000, l.run_date % 10000 / 100, l.run_date % 100,
                          l.run_time / 10000, l.run_time % 10000 / 100, l.run_time % 100, 0) AS last_start,
        l.message
FROM dbo.sysjobs AS j
LEFT JOIN last_run AS l ON l.job_id = j.job_id AND l.rn = 1
ORDER BY CASE l.run_status WHEN 0 THEN 0 WHEN 3 THEN 1 ELSE 2 END, j.name;

The permissions it needs

A sysadmin can run everything above. For a monitoring login, Microsoft documents three msdb roles for SQL Server Agent: SQLAgentUserRole, SQLAgentReaderRole and SQLAgentOperatorRole. We tested them on SQL Server 2025 with a login that had no other rights:

No role: SELECT on sysjobhistory and sysjobs is denied, and so is sp_help_jobhistory.
SQLAgentUserRole: sp_help_jobhistory runs but shows only the jobs the login owns (none, in our test).
SQLAgentReaderRole: sp_help_jobhistory shows the history of every job, but a direct SELECT on the two tables is still denied.
GRANT SELECT on dbo.sysjobhistory and dbo.sysjobs: the queries in this guide work, and nothing else is opened.

So pick one of the two, depending on whether you want the procedure or the queries above (replace the login name with yours):

USE [msdb];
IF DATABASE_PRINCIPAL_ID(N'CONTOSO\svc-jobcheck') IS NULL
    CREATE USER [CONTOSO\svc-jobcheck] FOR LOGIN [CONTOSO\svc-jobcheck];

-- Option 1: the queries in this guide
GRANT SELECT ON dbo.sysjobhistory TO [CONTOSO\svc-jobcheck];
GRANT SELECT ON dbo.sysjobs       TO [CONTOSO\svc-jobcheck];

-- Option 2: sp_help_jobhistory for every job
ALTER ROLE [SQLAgentReaderRole] ADD MEMBER [CONTOSO\svc-jobcheck];

-- Rollback (only what you granted for this):
-- REVOKE SELECT ON dbo.sysjobhistory FROM [CONTOSO\svc-jobcheck];
-- REVOKE SELECT ON dbo.sysjobs       FROM [CONTOSO\svc-jobcheck];
-- ALTER ROLE [SQLAgentReaderRole] DROP MEMBER [CONTOSO\svc-jobcheck];
-- DROP USER [CONTOSO\svc-jobcheck];

With the reader role, the documented procedure lists the failures (newest first by default):

USE [msdb];
-- Failed jobs and steps, all columns (dates and times are still integers here)
EXECUTE dbo.sp_help_jobhistory @run_status = 0, @mode = N'FULL';

Check many servers at once

PowerShell: loop with Invoke-Sqlcmd

This script runs the failure query on every instance in a list with Invoke-Sqlcmd from Microsoft's SqlServer module (Install-Module SqlServer once), puts the results in one list sorted newest first, writes a CSV and reports the instances it could not reach at the end instead of stopping. By default it lists job outcomes only; -IncludeSteps adds the failed steps, which catches the "step failed, job succeeded" case and shows the real error number.

param(
    [string[]]$ServerInstance = @('SQL01', 'SQL02', 'SQL03\SALES'),
    [int]$Hours               = 24,                 # how far back to look
    [switch]$IncludeSteps,                          # also list the failed steps, not only the job outcome
    [switch]$TrustServerCertificate,                # only for servers without a trusted certificate
    [string]$CsvPath          = "$env:USERPROFILE\Desktop\AgentJobFailures.csv"
)
Import-Module SqlServer -ErrorAction Stop

$stepFilter = if ($IncludeSteps) { '' } else { 'AND h.step_id = 0' }
$query = @"
SET NOCOUNT ON;
DECLARE @Since datetime = DATEADD(HOUR, -$Hours, GETDATE());
SELECT  j.name AS job_name, h.step_id, h.step_name, x.start_time,
        x.duration_s, h.run_status, h.sql_message_id, h.sql_severity, h.message
FROM msdb.dbo.sysjobhistory AS h
JOIN msdb.dbo.sysjobs AS j ON j.job_id = h.job_id
CROSS APPLY (SELECT DATETIMEFROMPARTS(h.run_date / 10000, h.run_date % 10000 / 100, h.run_date % 100,
                    h.run_time / 10000, h.run_time % 10000 / 100, h.run_time % 100, 0) AS start_time,
                    (h.run_duration / 10000) * 3600 + (h.run_duration % 10000 / 100) * 60
                    + h.run_duration % 100 AS duration_s) AS x
WHERE h.run_status IN (0, 3)
  AND h.run_date >= CONVERT(int, CONVERT(char(8), @Since, 112))
  AND x.start_time >= @Since
  $stepFilter;
"@

$outcome = @{ 0 = 'Failed'; 3 = 'Canceled' }
$found   = [System.Collections.Generic.List[object]]::new()
$failed  = [System.Collections.Generic.List[object]]::new()

$sqlArgs = @{ Database = 'msdb'; Query = $query; QueryTimeout = 120; ErrorAction = 'Stop' }
if ($TrustServerCertificate) { $sqlArgs.TrustServerCertificate = $true }

foreach ($instance in $ServerInstance) {
    try {
        $rows = Invoke-Sqlcmd -ServerInstance $instance @sqlArgs
    }
    catch {
        $failed.Add([pscustomobject]@{ Instance = $instance; Error = $_.Exception.Message })
        continue
    }
    foreach ($r in $rows) {
        $found.Add([pscustomobject]@{
            Started  = $r.start_time
            Instance = $instance
            Job      = $r.job_name
            Step     = if ($r.step_id -eq 0) { '(job outcome)' } else { "$($r.step_id) $($r.step_name)" }
            Outcome  = $outcome[[int]$r.run_status]
            Seconds  = $r.duration_s
            Error    = if ($r.sql_message_id -gt 0) { $r.sql_message_id } else { $null }
            Message  = $r.message
        })
    }
}

$sorted = $found | Sort-Object Started -Descending
$sorted | Format-Table Started, Instance, Job, Step, Outcome, Error -AutoSize
$sorted | Export-Csv -Path $CsvPath -NoTypeInformation -Encoding UTF8
"{0} failed or canceled runs on {1} instances in the last {2} hours, written to {3}" -f `
    $found.Count, $ServerInstance.Count, $Hours, $CsvPath

foreach ($f in $failed) { Write-Warning "$($f.Instance): $($f.Error)" }

Save it as C:\Scripts\Get-AgentJobFailures.ps1 and run it with your own instance names:

C:\Scripts\Get-AgentJobFailures.ps1 -ServerInstance SQL01, SQL02, 'SQL03\SALES'

# The instance list from a file (one per line), the last 3 days, failed steps too:
C:\Scripts\Get-AgentJobFailures.ps1 -ServerInstance (Get-Content C:\Scripts\instances.txt) -Hours 72 -IncludeSteps

-ServerInstance: the instances, as you would type them in SSMS (SERVER or SERVER\INSTANCE).
-Hours: how far back to look, by the server's own clock. The default is 24.
-IncludeSteps: list failed and canceled steps as well as job outcomes.
-TrustServerCertificate: accept a server certificate your PC does not trust (see Common errors).
-CsvPath: where the results go.

dbatools: Get-DbaAgentJobHistory

The free, community-run dbatools module has a command for this. It takes several instances, -StartDate, -EndDate, -Job, -OutcomeType (Failed, Cancelled and others) and -ExcludeJobSteps for outcome rows only, and returns real dates and durations.

# Install-Module dbatools -Scope CurrentUser    (once)
Get-DbaAgentJobHistory -SqlInstance SQL01, SQL02, 'SQL03\SALES' -OutcomeType Failed -ExcludeJobSteps -StartDate (Get-Date).AddDays(-1) |
    Sort-Object StartDate -Descending |
    Select-Object StartDate, SqlInstance, Job, Status, Message |
    Export-Csv "$env:USERPROFILE\Desktop\AgentJobFailures.csv" -NoTypeInformation -Encoding UTF8

Tested on Windows 11 with SQL Server 2025 (17.0.5045.3): we ran jobs that fail, fail after a retry, fail one step but succeed, and succeed, then checked the raw history rows, both queries, the permissions above with a test login, the PowerShell script (SqlServer module 22.4.5.1, two instances and an unreachable name), Get-DbaAgentJobHistory (dbatools 2.9.1) and the Application log entries. The notification statement below was only parsed.

Get told when a job fails (built in)

SQL Server Agent can e-mail an operator when a job fails. It needs Database Mail set up and SQL Server Agent's alert system pointed at a mail profile, then one setting per job (in SSMS: the job's Notifications page, When the job fails):

USE [msdb];
-- E-mail the operator "DBA Team" when this job fails (Database Mail must be set up)
EXECUTE dbo.sp_update_job @job_name = N'Nightly backup',
                          @notify_level_email = 2,
                          @notify_email_operator_name = N'DBA Team';

@notify_level_email is 0 never, 1 on success, 2 on failure, 3 always. It is set job by job and server by server, a new job lacks it unless someone remembers, and if mail is broken nobody hears about the failure.

One more built-in trace: a job's Write to the Windows Application event log setting defaults to When the job fails (@notify_level_eventlog = 2 in sp_add_job). On our test server each failed run wrote a Warning with source SQLSERVERAGENT and event ID 208 ("SQL Server Scheduled Job '...' - Status: Failed"), so an Event Log search across servers finds those failures too, as long as nobody turned the setting off.

How long SQL Server keeps job history

SQL Server Agent limits the history by rows, not by days. In SQL Server Agent, Properties, History, Maximum job history log size is 1,000 rows for the whole instance by default and Maximum job history rows per job is 100 (we read the same values on our test server; on Azure SQL Managed Instance they are fixed at those defaults). When a limit is reached the oldest rows are removed.

Each run writes one row per step plus the outcome row. A two-step job that runs every 10 minutes writes 18 rows an hour, so its 100 rows cover about five and a half hours. A failure on Saturday night can be gone by Monday morning, together with the error text. Raise the limits on busy servers, or keep a copy somewhere else.

Common errors

"The SELECT permission was denied on the object 'sysjobhistory', database 'msdb', schema 'dbo'." The login has no SELECT on the table. SQLAgentReaderRole alone does not give it: grant SELECT, or use sp_help_jobhistory.
"The EXECUTE permission was denied on the object 'sp_help_jobhistory'." The login is in none of the three SQL Server Agent roles in msdb.
"The certificate chain was issued by an authority that is not trusted." We got this from Invoke-Sqlcmd (SqlServer module 22.4.5.1) against a server with a self-signed certificate. The real fix is a trusted certificate on the server; -TrustServerCertificate skips the check and is only reasonable on a network you trust. dbatools has the same check; Set-DbatoolsConfig -FullName sql.connection.trustcert -Value $true turns it off for your session.
No rows on an instance that surely has failures. Check that SQL Server Agent is running there and that the history was not already removed by the row limits. SQL Server Express and LocalDB have no SQL Server Agent, so they have no job history.

Where these methods stop

Only what is still in msdb. With the default row limits, the history of a busy server covers hours or days, not weeks.
A check, not a record. Each run reads the servers again. Nothing remembers what you saw last week unless you keep the CSV files.
Job history is one log among several. Why a job failed is often in the SQL Server error log, the Agent log, the job's output file or the Windows Event Log, which are separate queries with their own permissions.
Every server needs its grant, and a server that is down or unreachable only shows up as a warning in your script.

When you want job failures kept and searchable

LogTrawler reads SQL Agent job history, together with the SQL Server error log and the SQL Agent log, from every instance you add, on a schedule and with read-only queries, starting with a look-back you choose when you add the instance. Each poll reads only the history rows that are new since the last one. Every job outcome and every step becomes a record: failed runs as errors, retries and canceled runs as warnings, with the job name, step, duration, retries and the server's message, and the text of the SQL Server error number from the server's own sys.messages.

It keeps the records in its own store on one computer on your network, one file per day, for as long as you set, so a failure is still there after SQL Server Agent has removed the row. One search covers all your servers, filtered by time, server, level and log type, together with Windows Event Logs and log files. A script your DBA can review grants only the read rights each log needs (for job history: SELECT on those two msdb tables), and Test connection shows what the login can read. Nothing is installed on the servers.

What it does not do: it does not alert you, and it is not real-time (it reads on its interval). If you only check a few servers now and then, the script above is enough and costs nothing.

About LogTrawler Download the free 7-day trial Get it from Microsoft Store

Price: $59 / year - includes 2 licensesLicense terms Refund policy

Questions

Where is SQL Server Agent job history stored?

In the msdb database, in the table dbo.sysjobhistory, with the job names in dbo.sysjobs. Every run writes one row per step and one row with step_id 0 for the outcome of the whole job. run_status 0 is Failed, 1 Succeeded, 2 Retry, 3 Canceled and 4 In progress.

How do I convert run_date and run_time to a datetime?

Both are integers: run_date is yyyyMMdd and run_time is HHmmss, in the server's local time. DATETIMEFROMPARTS(run_date / 10000, run_date % 10000 / 100, run_date % 100, run_time / 10000, run_time % 10000 / 100, run_time % 100, 0) gives a datetime; this is the method in Microsoft's example for sysjobhistory. run_duration is HHmmss as well, not seconds.

What permission do I need to read job history without sysadmin?

Either SELECT on msdb.dbo.sysjobhistory and msdb.dbo.sysjobs, which lets you query the tables directly, or membership in SQLAgentReaderRole in msdb, which lets sp_help_jobhistory show the history of every job. In our test on SQL Server 2025 the reader role alone did not allow a direct SELECT on the tables, and SQLAgentUserRole showed only the jobs the login owns.

Why is my job history missing or so short?

SQL Server Agent keeps 1,000 history rows per instance and 100 per job by default and removes the oldest rows when a limit is reached. Every run writes a row per step plus one for the job, so a frequent job uses up its 100 rows in hours. Raise the limits under SQL Server Agent, Properties, History, or copy the history somewhere else.

Can I see failed SQL Agent jobs on all my servers at once?

Yes. Run the failure query on each instance in a PowerShell loop with Invoke-Sqlcmd, or use dbatools' Get-DbaAgentJobHistory -SqlInstance with several instances and -OutcomeType Failed, and export one sorted list. To keep the history after SQL Server removes it and search it together with error logs and Windows Event Logs, use a collector such as LogTrawler.

Sources (Microsoft Learn): dbo.sysjobhistory, sp_help_jobhistory, SQL Server Agent fixed database roles, View the job history, SQL Server Agent Properties (History page), Resize the job history log, SQL Agent jobs in Azure SQL Managed Instance (history limits), sp_update_job, sp_add_job, Notify an operator of job status, Editions and supported features of SQL Server 2025 (SQL Server Agent), Invoke-Sqlcmd. dbatools: Get-DbaAgentJobHistory.