14

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?

  • 1
    I think @kin does a good job of answering your initial question, but in my opinion this is more of a debugging issue. If you know the index it is failing on, then i would try to identify why that specific index is failing. Can you see the sql code used? Is it in a stored proc? You could do several things to narrow it down. Create a job with only that index. Run sql trace while that job is running. Capture the sql used then try to reproduce the problem. – Sir Swears-a-lot Mar 16 '15 at 8:15
18

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

  • Cheers, I'll give this a go. just to let you know, the correct name of that table is msdb.dbo.sysjobstepslogs ('logs' rather than 'log'). I tried to edit the answer but single-character edits are not allowed. – toryan Mar 16 '15 at 2:32
  • 1
    @toryan np. I have edited my answer. If you feel that I have answered your question then please up vote / mark as answer. – Kin Shah Mar 16 '15 at 2:54
  • 1
    I re-ran the job and it only generated one error this time, which it recorded in the table as described. Unfortunately the text of the query was still truncated - is there any way to get more information? – toryan Mar 16 '15 at 5:33
  • I doubt it. Can u try with the output file method 1 as described in my answer. – Kin Shah Mar 16 '15 at 12:03
  • 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 '16 at 18:52
2

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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