Scheduling SQL Agent Jobs on Alternating Weeks

Question

Post reply

Scheduling SQL Agent Jobs on Alternating Weeks

Ben Thul

SSChasing Mays

Points: 638
More actions
November 17, 2023 at 12:00 am

#4318542

Comments posted to this topic are about the item Scheduling SQL Agent Jobs on Alternating Weeks

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

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

ildjarn.is.dead Ten Centuries Points: 1164 More actions · Answer 1

The worst schema I had to implement:

A customer wanted us to reboot the server for patches on the first friday of the month around 1am.

How do you schedule updates for 'the thursday before the first friday of the month, 22:00'? (Hint: 'The first Thursday of the month' does NOT work)

Jeff Moden SSC Guru Points: 1004151 More actions · Answer 2

ildjarn.is.dead wrote:

The worst schema I had to implement: A customer wanted us to reboot the server for patches on the first friday of the month around 1am.
How do you schedule updates for 'the thursday before the first friday of the month, 22:00'? (Hint: 'The first Thursday of the month' does NOT work)

Schedule the job to run every Thursday. Include this in the job. If @Today is the right Thursday, it will do something. Otherwise, it'll say so it's not the right Thursday and exit.

DECLARE @Today DATETIME = GETDATE()
;
     IF @Today IN
(--==== This finds the Thurday before the first Friday of the current and next months.
 SELECT ThuBefore1stFri = DATEADD(dd,-1,DATEADD(dd,DATEDIFF(dd,4,amo.Seventh)/7*7,4))
   FROM (VALUES 
             (DATEADD(mm,DATEDIFF(mm,6,@Today),6))               --First Friday of Current Month
            ,(DATEADD(mm,1,DATEADD(mm,DATEDIFF(mm,6,@Today),6))) --First Friday of Next Month
        )amo(Seventh)
)
--===== This is what will run if @Today is the Thursday before the first Friday of the month.
  BEGIN
        SELECT 'Replace with what the job should do';
    END
   ELSE 
--===== Otherwise, we'll just exit the job.
  BEGIN
        SELECT 'Not the Thursday before the first Friday of the month. Exiting.';
        RETURN
    END
;

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ScottPletcher SSC Guru Points: 101039 More actions · Answer 3

Jeff Moden wrote:

ildjarn.is.dead wrote:
The worst schema I had to implement: A customer wanted us to reboot the server for patches on the first friday of the month around 1am.
How do you schedule updates for 'the thursday before the first friday of the month, 22:00'? (Hint: 'The first Thursday of the month' does NOT work)
Schedule the job to run every Thursday. Include this in the job. If @Today is the right Thursday, it will do something. Otherwise, it'll say so it's not the right Thursday and exit.
DECLARE @Today DATETIME = GETDATE()
;
     IF @Today IN
(--==== This finds the Thurday before the first Friday of the current and next months.
 SELECT ThuBefore1stFri = DATEADD(dd,-1,DATEADD(dd,DATEDIFF(dd,4,amo.Seventh)/7*7,4))
   FROM (VALUES 
             (DATEADD(mm,DATEDIFF(mm,6,@Today),6))               --First Friday of Current Month
            ,(DATEADD(mm,1,DATEADD(mm,DATEDIFF(mm,6,@Today),6))) --First Friday of Next Month
        )amo(Seventh)
)
--===== This is what will run if @Today is the Thursday before the first Friday of the month.
  BEGIN
        SELECT 'Replace with what the job should do';
    END
   ELSE 
--===== Otherwise, we'll just exit the job.
  BEGIN
        SELECT 'Not the Thursday before the first Friday of the month. Exiting.';
        RETURN
    END
;

I don't think all that is really necessary. There's only an issue if the first day of the month is a Friday. Therefore, schedule the job every Thursday, but only actually execute if either: (1) the current day is between 1 and 6 OR (2) the next day is the 1st, like so:

DECLARE @today date = GETDATE();

/* Job runs every Thu, but only actually takes actions before the 1st Fri of a month: */
/* if the day is between 1 and 6, it is the first Thu (before a Fri) of the month; */
/* OR if the next day is Fri the 1st, also need to run, on last day of month */
IF DAY(@today) BETWEEN 1 AND 6
OR DAY(DATEADD(DAY, 1, @today)) = 1
BEGIN
    /* do processing */
END /*IF*/
ELSE
BEGIN
    /* exit, don't need to do anything */
END /*ELSE*/

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Jeff Moden SSC Guru Points: 1004151 More actions · Answer 4

Hadn't thought about that. Thanks, Scott.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004151 More actions · Answer 5

Actually, it could be simplified even more. Since it always tries to run on Thursdays, we don't need to figure that out. We just need to figure out if the day of the month for "tomorrow" is between 1 to 7 inclusive.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ScottPletcher SSC Guru Points: 101039 More actions · Answer 6

Code only needs to be schedule on the first and last Thursday of the month, since those are the only 2 days it might need to do something.

As to simplification, technically, yes, but I think the above is very slightly clearer, although comments could clarify the simpler code.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".