You can create different schedules and attach them to SQL Server agent job to run as per the requirements. However, sometimes the requirements might be such, that you can’t create the proper schedule form the in-built scheduling window. There comes creating custom schedule for you SQL Agent jobs. I am going to demonstrate one such scenario here:
Scenario: There is a job (say Update Stats) which should run once every day. However, the job must NOT run on either 1st , 15th or ‘last day of the month’
I could create a daily schedule for such job but the challenge was to let the job NOT run on the restricted days (1st, 15th and last day of the month) as stated above. To deal with this, I added an extra job step which would check for the restricted days, and, if the condition satisfies, I set it not to execute the other steps in the job. Here is the step-by-step instruction on how to achieve this.
1. Go to Properties > Steps of the job, Click Insert to add an extra job step
2. On the New Job Step window, fill-in the Step name, paste the following script (which checks for the restricted days)
IF ( (SELECT CASE WHEN (DATEPART (DD,GETDATE()) IN (1,15,DATEPART(DD,DATEADD(S,-1,DATEADD(MM, DATEDIFF(M,0,GETDATE())+1,0))))) THEN 0 ELSE 1 END) <> 1 ) RAISERROR ('Today is either 1st, 15th or last day of the month. This job must not execute today.', 16, 127)
3. Go to the Advanced page and set the properties of On failure action to Quit the job reporting success
The reason I changed the properties of On failure action to Quit the job reporting success is to ensure that the job overall doesn’t report a failure since the check failed. Step 1 failure is legit and so the job won’t run at all which is what we want. Had I set On failure action to Quit the job reporting failure it would report job status as Failed and unnecessary alert me for investigation.
4. Click OK. This will return you back to the Job Properties window. Click OK again. Here, if you see the following warning, click No and resolve this first as mentioned below.
Text of the Error:
TITLE: Microsoft SQL Server Management Studio ------------------------------ WARNING: The following job steps cannot be reached with the current job step flow logic:  Check Day Is this the intended behavior? ------------------------------ BUTTONS: &Yes &No ------------------------------
The above warning is raised because we added an extra job step but the Start Step of the Job is a different step than this newly added step. To resolve this, at the bottom of the Job Properties window, change Start Step to the newly added step (i.e. Step1:Check Day in my case)
5. Now, finally click OK and you are done.
Hope you liked this tidbit on SQL Agent scheduling. Stay tuned for the next blog!