Share via

How to trap errors in Agent Job execution

Corey Fleig 160 Reputation points
2025-10-28T00:38:34.7866667+00:00

I have an agent job with several steps, each step executing a stored proc.

A few times each year, when a step EXEC takes place, there's no response, the proc doesn't run at all,

and the step quits upon failure.

I have error checking in the proc, but I don't know how to check run-time execution errors. For example, did the proc not run because of a memory issue, or a locked records scenario? How can I code for that

kind of thing?

Developer technologies | Transact-SQL
Developer technologies | Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
0 comments
Answer accepted by question author
  1. Erland Sommarskog 128.2K Reputation points MVP Volunteer Moderator
    2025-10-29T21:41:51.3233333+00:00

    First of all, make sure that you on each job step have set up logging to a file, one file per job step. You do this on the advanced page of the job step. The information you see in job history is difficult to read and will be truncated if it is too long.

    As for error handling in general in stored procedures, I have an article about this on my web site: Error and Transaction Handling in SQL Server. Actually, it is a multi-part article, but you may let if suffice with Part One, which is short, at first.

0 additional answers

Sort by: Most helpful
Most helpful Newest Oldest

Your answer

Answers can be marked as 'Accepted' by the question author and 'Recommended' by moderators, which helps users know the answer solved the author's problem.