0

I have three SQL Agent jobs, one for each of three registries being managed. These go off on a staggered schedule every two hours (i.e. Reg 1 at 9am, 3pm; Reg 2 at 11am, 5pm; Reg 3 at 1pm, 7pm).

In most every circumstance, it takes less than two hours for one of these jobs to run. However, if it is taking longer than expected, I do NOT want the other job to run. So, if Reg 1's 9am run is taking more than 2 hours, I do not want Reg 2's 11am job to run UNTIL Reg 1's job is completed.

How can I work this conditional run functionality in? I am not seeing anything in the schedules properties of the job. Let me know if you need more clarification!

Thanks!!

1

Simplest thing is to have all the steps in a single job. It's built-in that a two instances of a job can't run at the same time.

| improve this answer | |
  • How do you set a specific schedule for each task ? (If they need to start at 9am, 11 am and 1pm exactly) – Dominique Boucher Jul 14 at 12:50
0

I had to do something similar. The way I handled it was to write a while loop in step 1 of the agent job that would check on the condition and the actual execution in step 2. In your case, something like this: https://dba.stackexchange.com/a/64091/154196, I think would check the status of your job. In my step 1, I wrote a TSQL WHILE loop, that checked a status and used a WAITFOR DELAY in the loop to try again 5 minutes later. I also wrote a counter that would kill the loop after 3 hours so it didn't execute step 2 if it waited that long.

edit: I forgot, in my while loop, if the loop counter expired, I used a TSQL THROW to make the step fail, so that step 2 didn't execute. I had separate steps so I could easily see how long it had to wait in the loop before failing or executing step 2.

Something like this:

IF ( ( Query referenced above modified to give a count of running) > 0 )
  BEGIN
    PRINT 'Waiting';
    DECLARE @MyCount int = 0
    WHILE (@MyCount < 10 AND ( Query referenced above modified to give a count of running) > 0 )
          BEGIN
            SET @MyCount = @MyCount+1
            WAITFOR DELAY '00:10' --Wait 10 minutes
         END
    IF (@MyCount >= 10 
       THROW 55555, 'Agent Job Still Running. Quitting',1
 END
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.