Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

SQL Server Job Scheduling

By Peter He, (first published: 2006/03/08)

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:

Schedule_id

Schedule_name

Schedule description

16431

First schedule

Every day every 4 minute(s) between 00:00:00AM and 08:00:00PM

16432

Second schedule

Every day every 2 minute(s) between 11:22:00AM and 10:00:00AM

No.

Time

Next run schedule

Next run time

1

2006-01-11 11:13:00

First schedule

2006-01-11 11:16:00

2

2006-01-11 11:17:00

First schedule

2006-01-11 11:20:00

3

2006-01-11 11:21:00

Second schedule

2006-01-11 11:22:00

4

2006-01-11 11:23:00

First schedule

2006-01-11 11:24:00

3

2006-01-11 11:25:00

Second schedule

2006-01-11 11:26:00

4

2006-01-11 11:27:00

First schedule

2006-01-11 11:28:00

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:

Value

Description (unit)

0x1

At the specified time.

0x4

Minutes.

0x8

Hours.

 

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.

Total article views: 37632 | Views in the last 30 days: 59
 
Related Articles
FORUM

Schedule a job in sql server agent

Schedule a job in sql server agent

FORUM

Scheduled agent jobs in SQL Server Express ?

Is it possible ? Scheduled agent jobs in SQL Server Express.

FORUM

SQL Server Agent Scheduled Jobs - Cannot find DLL when invoking executable program

SQL Server Agent, Scheduled Jobs, Cannot find DLL

FORUM

SQL Server Agent Job Creation Through .NET

How to use SQL Server Agent (SQL SRVR 2005) to define jobs and schedule them to automatically with ....

FORUM

SSIS package is scheduledas SQL agent job but while connecting todata sources, it is usingsql agent account to connect , not the connections defined in configurations

SSIS package is scheduled as SQL agent job but while connecting todata sources, it is usingsql agent...

Tags
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones