In a previous post (Create a regularly scheduled job), we described how to create a schedule using the SSMS visual interface.

In this post we will touch on issues related to schedules – how to create them in TSQL, how to see if they work as expected and how to attach/detach jobs to schedules.

One very useful feature available in SSMS is the Script Action. This action is available in many dialogs in Sql Agent. Here it is how it looks in the Job Properties dialog:

When creating , editing or attaching a schedule, this action can be very helpful to understand how Sql Agent works. The script created this way can be edited, saved for backup or executed against a different server.

Here is a script that creates a schedule and attach it to a job:

USE [msdb]
GO
DECLARE@schedule_idint
EXECmsdb.dbo.sp_add_jobschedule @job_id=N'GUID',@name=N'TestSchedule',
       @enabled=1,
       @freq_type=8,
       @freq_interval=3,
       @freq_subday_type=1,
       @freq_subday_interval=0,
       @freq_relative_interval=0,
       @freq_recurrence_factor=1,
       @active_start_date=20110119,
       @active_end_date=99991231,
       @active_start_time=0,
       @active_end_time=235959,@schedule_id=@schedule_idOUTPUT
select@schedule_id
GO

First, some notes:

- For explanations on the meaning of the different parameters, read the sp_add_jobschedule article in MSDN.

- This stored procedure will be deprecated in SQL 11. Here is the same functionality using the current SPs:

USE [msdb] GO DECLARE @schedule_id int EXEC msdb.dbo.sp_add_schedule @name=N'TestSchedule', @enabled=1, @freq_type=8, @freq_interval=3, @freq_subday_type=1, @freq_subday_interval=0, @freq_relative_interval=0, @freq_recurrence_factor=1, @active_start_date=20110119, @active_end_date=99991231, @active_start_time=0, @active_end_time=235959, @schedule_id = @schedule_id OUTPUT GO exec sp_attach_schedule @job_id = 'JOB GUID', @schedule_id = @schedule_id GO 

Here is what the script does:

1) Create a schedule with the specified parameters and return the schedule id

2) Attach the schedule to an existing job.

Tips & Tricks

• Remember, schedules are first class objects just like jobs. They can be created without being connected to any job. They have an owner and permissions are checked on every operation (update, delete, attach, detach).
• You can use the job and schedule names instead of their Ids in sp_attach_schedule.
• One question commonly asked is: How do I know the schedule I created will run as I want?

First of all, a schedule will not trigger unless it is attached to a job.

Once a schedule is attached to a job, we can see the next run date and next run time in sysjobschedules (note: the date format used is YYYYMMDD and the time format is HHMMSS).

Because Sql Agent caches the schedule information to increase performance, sometimes sysjobschedules is not updated for a while (the values will be zero). One quick way to force update is to restart the service.

• Some of the schedules can be relative – meaning the next run date/time depend on the current day and time. If you run the same script at a different date, you will get a different next scheduled run.
• Active_start_time and Active_end_time have a very specific meaning: they determine the active window for the schedule during a day. Independent of the type of schedule (daily, weekly, monthtly) there are 2 steps to determine the next execution:
• Calculate the day (tomorrow, next month etc)
• Once the day is known, the schedule will run at a specific time(s) during the day.

If the schedule runs once during the day, Active_start_time specifies that time.

If the schedule will run multiple times (e.g. hourly or every X minutes), Active_start_time specifies the time of the first execution on that day. After that, the schedule will trigger at the required intervals until the time is past Active_end_time when the schedule stops for the day.

This posting is provided "AS IS" with no warranties, and confers no rights. 
Use of included script samples are subject to the terms specified athttp://www.microsoft.com/info/cpyright.htm