Creating Schedules

Scheduling administrative jobs means defining the condition or conditions that cause the job to start running. You can schedule any kind of job. More than one job can use the same job schedule. A user can attach and detach schedules from jobs.

For example, you can schedule a job to run:

  • When SQL Server Agent starts.
  • When CPU usage of the computer is at a level that you have defined as idle.
  • One time, at a specific date and time.
  • On a recurring schedule.

As an alternative to job schedules, you can also create an alert that responds to an event by running a job.

Note

Only one instance of the job can be run at a time. If you try to run a job manually while it is running as scheduled, SQL Server Agent refuses the request.

To prevent a scheduled job from running, you must disable the schedule, disable the job, remove the schedule from the job, or stop the SQL Server Agent service. If the schedule is not enabled, the job can still run in response to an alert or when a user runs the job manually. When a job schedule is not enabled, the schedule is not enabled for any job that uses the schedule.

You must explicitly re-enable a schedule that has been disabled. Editing the schedule does not automatically re-enable the schedule.

Start Date

The start date of a schedule must be greater than or equal to 19900101.

To make sure that a schedule begins when you want, it is important to select a start date for the schedule, and verify the start date after the schedule is created. Always select a date equal to or greater than the date that the schedule is created. Dates in the past may create schedule start dates that cause weekly or monthly recurrences to operate unexpectedly. Using the current date may also cause an unexpected start date for a weekly or monthly recurrence. After the schedule is created, always check the start date to make sure that it is the date that you want.

CPU Idle Schedules

To maximize CPU resources, you can define a CPU idle condition for SQL Server Agent. SQL Server Agent uses the CPU idle condition setting to determine the best time to run jobs. For example, you can schedule a job to rebuild indexes during CPU idle time and slow production periods.

Before you define jobs to run during CPU idle time, determine the load on the CPU during ordinary processing. To do this, use SQL Server Profiler or Performance Monitor to monitor server traffic and collect statistics. You can then use the information that you collect to set the CPU idle time percentage and duration.

Define the CPU idle condition as a percentage below which CPU usage must remain for a specified time. Next, set the amount of time. When the CPU usage is below the specified percentage for the specified amount of time, SQL Server Agent starts all jobs that have a CPU idle time schedule. For more information about how to use SQL Server Profiler or Performance Monitor to monitor CPU usage, see Monitoring CPU Usage.

To schedule a job
To disable a scheduled job
To set CPU idle threshold and duration

See Also

Other Resources

sp_help_jobschedule (Transact-SQL)
sysjobschedules (Transact-SQL)

Help and Information

Getting SQL Server 2005 Assistance

Change History

Release History

17 November 2008

New content:
  • Added information about the start date behavior for a schedule.