Export (0) Print
Expand All

Scheduling Jobs

SQL Server 2000

Scheduling your administrative jobs is one way to automate administrative tasks. You can schedule local jobs or multiserver jobs. You can define a job to run:

  • Whenever SQL Server Agent starts.

  • Whenever CPU utilization of the computer is at a level you have defined as idle.

  • One time, at a specific date and time.

  • On a recurring schedule.

  • In response to an alert.

You can also execute a job manually; scheduling jobs is optional.

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

All jobs are enabled by default. To prevent a job from running according to its schedule, you must disable the schedule. The job can still execute in response to an alert or when a user runs the job manually.

SQL Server Agent automatically disables schedules that are no longer current. If you edit the schedule after it has been disabled by SQL Server Agent, you must explicitly reenable it. Schedules are disabled if:

  • They are defined to run one time, at a specific date and time, and that time has passed.

  • They are defined to run on a recurring schedule, and the end date has passed.
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 most advantageous time to execute jobs.

For example, you can schedule a daily backup job to occur during CPU idle time and slow production periods.

Before you define jobs to execute during CPU idle time, determine how much CPU the job requires. You can use SQL Profiler or System Monitor (Performance Monitor in Windows NT 4.0) to monitor server traffic and collect statistics. You can use the information you gather to set the CPU idle time percentage.

Define the CPU idle condition as a percentage below which the average CPU usage must remain for a specified time. Next, set the amount of time. When this time has been exceeded, SQL Server Agent starts all jobs that have a CPU idle time schedule.

To schedule a job

Enterprise Manager

Transact-SQL

SQL-DMO

To disable a job

Enterprise Manager

Transact-SQL

SQL-DMO

To set CPU idle time and duration

Enterprise Manager

See Also

sp_help_jobschedule

sysjobschedules

Was this page helpful?
(1500 characters remaining)
Thank you for your feedback
Show:
© 2014 Microsoft