Problem
I have configured a few SQL Server agent jobs for different purposes on my database server. Some jobs are required to run at the same time. In other words I am required to configure the same schedule for multiple SQL Server agent jobs. Is there any way to share a single schedule among multiple jobs?
Solution
SQL Server 2005 and SQL Server 2008 both provide an option to share a single schedule for multiple jobs when required. This feature could help to improve the simplicity and manageability of your SQL Server scheduled jobs environment. You can share any existing schedule even if it is already associated with a job or you can create a new schedule without any associated job, and attach it later to one or more jobs. In the upcoming sections we will go through both scenarios along with a couple of considerations.
Please note the images used in this tip are from SQL Server 2005. There may be some minor GUI differences for SQL Server 2008, but the overall procedure has been tested and it works on both versions of SQL Server.
Share a schedule already attached with a job
The following script will create a job named ‘Tip-demo-Job1’ with an attached schedule ‘Mid-night-schedule’ configured to run at 12:30 am. This script assumes that your current login has rights to manage schedules and jobs in SQL Server agent. The active login will be the owner of the created demo jobs. To work with configurations of schedules and jobs a Login is required to be a member of the sysadmin fixed server role or it should be member of the SQLAgentUserRole, SQLAgentReaderRole or SQLAgentOperatorRole fixed database roles in msdb database.
–Script #1: Create sample jobs and schedule on server
USE msdb
GO
— (a). Create Job
EXEC dbo.sp_add_job @job_name = N’Tip-demo-Job1′
GO
— (b). Create Job Step1
EXEC sp_add_jobstep
@job_name = N’Tip-demo-Job1′,
@step_name = N’Step1′,
@on_success_action= 1,
@command = ‘select ”Job step completed”’
— (c). Create schedule to run at 24:30
EXEC dbo.sp_add_schedule
@schedule_name = N’Mid-night-schedule’,
@freq_interval = 1,
@freq_type = 4,
@active_start_time = 003000
— (d). Attach schedule to job
EXEC sp_attach_schedule
@job_name = N’Tip-demo-Job1′,
@schedule_name = N’Mid-night-schedule’
GO
At this point we have a schedule ‘Mid-night-schedule’ attached to job ‘Tip-demo-Job1’. The schedule may be shared with any other job. So here we will attach the ‘Mid-night-schedule’ with a new job by using both SSMS and T-SQL.
I have also created a new job ‘Tip-demo-Job-2’ by using the commands (a and b) provided above in script #1.
The new job ‘Tip-demo-Job2’ exists without any attached schedule. Follow these steps to attach the existing ‘Mid-night-schedule’ to ‘Tip-demo-Job2’.
- Double click the job ‘Tip-demo-Job2’ in SSMS
- Click schedules in left panel
- The schedule list is empty, now click the ‘Pick…’ button to pick an existing schedule
- A list of available schedules will appear with information about the schedule properties as shown below
- You can view the settings for any schedule by selecting it and clicking the ‘Properties’ button
- After verifying the required configuration, select the ‘Mid-night-schedule’ from the list and click OK
Now the ‘Mid-night-schedule’ is shared by both jobs (Tip-demo-Job1 and Tip-demo-Job-2) as shown in the following image in the Manage Schedules frame. Clicking on the count of Jobs, as shown below, will display the names of the jobs using this schedule.
Alternatively, the same task can be performed using T-SQL. We can attach the ‘Mid-night-schedule’ to ‘Tip-demo-Job2’ using T-SQL as follows.
— Script #2: Attach job with schedule
USE msdb
GO
EXEC sp_attach_schedule
@job_name = N’Tip-demo-Job2′,
@schedule_name = N’Mid-night-schedule’
GO
Create a schedule for later use
A schedule can also exist without a job associated with it. You can create a schedule and attach it to as many jobs as required at a later time. Here we will create a schedule ‘End-of-shift’ to run at 6:15 pm daily. No job will be attached to this schedule at the moment. Independent schedules can be created through SSMS or T-SQL. To create a new independent schedule through SSMS follow these steps.
- Right click on Jobs folder in SSMS and select Manage Schedules
- In resultant frame, click the ‘New…’ button
- The schedule window will appear. Provide the required parameters and click OK to save the newly created independent schedule
- Verify the presence of ‘End-of-shift’ schedule in schedule list and click OK
- Now an independent schedule exists without any associated jobs
The same task can be performed with T-SQL as follows.
— Script #3: Create schedule to run at 6:15 pm
USE msdb
GO
EXEC dbo.sp_add_schedule
@schedule_name = N’End-of-shift’,
@freq_interval = 1,
@freq_type = 4,
@active_start_time = 181500
The schedule ‘End-of-shift’ may be used with any number of jobs in the same way as mentioned above.
Share schedule with previously created jobs
A schedule may be attached with a previously created job, even if the job already has a schedule attached with it. In our case we will attach the schedule ‘End-of-shift’ with any of the previously created jobs. The following script will attach the schedule ‘End-of-Shift’ with both of the jobs already created (Tip-demo-Job1 and Tip-demo-Job2).
–Script #4: Attach schedule with jobs
USE msdb
GO
EXEC sp_attach_schedule
@job_name = N’Tip-demo-Job1′,
@schedule_name = N’End-of-Shift’
GO
EXEC sp_attach_schedule
@job_name = N’Tip-demo-Job2′,
@schedule_name = N’End-of-Shift’
GO
Schedules with same name may exist
In the scheduled jobs environment, the existence of more than one schedule with the same name is possible. So if you have two schedules with the same name you may get an error while attaching the schedule with a job. For example in our case if we have more than one schedule with the name ‘Mid-night-schedule’ then the following error message will occur when executing the above script.
Msg 14371, Level 16, State 1, Procedure sp_verify_schedule_identifiers, Line 113
There are two or more schedules named “Mid-night-schedule”. Specify @schedule_id instead of @schedule_name to uniquely identify the schedule.
In such case use the parameter @schedule_id instead of @schedule_name. In msdb, the system table dbo.sysschedules may be queried to get the ID of any schedule. You can also get the schedule ID through the SSMS GUI. Right click on the Jobs folder and select the Manage Jobs option from the right click menu. A frame containing all schedules with the schedule ID will appear. You can note the schedule ID of the appropriate schedule for unique distinction of the schedule.
Important point to note
Schedules and jobs in SQL Server have a flexible relation. More than one schedule may be added to a single job and multiple jobs may be attached with a single schedule. While sharing a schedule among multiple jobs, it is important to note that if you change any parameter or configuration of a shared schedule then the change will be reflected in all jobs that use that schedule. This feature adds more flexibility if considered properly, but also may result in confusion and abrupt functionality otherwise. For example if you access a shared schedule configuration window through one of its attached jobs and change the time configuration or disable the job, then this change will propagate to all jobs where the particular schedule is shared.
Next Steps
- Less schedules means more simplified scheduled jobs environment, so minimize the number of schedules by utilizing the shared schedule option.
- Click here to get details about system stored procedures for configuration of jobs and schedules
- Click here to read more about managing your scheduled jobs environment
