Post reply

Job Scheduling - interesting requirement

  • January 6, 2003 at 9:52 am

    #58245

    I have two jobs that I wish to schedule. One (#1) is a weekly job. The other (#2) is a monthly job that I would like to schedule the 1st Sunday every month (or the 2nd Saturday, etc). Here's the interesting part. The jobs are mutually exclusive. On the weeks that #2 runs, #1 should not run. I've come up with a couple of possible ways to do this, but I wanted to see if anyone had a more elegant solution. Here's what I have...

    1) Schedule #2 for 1st Sunday every month.

    Schedule #1 to run 2nd Sunday.

    Schedule #1 to run 3rd Sunday.

    etc.

    2) Schedule #2 to run just a few minutes before #1 is to start. Add a step in #2's schedule that will run sp_update_job to disable #1. Every Monday, run another job which checks to see if #1 is disabled. If so, re-enable it.

    3) Finally include both jobs in a single job. Develop an algorithm to determine whether it is the 1st Sunday (2nd Saturday, etc). If it is, then run #2, otherwise run #1.

    These are the solutions that I've come up with. Any suggestions? In the case of 3), has anyone already developed an algorithm to do this?

    Any help or advice would be appreciated!

    Steve Phelps

    SQL Server DBA

    American Fidelity Group

  • January 6, 2003 at 10:00 am

    #445631

    vote for #1

    Steve Jones

    sjones@sqlservercentral.com

    http://www.sqlservercentral.com/columnists/sjones

    http://www.dkranch.net

  • January 6, 2003 at 10:29 am

    #445634

    I agree with Steve, #1 is the better solution. Why make it harder than it has to be? Also, if something changes down the road, with #1 it's easier to make adjustments.

    -SQLBill

  • January 7, 2003 at 1:19 pm

    #445763

    Agree as well.

    Case #1 is very clear as to what is running and when, no need to read documentation or figure out anything. Remeber KISS!

    John Zacharkan


    John Zacharkan

  • January 7, 2003 at 2:57 pm

    #445779

    Sorry here but, Steve did we have a wee bit too much of the nip for lunch today. As long as runs every Sunday then you will not have to worry about overlap of 1 and 2 the way you describe in option 1.

  • January 10, 2003 at 10:44 am

    #446080

    I'd like to thank everyone for their input on this. It seems "solution #1" wins hands down. The only problem that I have with "solution #1" is scheduling multiple jobs (as many as 4) for a single procedure. It just seems redundant. But, if thats the best solution, then thats the best solution!

    Thanks, again for your input!

    Steve Phelps

    SQL Server DBA

    American Fidelity Group

  • January 10, 2003 at 12:25 pm

    #446095

    Antares,

    He didn't make a mistake. However, it would have been clearer if he had explained what the jobs were....let's say he's doing backups. Once a month (the 1st Sunday) he wants to do a full backup. Then every other Sunday (2nd, 3rd, and 4th Sundays) he wants to do a differential backup.

    Then option one is the best...you don't want/need to do a differential backup when you just did a full backup, so you need the multiple jobs.

    -SQLBill

  • January 13, 2003 at 2:45 am

    #446138

    Not So difficult i think.......

    every time you execute the job store the month in a table. For example Months.....

    then you could do the following ....

    if datepart(dw,getdate()) = 1 (is a sunday)

    if month > storedmonth...

    Yep it is the first Sunday this month.....

    do the special stuff

    else

    do the normal stuff

    store the current month in a table

    should work i think.....

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply