作业调度需要帮助 [英] Help Needed With Job Scheduling

问题描述

通常安排一份工作是一项非常基本的操作，但是对于一些人而言，b $ b隐藏的原因我一直无法安排工作


一个28天的周期，即使我有至少16个其他工作

单独计划在28天周期内运行


不同的开始日期/时间。


我的生产SQL服务器在一台2处理器（4个虚拟）上运行SQL Server企业版

8.00.760（SP3）微软
< br $>
Windows 2000 Server 5.0.2195

通过企业管理器中的新建作业向导创建作业后，

此时此刻是43步，


的第一步是Transact-SQL脚本，剩下的42个全部操作

系统命令步骤，我尝试创建单击时间表选项卡上的时间表


。我单击New Schedule命令

按钮，输入计划名称，保留选项按钮

计划类型默认为Recurring，然后单击更改命令

按钮。在编辑重复作业计划弹出窗口


屏幕中，我在发现部分选择了每日选项按钮，在每日部分的28中键入

，因此它显示为Every 28


天，并在每日频率部分设置为发生一次在

10:00 PM。在持续时间部分中，将开始日期设置为


5/23/2005，无结束日期。单击确定几次以完成

作业设置。


当我在SQL Server代理 - 作业节点下查看作业时，它

显示下一个运行日期为今天的日期，无论


我在哪一天设置工作。我学到了很难的方法，当我将这个

工作设置为（未来）开始日期为3/28/2005时


它不仅运行了在2005年3月28日，但它从那时起每天晚上10点运行

直到我注意到并禁用了这项工作。从那时起，我已经设置并安排了其他几个工作，以28天的周期运行

以类似的方式，通过DTS作业调度<创建新工作时，
向导和日程安排选项卡，他们都按照预定的时间表运行

。


我我们一直试图以各种方式操纵这份工作时间表，甚至

删除并重新创建这项工作的新时间表


通过企业管理器中的GUI并且仍然遇到这个问题。

现在当我用（已经过去的）


开始日期5/23/2005开始这个工作时我会期待它表示下一次运行

日期将是6/20/2005 10PM，28天后

5/23/2005 10PM。此作业的运行持续时间为7到8小时

，具体取决于处理器负载。但它仍然总是


说下一个运行日期是我设定时间表的那一天。


我创造了一个步骤虚拟工作，希望我可以给它

所需的时间表，然后使用这个时间表来实现真正的工作，但是我甚至不能给这个假工作期望的时间表。


我将这个工作时间表与其他28天的工作进行了比较，这些工作是首先查看sysjobschedules表中所需的

。


除了不同的开始日期/时间，唯一的区别是

freq_relative_interval设置为0而不是1


和所有其他人一样。通过查询将其更改为1并未修复

问题，因此我尝试使用


sp_delete_jobschedule删除旧计划并使用
sp_add_jobschedule但得到了相同的结果。今天的下一个运行日期是


。我尝试使用

sp_update_jobschedule修改现有的时间表，以便


sysjobschedules中的freq_relative_interval是正确的，但是下一个运行日期仍然是今天

10PM。


我注意到在sp_update_jobschedule中有一些代码是

评论"通知SQLServerAgent更改，但只有当我们知道作业已被缓存时才会显示
，这会检查作业是否已缓存？b $ b缓存？然后调用sp_sqlagent_notify。我甚至单独从sp_update_jobschedule调用了
sp_sqlagent_notify，并且

作业仍然在今天晚上10点显示为下一个运行日期。


sp_update_jobschedule检查sysjobservers表中是否有这个作业，

所以我从表中删除了那行并尝试通过各种方法再次重新安排
重新安排作业如上所述。

sysjobservers中的行是默认创建的


在这个过程中，但我工作的下一个运行日期仍然显示今天

date（我每次检查都点击刷新）。


我可以告诉你所有28天工作之间的唯一区别

工作，这个是这个工作将运行7-8

小时，而其他人需要几分钟或几秒钟才能运行。是否还有其他

系统表，除了sysjobschedules，


sysjobs或sysjobservers之外，还没有更新

导致我的工作计划被缓存？是运行


这份工作的持续时间不允许它在28天的周期内吗？


我已经用尽所有选项。任何帮助将不胜感激。


