How to store longer SQL Agent Job Step output messages

By:   |   Comments (7)   |   Related: > SQL Server Agent


When working with SQL Agent jobs sometimes it is difficult to determine why a job failed or even exactly what occurred.  For each job step SQL Server provides a message, but it is not always that easy to determine exactly what was occurring during that step.  The default message that is saved in the job history is 1024 characters.  In most cases this may be enough, but if you have a long running process you may need to store more than 1024 characters. Is there any way to get additional information within the job history to help troubleshoot issues as well as just knowing what occurred?


In SQL Server 2005 and later you have the ability to log additional SQL Agent job output beyond the 1024 characters that is stored in the msdb.dbo.sysjobhistory table.  By default this enhanced logging is not turned on it is something that you need to turn on for each job step.

Let's take for example we are running DBCC CHECKDB commands for several databases.  This command provides a lot of output data unless you use the WITH NO_INFOMSGS option.  If you are not using the NO_INFOMSGS option the command output fills up the 1024 characters quite quickly and you can only see the part of the output in the job history message.

Here is a screen shot of the job history for the step that did a DBCC CHECKDB.  As you can see we only get a portion of the command output.


To allow additional data to be logged you need to turn on some settings for each job step in your job.  To do this edit the job step and select the Advanced tab.

On this screen you need to enable both the "Log to table" and "Include step output in history".

job step properties

After we make these changes and run this again if we look at the job history you will see the same short message.


In order to see the additional logged information you need to use this stored procedure sp_help_jobsteplog or you could query the msdb.dbo.sysjobstepslog table directly.

If we run this command in a query window (test2 is the job name):

EXEC dbo.sp_help_jobsteplog @job_name = N'test2';

we will get this additional output: (note: the output was edited to remove most of the middle to keep this web page smaller)

Job 'test2' : Step 1, 'DBCC' : Began Executing 2007-12-13 21:06:14 DBCC results for 'Test1'. [SQLSTATE 01000] Service Broker Msg 9675, State 1: Message Types analyzed: 14. [SQLSTATE 01000] Service Broker Msg 9676, State 1: Service Contracts analyzed: 6. [SQLSTATE 01000] Service Broker Msg 9667, State 1: Services analyzed: 3. [SQLSTATE 01000] Service Broker Msg 9668, State 1: Service Queues analyzed: 3. [SQLSTATE 01000] Service Broker Msg 9669, State 1: Conversation Endpoints analyzed: 0. [SQLSTATE 01000] Service Broker Msg 9674, State 1: Conversation Groups analyzed: 0. [SQLSTATE 01000] Service Broker Msg 9670, State 1: Remote Service
sys.queue_messages_2041058307'. [SQLSTATE 01000] There are 0 rows in 0 pages for object "sys.queue_messages_2041058307". [SQLSTATE 01000] DBCC results for 'Table1'. [SQLSTATE 01000] There are 62 rows in 1 pages for object "Table1". [SQLSTATE 01000] DBCC results for 'sysarticles'. [SQLSTATE 01000] There are 2 rows in 1 pages for object "sysarticles". [SQLSTATE 01000] DBCC results for 'sysarticlecolumns'. [SQLSTATE 01000] There are 4 rows in 1 pages for object "sysarticlecolumns". [SQLSTATE 01000] CHECKDB found 0 allocation errors and 0 consistency errors in database 'Test1'. [SQLSTATE 01000] DBCC execution completed. If DBCC printed error messages, contact your system administrator. [SQLSTATE 01000]

As you can see we can now get the entire output message since the output in the sysjobstepslog is stored as a nvarchar(max) instead of an nvarchar(1024) like in sysjobhistory.

Next Steps
  • Helpfully this tip is useful for you to get additional job step history, just a couple of clicks can provide a lot more detail
  • Keep in mind that only one row is stored per job step.  If you use the "Append output to existing entry in table" this will just append the data to the existing record.
  • Take a look at these other useful SQL Agent tips

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Greg Robidoux Greg Robidoux is the President and founder of Edgewood Solutions, a technology services company delivering services and solutions for Microsoft SQL Server. He is also one of the co-founders of Greg has been working with SQL Server since 1999, has authored numerous database-related articles, and delivered several presentations related to SQL Server. Before SQL Server, he worked on many data platforms such as DB2, Oracle, Sybase, and Informix.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

Tuesday, January 29, 2019 - 4:52:55 AM - Thomas Franz Back To Top (78901)

I added a script to which sets output files to every job / step.

@Della: Old question, but maybe the answer will help someone else

To create a job schedule that runs on 22nd day of every month and if job fail on 22nd day or some other reason like holiday or sunday this scheduled job run on next day i.e. 23rd day of that month, I would execute it on day 22 and 23 (add two different monthly schedules to the job) but add a check to the step(s), that they will be executed on day 23 only, if it was not okay at day 22.

IF DATEPART(day, GetDate()) = 22
  FROM msdb.dbo.sysjobhistory AS s
 INNER JOIN msdb.dbo.sysjobs AS j
    ON j.job_id = s.job_id
   AND = 'My_Special_Job'
WHERE s.run_status = 0 -- ok
  AND s.run_date = CONVERT(INT, CONVERT(CHAR(8), DATEADD(DAY, -1, GETDATE()), 112)) -- yesterday
 -- do something

Instead of querying the sysjobhistory you could of course check own (log)tables too, if the job writes something to your database.

Tuesday, October 31, 2017 - 1:48:33 PM - Della Back To Top (69017)

 To keep the job step log size under control, I created a job that runs weekly, renames the log file by appending date time, then purges log files that were last modified 10 days ago or more.


Monday, October 30, 2017 - 2:12:57 PM - Greg Robidoux Back To Top (68977)

Hi Della, I am not if there is a simple way to do this.


Monday, October 23, 2017 - 2:10:18 PM - Della Macklin Back To Top (68698)

 Excellent information.  Just what I needed.

Question--I chose Log to Table, and Append.  Is there a simple way to manage the size of the output record in the table?  Let's say I want to keep a rolling window of 7 days.  If not, I can conceive of a way using files instead of tables.

 Thanks for your help.

Friday, January 23, 2015 - 1:13:08 AM - Pradeep Shinde Back To Top (36034)

How to create a job schedule where I want to execute job on 22nd day of every month, if job fail on 22nd day or some other reason like holiday or sunday this scheduled job run on next day i.e. 23rd day of that month.

What are the steps written please explain in brief with t-sql queries.


Friday, January 18, 2008 - 2:28:21 PM - admin Back To Top (227)


Please check out this tip as a follow-up to this forum post:

Verbose SQL Agent Logging -

Thank you,
The Team

Friday, December 14, 2007 - 11:34:33 AM - timmer26 Back To Top (163)

Another alternative in both 2000 and 2005 is to create an output file for the script.  If you have multiple steps for the job you can also have them all post to the same output file (I locate mine in the LOG folder created in the SQL install).  You do this by using the same file name for each step, and selecting the Append output to existing file checkbox on the Job Step Properties / Advanced GUI form for each step after the first step.  (Therefore the first step overwrites the file, the subsequent steps append.)

get free sql tips
agree to terms