Short job history can lose the useful message you need after a failed step. Saving job step output to a file preserves more detail for review. Configure a server-side path, choose append or overwrite deliberately, and make sure the Agent execution environment can actually write there.
Separate Job History From Full Step Output
SQL Server Agent history is useful for status and concise messages, but long output can be limited. A job-step output file provides another place to retain the detailed text generated by a supported step. It does not change whether the job succeeds or repair the error being reported.
I inspect the current step definition before adding logging. The subsystem, owner, and existing flags matter. A T-SQL example is not a guarantee that every subsystem handles every output option identically. Check the supported behavior for the actual step type.
The file path is on the SQL Server host, not on the administrator's laptop merely because SSMS is open there. Prepare the folder and service permissions through the approved Windows process. A log setting pointing to a nonexistent folder is a remarkably tidy way to preserve nothing. Configuration and successful file creation require separate validation.
Retain job step output with the execution record so a successful status can still be checked against the step's actual messages.
Read the Existing Path and Flags First
The following query lists a selected job's steps, file path, and flags. Replace the placeholder with the actual reviewed job. Keep step_id alongside step_name because the update procedure targets the numeric identifier. Do not choose a step by its display position in a filtered result.
The flags contain more than one possible behavior. Preserve unrelated bits when enabling append. A blanket replacement with one value can silently remove an existing logging option. Read the current definition and decide which changes are required.
I also check the job owner and whether file output is permitted under the supported Agent security rules. Administrative file logging has owner and permission requirements beyond the folder's existence. A job operated under a restricted owner should use the supported logging route available to it rather than acquiring broad rights just to create a diagnostic file. Keep that decision with the job's operating record.
SELECT j.name AS JobName,SUSER_SNAME(j.owner_sid) AS JobOwner,
s.step_id,s.step_name,s.subsystem,s.output_file_name,s.flags
FROM msdb.dbo.sysjobs AS j
JOIN msdb.dbo.sysjobsteps AS s ON s.job_id=j.job_id
WHERE j.name=N'ReviewedJobName'
ORDER BY s.step_id;Configure a Tokenized File Name Carefully
Agent tokens substitute job context when the step runs. The example uses JOBID and STEPID inside the filename so different steps have separate files. The escape macro is part of the supported token syntax and must remain intact in the stored string. Run the block in a normal query window, not SQLCMD mode, because SQLCMD mode reads $( ) as its own variables.
The following block reads the selected step's existing flags and adds the append bit. It stops if the job or step was not found. The output folder and filename remain Windows-friendly, with every backslash preserved. This is an action example for a reviewed job, not an instruction to update all jobs.
What should the filename distinguish: job, step, execution, or day? A stable job-and-step name collects several runs together when append is enabled. Adding appropriate run tokens can create separate execution files, but that increases the number of files requiring retention. Choose the naming and cleanup policy together instead of discovering a crowded folder after the logging change.
DECLARE @JobID uniqueidentifier,@Flags int;
SELECT @JobID=j.job_id,@Flags=s.flags
FROM msdb.dbo.sysjobs AS j JOIN msdb.dbo.sysjobsteps AS s ON s.job_id=j.job_id
WHERE j.name=N'ReviewedJobName' AND s.step_id=1;
IF @JobID IS NULL THROW 51002,'The reviewed job step was not found.',1;
SET @Flags=@Flags|2;
EXEC msdb.dbo.sp_update_jobstep @job_id=@JobID,@step_id=1,
@output_file_name=N'C:\SqlAgentLogs\$(ESCAPE_SQUOTE(JOBID))_$(ESCAPE_SQUOTE(STEPID)).txt',
@flags=@Flags;
Choose Append or Overwrite as a Retention Decision
The append flag adds new output to an existing file. That preserves earlier runs but lets the file grow indefinitely unless another approved process rotates or removes old content. Overwrite keeps only the latest output for the chosen filename and loses earlier evidence.
To clear append while preserving other bits, calculate the existing flags with that bit removed rather than setting every option to zero. Keep the change scoped to the intended step. A logging policy should state how much history belongs in one file and how long files are retained.
Include the information needed to distinguish runs when appending. Step output can contain its own timestamps and meaningful phase messages. Do not assume the concatenated text makes run boundaries obvious. Logging should help a reader explain the failure, not create a long undifferentiated document whose useful message is harder to locate than the original Agent history entry.
Verify the Stored Job Step Output File
Read output_file_name and flags back from sysjobsteps after the update. That confirms the intended definition was stored. Then run the approved test execution and inspect the file through the supported Windows file process. The readback alone cannot prove folder access or output generation.
The Agent service account needs the required write access to the folder, and the job must satisfy the supported owner requirements for file logging. Validate those under the real execution context. An administrator manually creating a file in the folder does not prove Agent can do so.
The next query supplies the configuration readback. Retain it with the test outcome and the resulting file path. If no output appears, check subsystem support, job ownership, folder permissions, and the job's own messages. Keep the missing-file diagnosis concrete rather than repeatedly changing the path without understanding which requirement failed.
SELECT s.step_id,s.output_file_name,s.flags
FROM msdb.dbo.sysjobsteps AS s
JOIN msdb.dbo.sysjobs AS j ON j.job_id=s.job_id
WHERE j.name=N'ReviewedJobName'
ORDER BY s.step_id;Keep the Logs Useful and Bounded
Review the output for sensitive values before retaining it broadly. Full step text can contain details that a short status message did not expose. Limit access and retention according to the job's actual information needs.
Monitor the log folder's capacity and cleanup process. An appended file that grows without limit can eventually become a storage problem of its own. Preserve incident evidence before rotation when the operating policy requires it, and verify that cleanup targets only the approved log files.
Saving job step output is useful when the file actually contains the detail needed to diagnose a run. Inspect the existing definition, preserve flags, configure tokens carefully, and validate a real write. Keep the retention and permission decisions beside that configuration so detailed logging remains an operational aid rather than another unattended dependency.
Related reading on this blog: Agent Jobs Running Longer Than Usual: Finding Them in Job History and Documenting SQL Agent Jobs: Schedules, Steps and Owners in One Query.
A configured output path is not a saved log, it is a destination the job must successfully write.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
