Applies toSQL Server 2016–2025, Azure SQL Managed Instance
1. What failed?
SELECT j.name AS job_name,
DATEADD(second, (h.run_time / 10000) * 3600 + (h.run_time / 100 % 100) * 60 + h.run_time % 100, CONVERT(datetime, CONVERT(char(8), h.run_date))) AS started,
h.step_id, h.step_name, h.retries_attempted, LEFT(h.message, 1000) AS message
FROM msdb.dbo.sysjobhistory h JOIN msdb.dbo.sysjobs j ON j.job_id = h.job_id
WHERE h.run_status = 0 AND h.step_id > 0
AND h.run_date >= CONVERT(int, CONVERT(char(8), DATEADD(day, -1, GETDATE()), 112))
ORDER BY started DESC;
If there are no step rows but the job shows "failed", the job never reached a step. Look at the job-level row (step_id = 0) and the SQL Agent error log. That usually points to an owner or proxy problem, or Agent itself.
EXEC sys.sp_readerrorlog 0, 2; -- current SQL Agent log (2 = Agent)
EXEC master.dbo.xp_readerrorlog 0, 2, N'error';
2. Get the full error
History messages are truncated. Look in this order:
- Step output file, if configured (Step → Advanced → Output file).
- "Log to table" output:
EXEC msdb.dbo.sp_help_jobsteplog @job_name = N'Finance - ETL - Load GL'; - SSIS steps:
SSISDB.catalog.event_messages(see the SSIS scheduling post). - PowerShell / CmdExec steps: the program's own log, and the step's exit code ("Process Exit Code 1").
- SQL Server error log at the same time: deadlocks, IO errors, memory pressure.
If none of these exists, enable an output file now so the next failure is easier.
3. Match the cause
| Error text (excerpt) | Cause | Fix |
|---|---|---|
| "Unable to determine if the owner (DOMAIN\user) of job ... has server access" | Owner disabled, deleted or AD unreachable | Change the owner to sa or a service login (see the ownership post) |
| "Login failed for user..." / "permission was denied" | Step identity lacks rights: the job owner, the Agent account or the proxy | Grant the minimum rights to that identity, not to the person testing |
| "Unable to start execution of step ... Error authenticating proxy" | Proxy password changed, or missing "Log on as a batch job" | ALTER CREDENTIAL; fix the user right |
| "Access is denied" / "path not found" in CmdExec, PowerShell or SSIS | Share or NTFS rights, mapped drives, path doesn't exist on this node | UNC paths; grant the step identity; create folders on all nodes |
| "Transaction (Process ID n) was deadlocked..." (1205) | Deadlock with another workload | Add step retries; schedule apart; tune the queries (see the blocking post) |
| "Lock request time out period exceeded" / long run then failure | Blocking | Find the blocker; reschedule; SET LOCK_TIMEOUT deliberately |
| "The transaction log for database ... is full" (9002) | Log full during a large step | See the 9002 runbook; batch the work |
| "Could not allocate space ... filegroup is full" / "There is not enough space on the disk" | Data file or disk full | Add space; clean up; check backups and output files filling the disk |
| "Query timeout expired" from a linked server or app call | Remote side slow or unavailable | Check the remote server; set sensible timeouts and retries |
| "The step was cancelled (stopped) as the result of a stop job request" | Someone or something stopped it | sysjobhistory and the Agent log show who; check for overlapping maintenance |
| "SQLServerAgent is not currently running" | Agent stopped | Start Agent; set it to Automatic with auto-restart |
| "The job was not run because ... is not the primary" (custom) | AG node is secondary | Expected if you use a primary-check gate |
4. Rerun safely
- Check the step is idempotent, or clean up partial work first (for example, delete today's partly loaded rows).
- Start from the failed step, not the beginning, if earlier steps did their job:
EXEC msdb.dbo.sp_start_job @job_name = N'Finance - ETL - Load GL', @step_name = N'Merge into dw';
- Watch it (
sp_help_jobactivity, Job Activity Monitor) and confirm the outcome in history. - Tell the downstream owners (reports, interfaces) if their data was late.
5. Stop it recurring
- Retries on steps with transient failures (deadlocks, network):
@retry_attempts = 2, @retry_interval = 5. - Failure notifications: email operator and event log on every job.
- Output file on every non-trivial step.
- Owner
saor a service login. Proxies for non-T-SQL steps. - Alert on long-running jobs, not only failed ones (see the history analysis post).
- Add the root cause and fix to the job's description or run book.
-- add retries and an output file to a step
EXEC msdb.dbo.sp_update_jobstep @job_name = N'Finance - ETL - Load GL', @step_id = 2,
@retry_attempts = 2, @retry_interval = 5,
@output_file_name = N'L:\AgentLogs\Finance_ETL_Load_GL.txt', @flags = 2;
A morning check for on-call
Get-DbaAgentJob -SqlInstance (Get-Content servers.txt) |
Where-Object { $_.LastRunOutcome -eq 'Failed' -and $_.IsEnabled } |
Select-Object SqlInstance, Name, LastRunDate, LastRunOutcome | Sort-Object LastRunDate -Descending | Format-Table
Tip
Most "mystery" failures come from identity: the job doesn't run as the person who tested it. Before anything else, ask "who does this step run as?" Then test the command as that identity.