SQL Server Agent Job - Schedule daily except 1 day per month

Question

We need to run a daily job using SQL Server Agent (SQL 2000) but we don't want it to run on the 22nd of each month as there are other processes which only run on that day which will clash.

The recurring settings in Agent do not provide any exclusion options, is there any way to achieve this?

mrdenny · Accepted Answer

Put a job step as the first step in the job to look to see if the DATEPART(dd, getdate()) = 22 and if it does issue an sp_stopjob to stop the job.

JGibson · Answer

I wrapped an if statement around my whole procedure
if RIGHT( convert(varchar(11),CURRENT_TIMESTAMP,120),2) = 22 do something else end

Shoeless · Answer

Have a 2nd job run on the 22nd that disables the first job:

exec msdb..sp_update_job @job_id = 0xC0923E444828064EA22B46B2C47A1363, @enabled = 0

And then on the 23rd re-enable the job:

exec msdb..sp_update_job @job_id = 0xC0923E444828064EA22B46B2C47A1363, @enabled = 1

Chris_K · Answer

Well, one option is similar to your existing idea. Create another job that runs a script to check the date. If it is the 22nd that job then disables your daily job (via SQL). If it isn't the 22nd it enables your daily job.

The only advantage this might have over your suggestion is that it keeps your daily job "pure" of any date logic.

The only advantage this might have over your suggestion is that it keeps your daily job "pure"  of any date logic.

Shevek · Answer

One way to do this is to include a check for date in the script to be run and not run it on the 22nd.

Is this the only way though?

SQL Server Agent Job - Schedule daily except 1 day per month

5 Answers

Add your own answers!

Ask a Question