how to handle SQL Agent job error?

Question

Hello everyone,

I am writing a SQL Agent Job to remove rows from Table 1 and Table 2 once a day. The step of the SQL Agent job is -- delete some specific records from Table1, then delete some specific records from Table 2.

My question is,

1. How to record the error during SQL Agent? Any logs/events we could use? (so that we could see in what days the SQL Agent Job failed, and why it failed.)
2. I want the SQL Job to continue to execute (delete) specific records from Table2, even if for any reason delete from Table1 fails, any best practices how to implement this?

thanks in advance, George

Answer 1

I assume you mean Microsoft SQL Server?

If yes, both things are quite easy to do.

1. Just look at the properties of you SQL Agent job. Under "Notifications", you can tell the job to write into the windows log or send you an e-mail (but before, you have to configure e-mail once per server).
   You can choose if this should happen only when it fails, only when it's successful or always.

2. You can define different steps in a job. For each step, you can define what happens when if fails and what happens when it's successful (like "go to next step" or "end job with error").

EDIT:
First of all, here is a tutorial for your first question - how to set up notification so that you get an e-mail when the job fails.

For the second question, you need to open the properties of the SQL Server Agent job. I found an English screenshot of the properties window here.
Look at the picture - you have to select "Steps" on the left, and then you can see the different steps of the job on the right. Note the columns "On success" and "On Failure" on the right side, that's what I meant. You can edit those values when you edit the step.