0

I'm working on SQL Server Agent and i have some troubles. I need to re-run a job when a step fail, I mean, the entire job - not just the step. Is it possible ?

Do you know the best way to do this ?

Thanks a lot.

Sylvain Pauly
  • 11
  • 4
  • If you have a proper steps flow you can run the step #1 if a given step fails. But that might get you in a cycle, so I don't think that's the best way to solve your situation. – YYamil Feb 25 '15 at 15:32
  • Yeah, there is to be an end... Let's say i want to retry 3 times but no more. I need this because of async job who runs Azure job via powershell... It drive me crazy. – Sylvain Pauly Feb 25 '15 at 15:34
  • Are you able to create a table in the database to store details of each step that is run? If so, then you can implement a logging process where you log each step that is run, and do something like ensure that you only run Step 1 3 times in one day (thus avoiding getting stuck in a cycle). Let me know if you want further details. – The Dumb Radish Feb 25 '15 at 15:57
  • The "right" way to do this would be to move it to an SSIS package, or move the operations into a single step. SSIS gives you much better control over flow than SQL Agent does, but is more complex. The only other alternative I can think of is to basically re-create what SQL Agent does with the flow control you want. You can [jump through a few hoops](http://stackoverflow.com/questions/12249056/executing-sql-server-agent-job-from-a-stored-procedure-and-returning-job-result) to call jobs from other jobs, and control things that way. – Bacon Bits Feb 25 '15 at 16:03
  • @DumbRadish : That's the solution that I got in mind but I wanted to know if there was an easiest solution. Anyway, thanks :) – Sylvain Pauly Feb 25 '15 at 16:03
  • @bacon Bits: I dont know SSIS but it sounds interesting, and I cant do it in a single step because of asynchronous nature of the job. – Sylvain Pauly Feb 25 '15 at 16:10
  • SSIS is what they used to call DTS in SQL Server 2000. It's Microsoft's enterprise transform and load service meant for integration. It's very extensive; you actually create the packages using a modified version of Visual Studio. If everything you're doing is predominantly in SQL, this is probably what you want to use. If you're using SQL Agent as a scheduling tool for mostly non-SQL or non-DB tasks, SSIS is probably going the wrong way. SSIS is available on 2005+, but not Workgroup or Express. Basic tutorial [here](https://msdn.microsoft.com/en-us/library/jj720568.aspx). – Bacon Bits Feb 25 '15 at 17:24
  • Indeed it's mostly for non-sql task. Thanks anyway :) – Sylvain Pauly Feb 26 '15 at 08:36

1 Answers1

1

Under the job step properties click on "advanced", you can then restart the job on step 1. It also gives you the option to retry a number of times before aborting and ending.

Chris The DBA
  • 105
  • 1
  • 9