0

we have a sql server agent job which is running two sql servant jobs.The job is designed in the way that the second job will wait till the first job completed. But sometimes the wait is not working and it causes both jobs ends up running at same time. This causes failure of both jobs. We uses below code for waiting mechanism.

WHILE  
    (

        SELECT COUNT(*) AS cnt 
        FROM        msdb.dbo.sysjobs J
        INNER JOIN  msdb.dbo.sysjobactivity JA
            ON      J.job_id = JA.job_id
        WHERE       start_execution_date IS NOT NULL
            AND     stop_execution_date IS NULL
            AND     J.name = "first job name"
            AND     last_executed_step_date > DATEADD(DD,-1,GETDATE())--//Added to bypass historical corrupt data
    ) > 0
        BEGIN
            --WAIT
            WAITFOR DELAY '00:01';
END

any suggestion why this is not working sometimes?

bmsqldev
  • 2,627
  • 10
  • 31
  • 65
  • Why do not you add the 2nd job as `Step2`? – LONG Mar 16 '17 at 17:03
  • actually we did..there are 3 steps...first step for 1st job...2nd step is wait query...3rd step is 2nd job – bmsqldev Mar 16 '17 at 17:05
  • what is the purpose of adding 0:01 sec before the 2nd job start? – LONG Mar 16 '17 at 17:07
  • i hope...it will add the wait until first job completes – bmsqldev Mar 16 '17 at 17:08
  • There is no point to add a time gap between two, if you added the 2nd job as step2, it will execute only when 1st one completes (assume go to the next step when the previous succeeded). it is kind of over engineered to do this.. – LONG Mar 16 '17 at 17:11

0 Answers0