SQL Server 2K - Agent Jobs - Errors and Messages

Question

I have a job that runs every night. I do my own logging in my stored procedure so that I can tell what was going on if something fails.

Last night the job failed and I know where. When I ran the job manually it succeeded. So I went to the job history to see what I could find.

I selected the "Show Step Details" and can see the messages. So far so good but here is my problem.

The box that displays the messages/errors is filled with a lot of information but the last part where it says "The step failed" has bee truncated by the rest of the messages which have no information on the error. Those other messages are normal. I use BCP quite a few times and expect them.

My question is: is there any way to clear this within a stored procedure? What I woul do would be to clear this just before the part of the stpred procedure that fails so that I have a clear box

OR

Is there any way to see even more detail? I queried the sysjobhistory but it is basically the same info. Truncated / cut off and not much use.

OR

Is there any way to turn off the messages that get logged during a job so that only errors are there.

Thanks !! Thanks!

Answer 1

In the "Advanced" options for a SQL Agent job step, you can choose a file to log step output to. This log file will contain all the details of a step, not truncated. It doesn't help with this failure that already happened, but it will help with any future failures.