SQL Agent Job Output Options for Troubleshooting

Problem

When troubleshooting SQL Agent jobs, often the Job history output is truncated or poorly formatted, making it hard to read. This is especially true when calling SSIS Packages, running jobs like DBCC CheckDB or when running T-SQL code that returns a lot of output. Are there options to get more readable Job output to aid in troubleshooting?

Solution

This tip will demonstrate three options to configure your SQL Agent Job to capture the complete job output in a clean formatted, readable way to aid in troubleshooting. We will do this through a seldom used built-in feature from the SQL Server Management Studio (SSMS) SQL Server Agent – Advanced setting in the Job Step Properties.

This article assumes you know how to create SQL Jobs and will not go into those details. Additionally, this article will use a query against Microsoft free sample database, AdventureWorks. Links to articles on Job creation are provided at the end of the tip.

Three Methods for Capturing SQL Job Output

The image below shows the SQL Job Step Advance settings options for capturing Job output.

The options include:

1. Output file,
2. Log to Table, and
3. Include step output in history.

There are also check boxes to “Append the output” to the existing file or existing entry in a table. I recommend avoiding setting the append options and will cover that more later in this article. Also, I typically only set one of the options, though you can use all three at once.

This screen shows the Advanced option from the Steps with the optional settings:

For this article, I created a test job with three steps demonstrating each of the three options. You can create the test job manually by following the screen prints in the three Job steps explained below or run the Job creation script below to create the job. Run the Job manually to evaluate the results.

Here is my test job steps for Job “SQL Output Job Options”:

The Job Creation Script for my test job, “SQL Output Job Options”:

--MSSQLTips.com (T-SQL)
-- Job Create Script for: [SQL Output Job Options]
USE [msdb]
GO
 
/****** Object:  Job [SQL Output Job Options]    Script Date: 1/19/2025 1:29:17 PM ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object:  JobCategory [[Uncategorized (Local)]]    Script Date: 1/19/2025 1:29:18 PM ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
 
END
 
DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'SQL Output Job Options', 
      @enabled=1, 
      @notify_level_eventlog=0, 
      @notify_level_email=0, 
      @notify_level_netsend=0, 
      @notify_level_page=0, 
      @delete_level=0, 
      @description=N'No description available.', 
      @category_name=N'[Uncategorized (Local)]', 
      @owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [Output to File]    Script Date: 1/19/2025 1:29:18 PM ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Output to File', 
      @step_id=1, 
      @cmdexec_success_code=0, 
      @on_success_action=3, 
      @on_success_step_id=0, 
      @on_fail_action=2, 
      @on_fail_step_id=0, 
      @retry_attempts=0, 
      @retry_interval=0, 
      @os_run_priority=0, @subsystem=N'TSQL', 
      @command=N'select * from [AdventureWorks].[dbo].[MySalesPerson]', 
      @database_name=N'AdventureWorks', 
      @output_file_name=N'c:\temp\Job_Output.txt', 
      @flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [Log to Table]    Script Date: 1/19/2025 1:29:18 PM ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Log to Table', 
      @step_id=2, 
      @cmdexec_success_code=0, 
      @on_success_action=3, 
      @on_success_step_id=0, 
      @on_fail_action=2, 
      @on_fail_step_id=0, 
      @retry_attempts=0, 
      @retry_interval=0, 
      @os_run_priority=0, @subsystem=N'TSQL', 
      @command=N'select * 
from [AdventureWorks].[dbo].[MySalesPerson] a
cross join [AdventureWorks].[dbo].[MySalesPerson] b
cross join [AdventureWorks].[dbo].[MySalesPerson] c', 
      @database_name=N'AdventureWorks', 
      @flags=8
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [Include Step Output in History]    Script Date: 1/19/2025 1:29:18 PM ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Include Step Output in History', 
      @step_id=3, 
      @cmdexec_success_code=0, 
      @on_success_action=1, 
      @on_success_step_id=0, 
      @on_fail_action=2, 
      @on_fail_step_id=0, 
      @retry_attempts=0, 
      @retry_interval=0, 
      @os_run_priority=0, @subsystem=N'TSQL', 
      @command=N'select * 
from [AdventureWorks].[dbo].[MySalesPerson] a
cross join [AdventureWorks].[dbo].[MySalesPerson] b
cross join [AdventureWorks].[dbo].[MySalesPerson] c', 
      @database_name=N'AdventureWorks', 
      @flags=4
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
GO

Here is the example query I used in each of the job steps to produce a lot of output:

--MSSQLTips.com (T-SQL) 
-- Sample Query that returns a lot of Output using Microsoft sample database: AdventureWorks
select * 
from [AdventureWorks].[dbo].[MySalesPerson] a
cross join [AdventureWorks].[dbo].[MySalesPerson] b
cross join [AdventureWorks].[dbo].[MySalesPerson] c;

SQL Job Step Output Guidelines

• Only select one of the options.
• Avoid selecting the “Append” check box.
• Use for Jobs that have a lot of output like:
  • DBCC jobs.
  • SSIS Packages
  • Job that are hard to troubleshoot.

When to Use Append

I avoid using append. I’m afraid I’ll forget it and over time the MSDB database will grow out of control when saving to a table or if saving to a file and the drive may eventually run low on space. Typically, I only look at the Job output when troubleshooting a failure, so overwriting the previous Job execution output works fine. I typically set one of the Job output options when I create jobs that I know will have a lot of output or when I am troubleshooting a job that does not have the option set. Use append with caution!

Job Step Options and Description

Option 1: Output to File

For Job Step 1 “Output to File” in Advanced Options, set a file name and path for the SQL Agent to create an output file. After running the job, go back to the Job step Advanced tab and click View. The output will open in a Notepad text editor window for review. In Notepad, you will see all the job step output in a readable, clean format. Later, you can contrast this output to that from Job Step 3 “Include Step Output in History”. Refer to the two screen prints below.

Option 2: Log to Table

For Job Step 2 “Log to Table” in the Advanced options, check the box beside “Log to table” for the SQL Agent to direct the Job step output to an internal table in the MSDB databases. After running the job, go back to the Job step Advanced tab and click View. The output will open in a Notepad text editor window for review. In Notepad, you will see all the job step output in a readable, clean format. The “Log to table” output is identical to the format from Step 1 “Output file.” Again, you can contrast this output to that from Job Step 3 “Include Step Output in History.” See the two screen prints below.

Option 3: Include Step Output in History

For Job Step 3 “Include Step Output in History” in the Advanced options, check the box beside “Include step output in history”, as shown in the first screen print below. The SQL Agent will direct the Job step output to be appended to the Job history in the MSDB databases. After running the job, go to Job History. In contrast to the first two job steps, the output is appended to the Job History; however, the output is truncated as seen in the second screen print below.

Wrap Up

To summarize, this tip shows three ways to capture SQL Job output to help troubleshoot errors. The two options that capture the output to a file or table are great options as they capture all the output in a clean, readable format. Either of these options without setting the Append are “set it and leave it” options. I do not recommend setting the Append check box. But if you do, be sure to remember to un-check it when done troubleshooting. Of these, my preferred option is the second, “Log to table”. I set this option often to troubleshoot SSIS packages, DBCC jobs or other SQL Jobs that have a lot of output.

Next Steps

Review other MSSQLTips articles and learn more tips for managing SQL Jobs.

• Getting Started with SQL Agent
• Query SQL Agent Job Info
• Failed SQL Server Agent Jobs
• SQL Server Agent Job Management
• Review other articles author Jim Evans has written
• Microsoft Learn – Create a SQL Server Agent Job with SSMS