谢谢，


迈克奥兰多

CAMP系统

解决方案

Mike（mj*******@campsystems.com）写道：

我去过尝试以各种方式操作此作业计划，甚至通过GUI企业管理器中的GUI删除并重新创建此作业的新计划，但仍然遇到此问题。现在当我用（已经过去）5/23/2005的开始日期设置这份工作时，我希望它表明下一个运行日期是6/20/2005 10PM，28在5/23/2005 10PM之后的日子。此作业的运行持续时间在7到8小时之间，具体取决于处理器负载。但它仍然总是说下一次运行
日期是我设置时间表的那一天。

一位MVP同事说这是预期的，当你的约会对象过去是
，虽然我不确定我是否同意。当我测试

以28天的时间安排工作，并且开始日期为

2005-05-23时，我得到了2005年的下一个运行日期-06-26，今天是

的28天。而今天的工作还没有开始。


可能是因为当作业运行时可能无法使用开始日期来指定

第一次。

-

Erland Sommarskog，SQL Server MVP， es *** *@sommarskog.se


SQL Server SP3的联机书籍
http://www.microsoft.com/sql/techinf...2000/books.asp

迈克，


你的问题的简短答案是：每28天创建一个

的时间表，指定您希望作业何时运行

首先作为向导中的开始日期和开始时间。这个

必须在将来，因为过去无法决定是否需要工作。


不要指定过去的开始日期和开始时间，尽管

您认为这导致了您在3月份返回的问题
。你在这里报告的任何内容都是意料之外的，除了

3月份的问题，你提供的细节很少。如果

再次出现问题，请在此处发布另一个帖子，并在该日程表中包含

sysjobschedules行（我假设现在是

不可用）对于每天运行的3月份工作而言。

根据更多的实验，详细说明Erland报告的内容，

它看起来像是一份新工作'next_run_date确定如下（每日经常性工作最少
）。我可能没有检查过足够的情况，

但是看起来似乎有道理，如果非常奇怪的话。对于此描述，假设28天复发

。


1.如果指定的开始日期是明天或者稍后，

初始next_run_date是指定的开始日期。

工作再次运行28天后，56天后等等。


2.如果指定的start_date是今天，则初始

next_run_date取决于start_time。


2A。如果start_date是今天，并且start_time已经过去，那么最初的next_run_date是从今天开始的28天，在预定的

start_time。

2B。如果start_date是今天，并且start_time还没有通过，那么初始next_run_date就是今天，在预定的start_time。


到目前为止它是'合理，但它更奇怪。


3.如果start_date是昨天或者更早，最初的

next_run_date *仍然*取决于start_time。


3A。如果start_date是昨天或更早，并且start_time

已经过了今天的时钟，那么从预定的start_time开始，最初的next_run_date是28天

。

3B。如果start_date是昨天或更早，并且start_time

还没有在今天的时钟上传递，那么最初的next_run_date是今天，

在预定的start_time。

据我所知，这与你发现的
一致，除了过去的一个案例，你认为选择

a的开始日期导致一份工作每天都要运行，而不应该是b $ b。我当时不知道发生了什么，但我相信将来设定一个

开始日期是安排工作的正确方法。


我认为如果在过去的第一次预定作业运行的地方添加了一份工作，就应该提出错误，因为代理无法履行这样的请求。


鉴于我观察到的行为，我的猜测是，当这些例程被编程时，没有人会过多关注正确的行为。在过去开始工作的方式，或许是因为从来没有发生过某人会试图使用SQL Server来改变

历史。

Steve Kass

Drew大学

Mike写道：

通常安排工作这是一个非常基本的操作，但是由于一些隐藏的原因，我已经无法安排工作

这个工作在28天的周期内运行，即使我至少有16个其他工作<单独安排在28天的周期内运行

不同的开始日期/时间。

我的生产SQL服务器正在运行SQL Server企业版
8.00 .760（SP3）在2处理器（4个虚拟）Microsoft

Windows 2000 Server 5.0.2195

通过企业管理器中的新作业向导创建作业后，
在这一点上是43步，这是一个Transact-SQL脚本的第一步，其余42个全部在运行按钮，输入计划名称，保留选项按钮

