471,108 Members | 1,312 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,108 software developers and data experts.

Help Needed With Job Scheduling

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

Jul 23 '05 #1
13 5887
Mike (mj*******@campsystems.com) writes:
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.


An MVP colleague says that this is to be expected, when your date is
in the past, although I'm not really sure that I agree. When I test
to set up a job with a 28-day schedule, and with start date of
2005-05-23, I get a next run date of 2005-06-26, that is 28 days from
today. And the job does not run today.

It might be that it is not possible to use the start date, to specify
when the job should run the first time.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #2
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

Jul 23 '05 #3
Steve Kass (sk***@drew.edu) writes:
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.


Yet, it's simply a matter of interface. I have this job that is to
run every 28th day, and on the 23rd I ran it manually. Now I want SQL
Agent to run it for me in the future. Since I am lazy, I want SQL Agent
to do the job for me. After all, isn't that why we have computers?
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #4
Thanks for your responses Erland and Steve. I used some of your ideas
and did a few more tests of scheduling my dummy job by changing

the existing schedule (328).

Scenario 1:

Date: 6/2/2005
Time: 9:24AM

Changed schedule 328 to run every 28 days at 9:00AM starting 5/23/2005.
The job shows next run date as 6/30/2005 at 9:00AM. Thats 28

days from today, 6/2/2005, the day I edited the schedule.

The following is the row in sysjobschedules:

schedule_id job_id name
enabled freq_type
freq_interval freq_subday_type freq_subday_interval

freq_relative_interval freq_recurrence_factor active_start_date
active_end_date active_start_time active_end_time next_run_date

next_run_time date_created

328 C2FA68BE-E6A8-43CA-A427-B9ADDEF2D8FD SchedName 1 4 28 1 0 0 0 20050523

99991231 90000 235959 0 0 2005-05-27 10:43:54.763
The next_run_date and next_run_time are 0.
Scenario 2:

Date: 6/2/2005
Time: 9:45AM

Changed schedule 328 by making start time 10:00AM, and keeping start
date 5/23/2005. Now the schedule says it will run next today

6/2/2005 at 10AM.

The following is the row in sysjobschedules:

schedule_id job_id name
enabled freq_type
freq_interval freq_subday_type freq_subday_interval

freq_relative_interval freq_recurrence_factor active_start_date
active_end_date active_start_time active_end_time next_run_date

next_run_time date_created

328 C2FA68BE-E6A8-43CA-A427-B9ADDEF2D8FD SchedName 1 4 28 1 0 0 0 20050523

99991231 100000 235959 0 0 2005-05-27 10:43:54.763

The next_run_date and next_run_time are still 0. I let the dummy job
run to see what the next run date would be after it runs and it

shows me it will be: 6/30/2005 10:00AM.

After this job ran the row in sysjobschedules is as follows:
328 C2FA68BE-E6A8-43CA-A427-B9ADDEF2D8FD SchedName 1 4 28 1 0 0 0 20050523

99991231 100000 235959 20050630 100000 2005-05-27 10:43:54.763

The next_run_date and next_run_time are 20050630 and 100000
respectively.

Scenario 3:

Date: 6/2/2005
Time: 10:45AM

Changed schedule 328 by making start time 11:00AM, and start date
6/3/2005. Now the schedule says it will run next 6/3/2005 at 11:00AM.
The following is the row in sysjobschedules:

schedule_id job_id name
enabled freq_type
freq_interval freq_subday_type freq_subday_interval

freq_relative_interval freq_recurrence_factor active_start_date
active_end_date active_start_time active_end_time next_run_date

next_run_time date_created

328 C2FA68BE-E6A8-43CA-A427-B9ADDEF2D8FD SchedName 1 4 28 1 0 0 0 20050603

99991231 110000 235959 0 0 2005-05-27 10:43:54.763

The next_run_date and next_run_time are 0. The job ran as desired on
6/3/2005 11:00AM. The job has a next run date of 7/1/2005 11:00AM,

exactly what was desired.

Scenario 4:

Date: 6/3/2005
Time: 11:59AM

Changed schedule 328 by making start time 11:00PM, and start date
6/3/2005. Now the schedule says it will run next 6/3/2005 at 11:00PM.

The following is the row in sysjobschedules:

schedule_id job_id name
enabled freq_type
freq_interval freq_subday_type freq_subday_interval

freq_relative_interval freq_recurrence_factor active_start_date
active_end_date active_start_time active_end_time next_run_date

next_run_time date_created

328 C2FA68BE-E6A8-43CA-A427-B9ADDEF2D8FD SchedName 1 4 28 1 0 0 0 20050603

99991231 230000 235959 20050603 230000 2005-05-27 10:43:54.763

The job ran as desired. The next run date is 7/1/2005 11:00PM, exactly
what is desired.

So I will wait until June 20, 2005 after noon and schedule my real job
to run every 28 days starting on June 20, 2005 at 10:00PM.

Judging from these recent tests I expect that my 28 day schedule will
execute as desired. I will let you know if I still have a problem.

Thanks,

Mike

Jul 23 '05 #5
Mike (mj*******@campsystems.com) writes:
Judging from these recent tests I expect that my 28 day schedule will
execute as desired. I will let you know if I still have a problem.


Great to hear that you believe have things under control. And big
thanks for reporting back!
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #6
Scenario 5 (STILL HAVING THE SAME PROBLEM):

Date: June 20, 2005
Time: 12:30PM

Deleted schedule 328 from my job and created a new 28-day schedule
starting tonight 6/20/2005 at 10:00PM.

The following is the row in sysjobschedules:

schedule_id job_id name

