How to create custom schedule for SQL Server Agent Job   2 comments


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)


P.S: You might have to write a different T-SQL according to your requirements provided the scenario remains the same (i.e. The job should not run on specific days)

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:
[1] 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!

2 responses to “How to create custom schedule for SQL Server Agent Job

Subscribe to comments with RSS.

  1. Pingback: blogs | manishkumar1980

  2. Pingback: We didn’t know that… | SQLactions.com

Leave a Comment

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 974 other followers

%d bloggers like this: