Seeing the whole error: SQL Agent step output files, log to table and history options

#The problem

Job History shows Executed as user: ... The step failed. and a message cut off after about 4,000 characters. For a stored procedure that prints progress or an SSIS package with many errors, the real cause is lost.

#The options (step → Advanced page)

#Set an output file in T-SQL

EXEC msdb.dbo.sp_update_jobstep
     @job_name = N'ETL - Load Sales', @step_id = 1,
     @output_file_name = N'D:\SQLAgentLogs\ETL_LoadSales_$(ESCAPE_SQUOTE(STEPID))_$(ESCAPE_SQUOTE(STRTDT))_$(ESCAPE_SQUOTE(STRTTM)).txt',
     @flags = 0;   -- 0 = overwrite, 2 = append to output file

The tokens make a new file per run, so nothing is overwritten. Other useful tokens: $(ESCAPE_SQUOTE(JOBID)), $(ESCAPE_SQUOTE(SRVR)).

The Agent service account (or the step's proxy) needs write access to the folder. Clean old files up with a PowerShell step:

Get-ChildItem 'D:\SQLAgentLogs' -Filter *.txt |
  Where-Object LastWriteTime -lt (Get-Date).AddDays(-30) | Remove-Item

#Log to table

EXEC msdb.dbo.sp_update_jobstep @job_name = N'ETL - Load Sales', @step_id = 1,
     @flags = 8;    -- 8 = write log to table (overwrite); 16 = append to table

Read it:

SELECT j.name, s.step_id, l.date_modified, l.log
FROM msdb.dbo.sysjobstepslogs l
JOIN msdb.dbo.sysjobsteps s ON s.step_uid = l.step_uid
JOIN msdb.dbo.sysjobs j ON j.job_id = s.job_id
WHERE j.name = N'ETL - Load Sales';

Or in SSMS: job step properties → Advanced → View next to Log to table.

Warning

"Append to table" grows without limit. Use overwrite, or purge with sp_delete_jobsteplog @job_name = ..., @older_than = ....

#Flags you can combine

Example: @flags = 2 + 4 = append to file and include output in history.

#Make the output worth reading

• In procedures, RAISERROR('Loaded %d rows', 0, 1, @rows) WITH NOWAIT; gives timestamps you can follow in the file.
• In PowerShell steps, write Write-Output lines with timestamps and throw on errors, so the step fails properly.
• In CmdExec steps, redirect stderr: cmd /c "script.bat 2>&1".

#Prevent it next time

• Standard: every step longer than a few seconds writes to an output file or table.
• One log folder per instance, with retention.
• Mention the log location in the job description.