In short: It seems I only get PRINT messages to my SQL Server job history. What is the definition of valid history output?

Background: Just executing a statement like UPDATE product SET ... will leave no trace in the job history (no matter whether "Include step output in history" is checked or not). Running the same statement in Query Analyzer shows "(X row(s) affected)".

By creating a variable and assigning it the @@ROWCOUNT contents after the update I can build and PRINT a message that will include the number of affected rows. This will be included in the job history. This works fine, but I'd like to know the definition of output (that is written to history) from a Transact-SQL Script job Step. What is included except PRINTed strings?

One might easily think that the history would show exactly the same as is shown in the Messages pane of the Query window (formerly known as the Query Analyzer) of SQL Server Management Studio – but that is not the case.


2 Answers 2


For T-SQL Job Steps, the "output" refers to "messages" -- notices sent via PRINT and RAISERROR. Result sets are also included as "output", but only if there are no PRINT / RAISERROR messages, else it is only the PRINT / RAISERROR messages that are included.

Try this test:

Job Step 1


PRINT ' ** Line 1 ** ';

SELECT ' ** Line 2 ** ' AS [Line Two];

RAISERROR(' ** Line 3 ** ', 10, 1);

Output in job history:

Executed as user: NT SERVICE\SQLSERVERAGENT. ** Line 1 ** [SQLSTATE 01000] (Message 0) ** Line 3 ** [SQLSTATE 01000] (Message 50000). The step succeeded.

Job Step 2


SELECT ' ** Line B1 ** ' AS [Line B-One];

--PRINT ' ** Line B2 ** '; -- uncomment and output will show this and not "Line B1"

Output in job history:

Executed as user: NT SERVICE\SQLSERVERAGENT. Line B-One
** Line B1 **
(1 rows(s) affected). The step succeeded.

Job Step 3


PRINT ' ** Start ** ';

RAISERROR(' ** Test Exception ** ', 16, 1);

PRINT ' ** End ** ';

Output in job history:

Executed as user: NT SERVICE\SQLSERVERAGENT. ** Start ** [SQLSTATE 01000] (Message 0) ** Test Exception ** [SQLSTATE 42000] (Error 50000) ** End ** [SQLSTATE 01000] (Message 0). The step failed.

Regarding the following statement in the Question:

One might easily think that the history would show exactly the same as is shown in the Messages pane of the Query window [in SSMS] [for example: "(X row(s) affected)"]

One certainly might think that, but what is shown in the Messages tab of SSMS is not necessarily direct output from SQL Server. The "X rows(s) affected" is being generated by SSMS based on info it received from SQL Server that was not direct output but additional info that comes back in the Tabular Data Stream (TDS). The same applies to batch iteration using "GO x" where the x is an integer telling SSMS how many times to submit that particular batch (the one ending with the "GO" batch separator). The output in the "Messages" tab would show Beginning execution loop and at the end Batch execution completed 4 times., but those are messages from SSMS and not from SQL Server.

  • I've been looking but haven't found an explicit mention - Sql Server Agent jobs appear to do at least an LTRIM() on whatever output you emit with PRINT or RAISERROR. My format string had '%24s: message blah' to account for variable length prefix, but the padding was getting nibbled off. To keep it, I had to use '.%24s: message blah' Commented Jun 15, 2021 at 21:12
  • @user1664043 Interesting. Good to know. I don't have time to test this now, but if that is a true T-SQL LTRIM(), then it's only space that is being trimmed, hence for cleaner output you can use either a tab ( NCHAR(0x09) ) or non-breaking space ( NCHAR(0xA0) ) instead of the period as the first character. If the trim is being done in the app code then it will likely trim those as well, but I could probably find some non-printable character that would work in that case. Thanks for sharing, and if you are table to test those, please let me know the results. Thanks! 😺 Commented Jun 15, 2021 at 22:59
  • So I ran a quick test. Both tab and nbsp prevent the leading space nibbling on the agent job output. RAISERROR won't let you do expressions in the parameters, so you have to declare a variable for the format string and then specify that as the parameter, so you can do CHAR(9)+ or CHAR(160)+ the rest of your format string. Commented Jun 16, 2021 at 17:28
  • @user1664043 Thanks for testing and reporting back. I tried doing an expression in the message itself for RAISERROR but that also didn't work. So yes, you can either create a separate variable for the NCHAR(9) or NCHAR(160), or you can prepend them to the variable used as the parameter for the %24s right before the call to RAISERROR. However, while you can use CHAR(9) and it will produce the same tab character, doing CHAR(160) (i.e. not NCHAR) might not produce NBSP, depending on the collation of the current DB. Just FYI. Commented Jun 16, 2021 at 19:31

If you direct output for the step into a "table", like this:

enter image description here

You can see all output from the last run of the job, including PRINT, RAISERROR, and results from queries, by looking in the msdb database at the dbo.sysjobstepslogs table:

SELECT JobName = sj.name
    , StepName = sjs.step_name
    , DateModified = sjsl.date_modified
    , LogText = sjsl.log
FROM dbo.sysjobs sj
    INNER JOIN dbo.sysjobsteps sjs ON sj.job_id = sjs.job_id
    INNER JOIN dbo.sysjobstepslogs sjsl ON sjs.step_uid = sjsl.step_uid;

One gotcha, you'll need to copy-and-paste the contents of the LogText column into notepad (or editor of your choice) to see multiple lines of output.

With a job defined as:

USE [msdb]

SELECT @ReturnCode = 0
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
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


EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'TestOutput', 
        @description=N'No description available.', 
        @category_name=N'[Uncategorized (Local)]', 
        @owner_login_name=N'[login_name]', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [Step1]    Script Date: 4/14/2016 2:41:19 PM ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Step1', 
        @os_run_priority=0, @subsystem=N'TSQL', 
        @command=N'SELECT d.name
FROM sys.databases d
WHERE d.database_id > 4
ORDER BY d.name;

PRINT ''test'';', 
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
GOTO EndSave


Output from the query looks like:

enter image description here

and the copy-and-paste text looks like:

Job 'TestOutput' : Step 1, 'Step1' : Began Executing 2016-04-14 14:38:44


(1 rows(s) affected)

test [SQLSTATE 01000]

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.