I'm doing debugging of a SQL Server job that keeps failing without obvious cause while also completing every calculation properly. The situation is as follows:
The job runs a procedure surrounded by try/catch (log then throw exception), has a log step before and after, and extensive logging inside that writes logs on practically every relevant step. It calls a number of smaller procedures that each handles some part of the logic, and one external procedure that's not under my control/responsibility to maintain.
The execution of the job completes, writing down every result and the logs behave as expected.
Except the job fails. Every time. And the excessive use of PRINT done by the external procedure keeps hiding the actual cause of the failure because it all goes into the job step's message, which is then completely useless.
My primary question is this: if a procedure uses extensive printing, inside a job step, will that eventually cause the job to fail when logging completion, even if every step inside completed successfully? And if so, is there any documentation that indicates what the limits/settings are for this that I can consult?
Thanks to Thom A for his assistance in the comments, he got me looking in the right place.
The above issue is not the source of the problem, the but the underlying issue was hidden entirely by the printing. Fortunately, if set properly, job steps keep extensive logging in files, which makes checking possible later on, and that's what allowed me to trace the issue to a pair of Msg rows that pointed at the underlying issue.
Clearly, the fact that a remote server error of a procedure does not immediately dump into Catch is an issue I'll have to figure out how to overcome, but at least i can actually find the issue now.