计划类型默认为Recurring，然后单击更改命令
按钮。在编辑重复作业计划弹出窗口中我选择了发现部分中的每日选项按钮，在每日部分的28中键入
，因此它显示为每28页
天，并在每日频率部分将其设置为在晚上10:00发生一次。在持续时间部分中，将开始日期设置为无结束日期，即5/23/2005。单击确定几次以完成
作业设置。

当我在SQL Server代理 - 作业节点下查看作业时，它将下一个运行日期显示为今天的日期，无论我什么时候开始工作。当我将这个
工作设置为（未来）2005年3月28日的开始日期时，我学到了很难的方法

它不仅在2005年3月28日运行，而且它从那时起每天晚上10点运行
直到我注意到并禁用了这项工作。从那时起，我已经设置并安排了几个其他工作，以28天的周期运行
以类似的方式，通过DTS作业安排向导

向导和创建新工作时的计划选项卡，他们都按照所需的时间表运行。

我一直试图以各种方式操纵这份工作时间表，甚至删除
并通过企业管理器中的GUI重新创建此作业的新计划，并且仍然遇到此问题。
现在，当我设置此作业时（已经过去）

2005年5月23日的开始日期我希望它表明下一次运行的日期是2005年6月20日晚10点，28天后

5/23 / 2005年10月10日。此作业的运行持续时间为7到8小时
，具体取决于处理器负载。但它仍然总是说下一个运行日期是我设定时间表的那一天。

我创造了一步虚拟工作，希望我能给予它是所需的时间表，然后使用这个时间表来完成真正的工作，但是我甚至无法给这个虚拟工作提供所需的时间表。

我比较了这份工作通过首先查看sysjobschedules表，安排其他28天的工作作为
期望。

除了不同的开始日期/时间，唯一的区别是
freq_relative_interval设置为0而不是1

和所有其他人一样。通过查询将其更改为1并没有解决问题，因此我尝试使用
sp_delete_jobschedule删除旧计划并使用
sp_add_jobschedule创建新计划但得到相同的结果。下一个运行日期今天仍然是

。我尝试使用sp_update_jobschedule修改现有的时间表，以便

sysjobschedules中的freq_relative_interval是正确的，但是下一个运行日期是今天晚上10点。

我注意到在sp_update_jobschedule中有一些代码被
评论为通知SQLServerAgent更改，但只有在我们知道作业已被缓存的情况下才会通知它，它会检查作业是否被缓存？然后调用sp_sqlagent_notify。我甚至单独从sp_update_jobschedule调用了sp_sqlagent_notify，并且今天晚上10点仍然显示
作业作为下一个运行日期。

sp_update_jobschedule检查sysjobservers表中的这个作业，
所以我从表中删除了那行并尝试通过上述各种方法重新安排作业。
sysjobservers中的行是默认创建的
<在这个过程中，但是我工作的下一个运行日期仍然显示今天的日期（每次我检查时都会刷新）。

我能说出的唯一不同之处在你工作的所有28天工作之间，这个工作将是7-8个小时，而其他工作需要几分钟或几秒钟才能运行。除了sysjobschedules，

sysjobs或sysjobservers之外还有其他
系统表需要检查吗？这些表无法更新，导致我的作业计划被缓存？是运行

这份工作的持续时间不允许它在28天的周期内吗？

我已经筋疲力尽了所有的选择。任何帮助将不胜感激。

谢谢，

Mike Orlando
CAMP系统

< blockquote> Steve Kass（s​​k***@drew.edu）写道：

鉴于我观察到的行为，我的猜测是，当这些例程被编程时，没有人注意到正确的过去开始工作的方式，也许是因为程序员从未想过有人会尝试使用SQL Server来改变历史。

然而，这仅仅是界面问题。我有这个工作是每隔28天运行
，并且在23日我手动运行它。现在我想要SQL

Agent将来为我运行它。由于我很懒，我希望SQL Agent

为我做这份工作。毕竟，这不就是为什么我们有电脑吗？

-

Erland Sommarskog，SQL Server MVP， es **** @ sommarskog.se


SQL Server SP3联机丛书
http://www.microsoft.com/sql/techinf...2000/books。 asp

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

解决方案

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

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

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

这篇关于作业调度需要帮助的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！