enabled freq_type freq_interval
freq_subday_type freq_subday_interval freq_relative_interval
freq_recurrence_factor active_start_date active_end_date
active_start_time active_end_time next_run_date next_run_time
date_created
----------- ------------------------------------
--------------------------------------------------------------------------------------------------------------------------------
----------- ----------- ------------- ----------------
-------------------- ---------------------- ----------------------
----------------- --------------- ----------------- ---------------
------------- -------------
------------------------------------------------------
329 D16909F8-6C6D-4567-BEE4-535440999A4E TwentyEight

1 4 28
1 0 0 0
20050620 99991231 220000 235959
0 0 2005-06-20 13:41:07.653

The freq_relative_interval was set to 0 as opposed to the other 28 day
jobs which are set to 1. The next_run_date and next_run_time were
also set to 0 respectively.

The job ran as desired however the next run date was NOT 28 days from
June 20, 2005 10:00PM. The next run date is June 21, 2005 at 10PM.
Back to the same problem, but this time I have a before and after row
in sysjobschedules. After the job ran the row in sysjobschedules looks
like this:

schedule_id job_id name

enabled freq_type freq_interval
freq_subday_type freq_subday_interval freq_relative_interval
freq_recurrence_factor active_start_date active_end_date
active_start_time active_end_time next_run_date next_run_time
date_created
----------- ------------------------------------
--------------------------------------------------------------------------------------------------------------------------------
----------- ----------- ------------- ----------------
-------------------- ---------------------- ----------------------
----------------- --------------- ----------------- ---------------
------------- -------------
------------------------------------------------------
329 D16909F8-6C6D-4567-BEE4-535440999A4E TwentyEight

1 4 28
1 0 0 0
20050620 99991231 220000 235959
20050621 220000 2005-06-20 13:41:07.653
I thought I had this problem figured out by creating the schedule after
noon on the day it was supposed to run. Appearently this job still
wants to run every day. This is very frustrating since I waited almost
a month to schedule this job at a specific time so that the schedule
works on a 28-day interval. What the @##%^#@ is wrong with SQL Server?
Are there any patches that can be run to fix this?

Jul 23 '05 #7
Mike (mj*******@campsystems.com) writes:
Scenario 5 (STILL HAVING THE SAME PROBLEM):

Date: June 20, 2005
Time: 12:30PM

Deleted schedule 328 from my job and created a new 28-day schedule
starting tonight 6/20/2005 at 10:00PM.
...
The job ran as desired however the next run date was NOT 28 days from
June 20, 2005 10:00PM. The next run date is June 21, 2005 at 10PM.
Back to the same problem, but this time I have a before and after row
in sysjobschedules. After the job ran the row in sysjobschedules looks
like this:
...
I thought I had this problem figured out by creating the schedule after
noon on the day it was supposed to run. Appearently this job still
wants to run every day. This is very frustrating since I waited almost
a month to schedule this job at a specific time so that the schedule
works on a 28-day interval. What the @##%^#@ is wrong with SQL Server?
Are there any patches that can be run to fix this?


Well, the first step to find a patch is that the problem can be recreated.
This far I have not been successful. Some 45 minutes ago I created a job
to run every 28th day at 23:15 starting today. The job ran, and is now
scheduled to run 2005-07-19 next time.

But I recalled that your job was a long-running job, so I've now scheduled
a job that will start at 23:45 and run for six hours. We'll see tomorrow
when this will be scheduled to run the next time.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #8
Erland Sommarskog (es****@sommarskog.se) writes:
But I recalled that your job was a long-running job, so I've now scheduled
a job that will start at 23:45 and run for six hours. We'll see tomorrow
when this will be scheduled to run the next time.


That will be tonight at 23:45. Which is incorrect. So now at least I have
something I can bring up with Microsoft.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #9
I represent a company called Vinzant, Inc. We develop and market a
product called Global ECS which is a job scheduling and batch
processing solution for Windows, Linux, most flavors of Unix, AS/400
and MPE/ix. We can schedule your tasks very easily to run in your SQL
Server environment. Check us out at http://www.globalecs.com . Feel
free to call us at 800.355.3443 and we can set up a live demo to show
your our solution. You can also download a free evaluation version of
Global ECS so that you may try it in your environment.

Thanks!

Scott
sm******@vinsoft.com
219.942.9544

Jul 23 '05 #10
Erland Sommarskog (es****@sommarskog.se) writes:
Erland Sommarskog (es****@sommarskog.se) writes:
But I recalled that your job was a long-running job, so I've now scheduled a job that will start at 23:45 and run for six hours. We'll see tomorrow
when this will be scheduled to run the next time.


That will be tonight at 23:45. Which is incorrect. So now at least I have
something I can bring up with Microsoft.


I've now tested the case on SQL 2005. The problem exists there as well.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #11
Erland, I think you are on the right track. I think it has to do with
the job ending on a different day than the start date. All my other
28-day jobs ran as desired this month and they ended on the same day as
the start date.

Jul 23 '05 #12
Mike (mj*******@campsystems.com) writes:
Erland, I think you are on the right track. I think it has to do with
the job ending on a different day than the start date. All my other
28-day jobs ran as desired this month and they ended on the same day as
the start date.


So that brings us to a possible workaround. Create your job, but don't
schedule it. Instead schedule another job, which has the sole duty to
start the real job.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #13
Simplicity is genius! Thank you, I'll let you know if there are any
problems.

Jul 23 '05 #14

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

6 posts views Thread by Jack Smith | last post: by
1 post views Thread by worzel | last post: by
2 posts views Thread by Willie | last post: by
1 post views Thread by Brian Gallagher | last post: by
reply views Thread by Christopher | last post: by
5 posts views Thread by oasis | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.