Normally scheduling a job is a very elementary operation but for some
hidden reason I've been unable to schedule a job
which runs on a 28 day cycle, even though I have at least 16 other jobs
seperately scheduled to run on a 28 day cycle with
different start dates/times.
My production SQL server is running SQL Server Enterprise Edition
8.00.760(SP3) on a 2 Processor(4 virtual) Microsoft
Windows 2000 Server 5.0.2195
After creating my job through the New Job Wizard in Enterprise Manager,
which at this point is 43 steps, the first step of
which is a Transact-SQL Script, and the remaining 42 all Operating
System Command steps, I try to create the schedule by
clicking on the Schedules tab. I click the New Schedule command
button, type in a schedule name, keep the option button
schedule type default as Recurring, and then click the change command
button. In the Edit Recurring Job Schedule popup
screen I selected the Daily option button in the Occurs section, typed
in 28 in the Daily section so it reads Every 28
days, and in the Daily Frequency section set it to Occurs once at
10:00PM. In the Duration section set the start date to
5/23/2005 with No End Date. Clicked OK several times to complete the
job setup.
When I went to see the Job under the SQL Server Agent - Jobs node, it
shows next run date as todays date, regardless of
what day I set up the job. I learned the hard way that when I set this
job up with a (Future) start date of 3/28/2005 that
it not only ran on 3/28/2005, but it ran every day 10PM since then
until I noticed it and disabled the job. Since then I
have set up and scheduled several other jobs to run on a 28 day cycle
in a similar way, both through the DTS job scheduling
wizard and scheduling tab when creating new jobs, and they all ran on
the desired schedule.
I've been trying to manipulate this job schedule in various ways, even
deleting and recreating a new schedule for this job
through the GUI in Enterprise Manager and still encounter this problem.
Now when I set up this job with the (already past)
start date of 5/23/2005 I would expect it to indicate that the next run
date would be 6/20/2005 10PM, 28 days after
5/23/2005 10PM. The run duration of this job is between 7 and 8 hours
depending on processor load. But still it always
says the next run date is the day that I set up the schedule.
I've created one step dummy jobs in hopes that I can give it the
desired schedule and then use this schedule for the real
job, but I cannot even give this dummy job the desired schedule.
I compared this job schedule with the other 28-day jobs that run as
desired by first looking in the sysjobschedules table.
Aside from the different start date/times the only difference was that
the freq_relative_interval was set to 0 instead of 1
like all the others. Changing it to 1 through a query did not fix the
problem, so I tried deleting the old schedule with
sp_delete_jobschedule and creating a new schedule using
sp_add_jobschedule but got the same results. Next run date was
still today. I tried modifying an existing schedule with
sp_update_jobschedule so that freq_relative_interval in
sysjobschedules was correct, but still the next run date was today
10PM.
I noticed in the sp_update_jobschedule there is some code which is
commented " Notify SQLServerAgent of the change, but
only if we know the job has been cached", which checks if the job is
cached? and then calls sp_sqlagent_notify. I even
called sp_sqlagent_notify seperately from sp_update_jobschedule and the
job still shows today 10PM as the next run date.
The sp_update_jobschedule checks the sysjobservers table for this job,
so I deleted that row from the table and tried
rescheduling the job again through the various methods described above.
The row in sysjobservers was created by default
during this process, but the next run date of my job still shows todays
date (I hit refresh every time I've checked).
The only differences I can tell you between all the 28-day jobs that
work, and this one is that this job will run for 7-8
hours while others take minutes or seconds to run. Are there any other
system tables to check besides sysjobschedules,
sysjobs, or sysjobservers which are failing to be updated that are
causing my job schedule to be cached? Is it the Run
Duration of this job which does not allow it to be on the 28-day cycle?
I've exhausted all options. Any help would be greatly appreciated.
Thanks,
Mike Orlando
CAMP Systems
