17

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?

toryan
  • 335
  • 2
  • 3
  • 7

4 Answers4

21

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)

enter image description here

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))

enter image description here

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

Kin Shah
  • 62,545
  • 6
  • 124
  • 245
4

You can get full text:

  1. Go to the job
  2. Right click select properties
  3. Go the job step
  4. Select step and click on edit button
  5. Select advanced. Here you can see the log path.

Now it's simple follow the path.

Paul White
  • 94,921
  • 30
  • 437
  • 687
Parassharma1990
  • 103
  • 1
  • 11
1

In my case this was most easily visible from

  1. expanding Management
  2. Maintenance Plans
  3. finding the maintenance plan we had set up for rebuilding indexes,
  4. right click, view history

The error message listed here, under "Error message:" was not truncated, like I had seen elsewhere.

VB Did Nothing Wrong
  • 111
  • 2
0

The SQL "Log File Viewer" (job history) has a "Selected row details" pane further down, but may need to be expanded or scrolled to see the full message.

While viewing the job history in SSMS,

  1. In the right panel, "Log file summary", expand the date of the job failure.
  2. Select the row of the step in with the error.
  3. Use your cursor to enlarge the bottom pane and/or use the scroll bar at far bottom-right so you can view the full text under the Message heading. (The top of the pane shows Date, Log, Step ID, etc., the Message is way at the bottom)

VB_Did_Nothing_Wrong's answer led to the right place to view the full text of the message.

jasurf
  • 1
  • 1