If a job needs to run hourly from 3AM to 7AM, once at 12:00AM, and hourly from 6PM to 9PM everyday, how would you schedule the job in SQL server? If your answer to the question is to define three individual jobs, you will get a better way after reading this article.

SQL server has a very flexible scheduling system. This article will focus on the following topics:

• Attach multiple schedules to a job
• Schedules for frequent execution (schedule interval <1 minute) jobs
• Start a job whenever CPU is idle
• Start a job when SQL Agent server starts

Attach multiple schedules to a job

This answers the question in the beginning of the article.  SQL server allows to define multiple schedules to a job.  In SQL agent job schedule tab, click “New Schedule…” button will add schedules to a job.

This script will create a job “MyJob” with two schedules showed in the above

diagram. The schedules are saved in msdb..sysjobschedules table. We can run msdb..sp_help_jobschedule to check the schedules, e.g.:

msdb..sp_help_jobschedule @job_name='MyJob'

The results:

The time frame (active start and end date) and daily running time (active start and end time) of schedules attached to a job can be overlapped. SQL server calculates the next run time in the following way:

• Calculate the next run time for each enabled schedule based on its definition
• Among them, find the soonest time as the next run time of the job
• The time and the schedule will be the next run schedule and next run time for the job
• If more schedules produce the same next run time, the schedule with the smallest schedule_id will be

  the next run schedule.

E.g., for the job with the following schedules, the execution history will be as follows:

Schedules for frequent executing jobs

SQL server jobs can have high running frequency with interval less than 1 minute. But this capability is not exposed to SQL agent GUI, only “Hours” and “Minutes” are supported.

This can be achieved by calling the stored procedure msdb.dbo.sp_add_jobschedule or msdb.dbo.sp_update_jobschedule.

The stored procedures have a parameter @freq_subday_type, it has three values according to BOL:

For the same column in msdb..sysjobschedules table, it has four values, which includes 0x2 for seconds.

Though 0x2 is not documented for the two stored procedures, it can accept the value and create the schedule correctly, e.g. this script will create a job runs every 30 seconds everyday.

When a job is scheduled to run in the interval less than 1 minute, the schedule showed up in SQL agent GUI will not be correct, e.g. for the job created by the previous script, the schedule will be showed as following:

Jobs with less than 1 minute daily frequency may use lots of resources and may have negative impact on overall server performance.

Start a job whenever CPU is idle

SQL server can run jobs based on CPU utilization if the CPU idle condition is enabled and met. First of all we need to define the threshold for CPU idle in SQL agent property -> Advanced tab:

According to BOL:

·        Average CPU usage falls below:

Specify the idle CPU condition by percentage. Idle is when the average CPU usage remains below the selected percent for the specified number of seconds.

·       And remains below this level for

Specify the idle CPU condition by seconds. Idle is when the average CPU usage remains below the selected percentage for the specified number of seconds.

If the Idle CPU condition is not defined, SQL server does not give any error messages when you define jobs to run on CPU idle. However, SQL Agent server gives a message in the error log when it starts up if the idle condition is not defined:

2006-02-07 08:24:00 - + [396] An idle CPU condition has not been defined - OnIdle job

schedules will have no effect

To use this schedule, in SQL agent job property -> schedule tab, select “New Schedule…” or “Edit…”, and

select the second option in “Schedule Type” as following:

To define the schedule by calling stored procedure msdb.dbo.sp_add_jobschedule, we need to pass 128 for @freq_type.

Please keep in mind that CPU idle does not mean the server is idle, e.g. the I/O subsystem may be very busy at the time. So this type of schedule is suitable for jobs with light I/O and heavy CPU utilization, and the job is recurring with short running duration.

Start a job when SQL Agent server starts

The job with this type of schedule runs once SQL Agent server starts. It’s suitable to clean up or set up running environment for other jobs, e.g. create or clean up certain folder etc. To use this schedule, in SQL agent job property -> schedule tab, select “New Schedule…” or “Edit…”, and select the first option in “Schedule Type” as

following:

To define the schedule by calling stored procedure msdb.dbo.sp_add_jobschedule, we need to pass 64 for @freq_type.

Conclusion

SQL server has a very flexible scheduling system. Special irregular schedules can be defined for a job by combining different type of schedules, such as recurring, one time or CPU idle etc.

SQL server 2005 separated job and schedule. So schedules can be shared by multiple jobs.