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!!

Dom Vito
  • 101
  • 2

2 Answers2

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.

David Browne - Microsoft
  • 46,306
  • 3
  • 46
  • 96
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
John
  • 21
  • 3