How to get SQL server job step error which is more than 1024 characters

The default message that is saved in the SQL Agent job history is 1024 characters.

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.

To allow additional data to be logged we need to enable both the “Log to table” and “Include step output in history” settings for each job step in the job.

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 we need to use this stored procedure sp_help_jobsteplog
or we could query the msdb.dbo.sysjobstepslog table directly.

EXEC dbo.sp_help_jobsteplog @job_name = N’TestJob’ ;
GO

Reference 1

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s