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?

Improve this question
0

3 Answers 3

Reset to default
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

Improve this answer
1
  • 1
    @toryan I had the same problem -- logging was still truncated, even with output to a table (method (b) above) and reading sysjobstepslogs. This was the recommendation of most online discussions of this problem. But method (a), output to a file, did NOT have this problem: the text files are showing me all job step logging, finally without truncation! (In my case, my job steps are DTEXEC commands, running SSIS packages.) Only drawback: only the last run is seen, unless appending to the file. Rather than appending, I choose to live with truncation on earlier runs.
    – Doug_Ivison
    Mar 15, 2016 at 18:52
3

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 its simple follow the path.

Improve this answer
0

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.

Improve this answer

Your Answer

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

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