SQL Agent Job Failed: Finding the Step That Actually Broke

Step 1: Read the Step Row, Not the Job Row

Everything you need is in msdb.dbo.sysjobhistory, and the row most people read is the wrong one. A run writes 1 row per step plus 1 outcome row, and the outcome row is step_id = 0. That row exists to record that the run ended and which step it ended on. It has never carried an error message. The step rows do, so filter them in.

SELECT TOP (20)
       j.name                                           AS job_name,
       h.step_id,
       h.step_name,
       msdb.dbo.agent_datetime(h.run_date, h.run_time)  AS run_at,
       h.run_duration,
       h.message
FROM   msdb.dbo.sysjobhistory AS h
JOIN   msdb.dbo.sysjobs       AS j ON j.job_id = h.job_id
WHERE  h.run_status = 0        -- 0 = Failed, 1 = Succeeded, 2 = Retry, 3 = Cancelled
  AND  h.step_id   > 0         -- drop the job outcome row, it has no cause in it
ORDER  BY h.instance_id DESC;

The difference is not subtle. These 2 rows are the same run of the same job, the outcome row first and the step row second.

Note the Executed as user: prefix on the step row. That is the security context the step actually ran in, and it is the single most useful thing on the line, because it is often not the context you assumed. Get SQL Agent Job Failure Summary runs this across the whole instance for the last 7 days, which is the version worth keeping, because job history is only useful when you read it without having to go looking. The table itself is documented in MS Docs for sysjobhistory.

Step 2: The Owner the Server Can No Longer Resolve

This one is unusual, because the failure is recorded on the job outcome row rather than on a step. There is no step error at all, since no step was allowed to start.

A job stores its owner as a SID in msdb.dbo.sysjobs.owner_sid, not as a name. When that SID stops resolving, usually because the Windows or domain account behind it was deleted or renamed, or because Agent cannot reach a domain controller to ask, Agent cannot establish whether the owner is still allowed to run anything, and it refuses rather than guessing. The underlying message is 15404, severity 16, and on the instance it reads Could not obtain information about Windows NT group/user '%ls', error code %#lx.

Find every job in that state in 1 query, no job name needed:

SELECT j.name,
       j.owner_sid,
       SUSER_SNAME(j.owner_sid) AS owner_login   -- NULL means it no longer resolves
FROM   msdb.dbo.sysjobs AS j
WHERE  SUSER_SNAME(j.owner_sid) IS NULL;

The fix is to reassign the owner, with EXEC msdb.dbo.sp_update_job @job_name = N'...', @owner_login_name = N'sa'; or, better, a dedicated service login that nobody is going to decommission. Worth knowing: a plain DROP LOGIN will not create this state, because SQL Server blocks it with Msg 15170 ... This login is the owner of 1 job(s). You must delete or reassign these jobs before the login can be dropped. The orphan almost always arrives from the Windows side, where nothing consults SQL Server first. Get Login and Job Inventory lists every job with its owner so you can see which ones hang off a human account, and SQL Server Login Migration: What Gets Silently Left Behind is the same failure arriving a week after a server move.

Step 3: The Proxy, and the Credential Behind It

If the message begins Unable to start execution of step, the step never ran. There is no command output to read because no command was issued, and chasing the T-SQL or the batch file is wasted time.

A proxy is a name over a credential, and the credential holds a Windows account and a password. The password is a copy. When that account’s password is rotated in Active Directory, nothing updates the credential, so every job step that runs under the proxy starts failing at the same moment, which is the giveaway: several unrelated jobs breaking on the same night usually means 1 credential, not several bugs. Repoint it with ALTER CREDENTIAL and the current password, then run 1 step by hand to confirm. The proxy model itself is in MS Docs on creating an Agent proxy.

Get Audit Specifications, DDL Triggers, and Proxy Credentials lists every proxy with the credential and identity behind it, which is also the review you want for a different reason: a proxy lets a job step run under an account more privileged than the login that owns the job.

Step 4: The Permission the Step Actually Runs With

Read the Executed as user: prefix before you read the error after it. A T-SQL step in a job owned by a sysadmin runs as the SQL Server Agent service account, which is the NT SERVICE\SQLSERVERAGENT prefix on the capture in Step 5. A T-SQL step in a job owned by anyone else runs as that owner, which is the Lab1001_owner prefix on the capture in Step 1. Both of those came off the same instance, on consecutive runs, and the only thing that differed was who owned the job.

That is why “it works when I run it in SSMS” proves nothing. In SSMS the batch runs as you, and you are probably a sysadmin. Grant the right to the context named in the message, not to yourself, then re-run the step. The SELECT Permission Was Denied on the Object (Error 229) decodes that family, including the cases where the object is a view and the permission you need is on something else.

There is a sharper version of the same fault. With the owner login present but with no user in the step’s target database, the job did not reach the command at all, and the step row read 'EXECUTE AS LOGIN' failed for the requested login 'Lab1001_owner'. The step failed. Creating the database user, with no permissions granted at all, moved the failure forward to the 229 above. So a step that fails before it runs anything is usually a mapping problem, and a step that fails with a permission error has already got past that.

Step 5: The Path the Agent Account Cannot See

Backup, restore, BULK INSERT and CmdExec steps all fail this way, and the message is generous: it names the account and the exact path it tried.

Operating system error 3 and operating system error 5 are 2 different jobs of work. Error 3 means the path is not there from where the service is standing: a folder that was removed, or a drive letter that only exists in somebody’s interactive session. A mapped drive is the classic one, because a drive letter mapped by a logged-on user does not exist in a service session at all, so a backup path of Z:\SQL\ can work perfectly when you test it and fail every night. Error 5 means the path is there and the account is refused, which is a share permission, an NTFS permission, or both. Cannot Open Backup Device: Operating System Error 5 is the full walkthrough for the access-denied half.

Fix it by granting the account named in the message, which for most jobs is the Agent or engine service account rather than a person, and by writing to a UNC path instead of a drive letter wherever a job touches a remote target.

Step 6: The Message Stops Before the Useful Part

Job history is not a log file, and a step that prints a lot loses the end of it. Measured on SQL Server 2025 (RTM-CU8, 17.0.4075.5): a step that printed 6,000 characters and then failed stored a history message of 2,122 characters in total, of which 1,999 were the step’s own output before it was cut. The column is nvarchar(4000), so the cap that bit was not the column. Turning on log-to-table for the same step wrote 2,221 characters into msdb.dbo.sysjobstepslogs, still nowhere near 6,000.

So if the message ends mid-sentence, assume the cause is in the part you cannot see, and do not spend time re-reading what survived. There are 2 ways forward. Set an output file on the step, @output_file_name on sp_add_jobstep in MS Docs, pointed at a folder the Agent service account can write to, and run it again. Or cut the noise at source: a step that prints progress for every table is the reason the error got pushed off the end, and quieting it is usually the better fix.

The same cap is why a job that fails intermittently is worth instrumenting before the next failure rather than after it, since the 1 run you care about is the one whose message got trimmed.