Get detailed output of SQL Agent job

We have an SQL Server Agent job that runs a maintenance plan to reindex all of the database on the server. Recently this has failed but the job history is not providing enough information to diagnose the problem.

In the job history it states The job failed. The job was invoked by user foo\bar. The last step to run was step 1 (Rebuild Index).

In the details window are multiple messages in the following form:

Executing query "ALTER INDEX [something] ON [a...".: 0% complete  End Progress  Progress: 2015-03-15 22:51:23.67     Source: Rebuild Index Task

The SQL statement that is running is truncated, and I would assume that the output of the statement is also truncated, preventing me being able to identify which particular statement has failed and why. Is there any way to extract the full text of these messages?

Is there any way to extract the full text of these messages?

You can achieve it in 2 ways - Go in job step and select the Advanced tab:

a. Output to a file (<== My preferred method)

b. "Log to table" and "Include step output in history" (<== You need to trim msdb..sysjobhistory in long-run as the messages are stored as nvarchar(max) instead of nvarchar(1024))

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.sysjobstepslogs table directly.

More info here

StackExchange Database Administrators Q#95333, answer score: 21