Monday, February 20, 2012

Preventing schedule job to run

Hi,
I have 2 jobs schedule to run after every alternate hour. Job A runs at 1 am, 3 am, 5 am etc. and job B runs at 2 am, 4 am , 6 am.
If job A is still running I would like Job B not to start at the scheduled time. How can I achieve this?
Thanks in Advance ... jVery simply... first step in Job A could be to update a flag on the DB, last step would be to remove it. First step in Job B would be to check the flag, if present it bombs if it's not there then continue.|||If you mean to have Job B not run at all, and let Job A potentially run twice in a row, then you could do it this way:

Create a table in say the pubs database called Status (col1 varchar(10))

The first step in Job A will be to put a row into this table. The last step in Job A will be to delete the table (think of it as an on/off switch)

The first step in Job B would be a check to see if a row exists, and if it does, then select 1/0 (or any other error of your choice that will trigger the OnJobStepFailure to trigger.

Set the failure action of step 1 of Job B to be exit the job, then you are done.

Not too sure what you would have to do to get Job B to run at say 2:30 or so, if you can not have Job A run twice in a row.

Hope this helps.|||Can I disable Job B or its schedule untill job A finishes? And once it finishes can I enable it?|||Yeah, you can do that, just do the following...

USE msdb
UPDATE sysjobs
SET enabled = 1
WHERE jobid = <jobid>

to enable the job and then set it back to 0 to disable it.

Another option would be to add job B as a step of job A. And just let it always run as soon as job A finishes. Not sure if that is an option though.

Hope this helps.|||Can I disable Job B or its schedule untill job A finishes? And once it finishes can I enable it?|||Sure... use the method Kuthula posted above, only instead of a flag in a table, have job A disable job B as the first step, and then enable it as the last step. using the update to sysjobs in msdb.

No comments:

Post a Comment