|
Home > Archive > Microsoft SQL Server forum > June 2005 > Help Needed With Job Scheduling
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
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_interv
al 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_jobschedul
e 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_jobschedul
e so that freq_relative_interv
al in
sysjobschedules was correct, but still the next run date was today
10PM.
I noticed in the sp_update_jobschedul
e 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_jobschedul
e and the
job still shows today 10PM as the next run date.
The sp_update_jobschedul
e 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
| |
| Erland Sommarskog 2005-05-28, 8:23 pm |
| Mike (mjorlando@campsyste
ms.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, esquel@sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
| |
| Erland Sommarskog 2005-05-29, 9:23 am |
| Steve Kass (skass@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, esquel@sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
| |
|
| 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_interv
al freq_recurrence_fact
or 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 SchedNa
me 1 4 28 1 0 0 0 20
050523
99991231 90000 23595
9 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_interv
al freq_recurrence_fact
or 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 SchedNa
me 1 4 28 1 0 0 0 20
050523
99991231 100000 2359
59 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 SchedNa
me 1 4 28 1 0 0 0 20
050523
99991231 100000 2359
59 20050630 100000 2
005-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_interv
al freq_recurrence_fact
or 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 SchedNa
me 1 4 28 1 0 0 0 20
050603
99991231 110000 2359
59 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_interv
al freq_recurrence_fact
or 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 SchedNa
me 1 4 28 1 0 0 0 20
050603
99991231 230000 2359
59 20050603 230000 2
005-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
| |
| Erland Sommarskog 2005-06-07, 8:23 pm |
| Mike (mjorlando@campsyste
ms.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, esquel@sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
| |
|
| 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_interv
al
freq_recurrence_fact
or 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_interv
al 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_interv
al
freq_recurrence_fact
or 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?
| |
| Erland Sommarskog 2005-06-21, 8:24 pm |
| Mike (mjorlando@campsyste
ms.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, esquel@sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
| |
| Erland Sommarskog 2005-06-22, 3:23 am |
| Erland Sommarskog (esquel@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, esquel@sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
| |
| Global ECS Guru 2005-06-22, 8:24 pm |
| 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
smockler@vinsoft.com
219.942.9544
| |
| Erland Sommarskog 2005-06-22, 8:24 pm |
| Erland Sommarskog (esquel@sommarskog.se) writes:
> Erland Sommarskog (esquel@sommarskog.se) writes:
scheduled[color=dark
red]
>
> 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, esquel@sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
| |
|
| 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.
| |
| Erland Sommarskog 2005-06-23, 8:23 pm |
| Mike (mjorlando@campsyste
ms.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, esquel@sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
| |
|
| Simplicity is genius! Thank you, I'll let you know if there are any
problems.
|
|
|
|
|