Friday, April 9, 2010

SQL job retry (with additional logic) on fail

Here's some code I put together to get a SQL Server 2005 job to retry on fail. I spent a while looking for how to just have the job scheduler retry on fail, and found nothing. Of course, right after I coded and tested this, I found out a way to easily retry individual steps on fail, which is probably the way you want to do it 95%+ of the time :-), but just in case someone has the need to retry a job programatically and insert additional logic, or have more control over the process, or just wants to see how it could otherwise be done (ok, maybe at this point I'm just trying to justify wasting 20 minutes of my life coding this manually...), here's the code:

USE msdb

IF(
SELECT COUNT(*)
FROM sysjobhistory
WHERE
-- status 0 = error
run_status = 0
-- isolate the step we're looking for
AND step_name = '(job outcome)'
-- job unique ID (find this out by going to the job properties, and selecting "Script")
AND job_id = '48F4EE80-62C7-4DFF-8F6F-D009F616DA07'
-- today's date in the same style as the job history table format
AND run_date = CONVERT(VARCHAR(8), GETDATE(), 112)
) > 0
BEGIN
-- insert additional logic here

EXEC sp_start_job @job_name='[JOB YOU WANT TO RETRY]'
END


And here's the easy way:
Right-click on the job, choose "Properties". Click on "Steps", and select the step you want to retry. Click on "Advanced", and set the "Retry attempts" and "Retry interval". Repeat for additional steps. Done. D'oh!

No comments: