Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

How to setup and manage SQL Server Agent shared job schedules


By:   |   Read Comments (2)   |   Related Tips: More > SQL Server Agent

New Whitepaper > 10 Ways We Can Steal Your Data >> download now


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.

Confirm the jobs created in SSMS

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

Pick schedul to share in job

  • A list of available schedules will appear with information about the schedule properties as shown below

List of avaialable schedules

  • 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.

Schedule with attached jobs count

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

Click on manage schedules in SSMS

  • In resultant frame, click the 'New...' button

Click new button in manage schedules frame

  • 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


Last Update:


signup button

next tip button



About the author
MSSQLTips author Atif Shehzad Atif Shehzad is a passionate SQL Server DBA, technical reviewer and article author.

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Wednesday, July 16, 2014 - 2:35:59 AM - Mohammad Shahnawaz Back To Top

As salamo alaikum wa rahmatullah,

Very nice aritcle and very helpfull. please share more tips and articles

thanking you

Allah Hafiz


Monday, October 21, 2013 - 1:25:08 PM - D Wend Back To Top
  • Less schedules means more simplified scheduled jobs environment, so minimize the number of schedules by utilizing the shared schedule option.

Please change from Less to Fewer

 Use less when you’re referring to something that can’t be counted or doesn’t have a plural (e.g. money, air, time, music, rain).

Use fewer if you’re referring to people or things in the plural (e.g. houses, newspapers, dogs, students, children).

 

 


Learn more about SQL Server tools