|
|
Posted by Steve Kass on 05/29/05 05:17
Mike,
The short answer to your question is this: to create an
every-28-day schedule, specify when you want the job to run
first as the start date and start time in the wizard. This
must be in the future, since it is not possible to decide now
to run a job in the past.
Do not specify a start date and start time in the past, despite
your belief that this caused the problem you had back in
March. Nothing you report here is unexpected, except for
the problem back in March, for which you give few details. If
that problem occurs again, post another thread here, and include
the sysjobschedules row for that schedule (which I assume is now
unavailable for the March job that ran every day).
To elaborate on what Erland reported, based on a few more experiments,
it looks like a new job's next_run_date is determined as follows (at
least for daily recurring jobs). I may not have checked enough cases,
but it seems plausible, if very strange. Assume a 28-day recurrence
for this description.
1. If the specified start date is "tomorrow" or later, the
initial next_run_date is the specified start date. The
job again runs 28 days later, 56 days later, etc.
2. If the specified start_date is "today", the initial
next_run_date depends on the start_time.
2A. If the start_date is today, and the start_time has passed,
the initial next_run_date is 28 days from today, at the scheduled
start_time.
2B. If the start_date is today, and the start_time has not passed,
the initial next_run_date is today, at the scheduled start_time.
So far it's reasonable, but it gets weirder.
3. If the start_date is "yesterday" or earlier, the initial
next_run_date *still* depends on the start_time.
3A. If the start_date is yesterday or earlier, and the start_time
has passed on today's clock, the initial next_run_date is 28 days
from today, at the scheduled start_time.
3B. If the start_date is yesterday or earlier, and the start_time
has not passed on today's clock, the initial next_run_date is today,
at the scheduled start_time.
As far as I can tell, this is all consistent with what you have
found, except for one case in the past, where you think that choosing
a start date in the future caused a job to run daily when it should not
have. I don't know what happened then, but I believe that setting a
start date in the future is the correct way to schedule a job.
I think that an error should be raised if a job is added where the
first scheduled job run is in the past, because Agent cannot fulfill
such a request.
Given the behavior I observed, my guess is that when these routines
were programmed, no one paid much attention to the "correct" way to
start a job in the past, perhaps because it never occurred to the
programmers that someone would try to use SQL Server to change
history.
Steve Kass
Drew University
Mike wrote:
> 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
>
Navigation:
[Reply to this message]
|