Understanding how SQL Server Agent Jobs can share schedules

By:   |   Updated: 2015-03-18   |   Comments (4)   |   Related: More > SQL Server Agent

Problem

I have a job that runs at a particular time during the week. However, this week it didn't run when I expected. I haven't modified that job in any way. I did modify another job's schedule, and the job in question ran at the exact time I set the second job to run. Why did editing the second job affect the first one?

Solution

The likely issue here is that the two jobs share a job schedule. If you expand SQL Server Agent in SSMS, and right-click on the Jobs folder, you'll get an option to manage the schedules that are in place:

Understanding SQL Server Agent Job Schedule Conflicts

This takes you to a new screen where you can see every schedule. Here are the default job schedules for a given installation of SQL Server:

Here are the default job schedules for a given installation of SQL Server

Note the column titles "Jobs in schedule." With the schedules listed above, there are no schedules with more than one job listed. Let's create a situation that should mirror what you say.

Creating Two SQL Server Agent Jobs with the Same Schedule

First, let's create a new job and create a new schedule. We don't have to put any steps in place for the job. All that we need to do is go to the Schedules page, click the New button, and then give the schedule a title. We'll leave it with the default scheduling values. I've named a schedule "Overburdened Schedule" as in the image below.

Creating Two Jobs with the Same Schedule

When we click OK, we should see the new schedule listed (note the ID of "New"):

we should see the new schedule listed

And when we go back to Manage Schedules, we will see our new schedule in the list along with the one job which uses it:

go back to Manage Schedules

Let's create a second job and again go to the Schedules page but this time click the Pick button:

Let's create a second job and again go to the Schedules page

We'll get an interface that looks very similar to the Manage Schedules one, however in this case we can pick what schedule we want to use. Click on "Overburdened Schedule" to select it and click OK:

We'll get an interface that looks very similar to the Manage Schedules

Now if we go back to Manage Schedules we'll see two jobs listed against the schedule in question:

Now if we go back to Manage Schedules

Changing the SQL Server Agent Schedule and Affecting Both Jobs

Edit the first job you created and click on the Schedules page again. This time click the Edit button:

Changing the Schedule and Affecting Both Jobs:

Change the time in some way. Here I'm changing when it runs from 12 AM to 6 AM:

Change the time in some way

If we then go to the second job and we look at the schedule in the list, we see that it now says 6 AM:

If we then go to the second job and we look at the schedule in the list

If we go back to Manage Schedules, we will see the change there as well.

If we go back to Manage Schedules

Editing the schedule in any job that uses it affects all jobs which use that schedule. And this is likely the cause of your job being rescheduled unexpectedly.

Should I Use a Separate Schedule Per Job?

Given that changing the schedule for one job will affect any other job also using that same schedule, this is a fair question to ask. If you know that at some point in the future you'd want the jobs to run at different times, then create different schedules. However, if you have jobs that have to execute on the same schedule, you definitely want them to share the same schedule. Otherwise, someone who doesn't know that the two jobs must run together might reschedule one of them.

What if it's somewhere in between... you know the jobs don't have to run together and you don't know if they will need to run at different times later?  The general rule most IT folks try to follow is to keep things as simple as possible. This typically means reducing the number of schedules and dealing with the separation later, if it ever comes.

Next Steps


Last Updated: 2015-03-18


get scripts

next tip button



About the author
MSSQLTips author K. Brian Kelley K. Brian Kelley is a SQL Server author and columnist focusing primarily on SQL Server security.

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
Email me updates

Signup for our newsletter

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.





Friday, March 20, 2015 - 4:54:54 AM - John Stafford Back To Top

Great article - thanks.

 

I've been caught out by this when scripting out a job to create a slightly different version of it. It will create a second job with the same schedule, which I hadn't realised, and got the same issue as described in the article!

 

Now, when creating a job by scripting out an existing job, I delete the lines that add the schedule and add them manually afterwards in SSMS :-)


Thursday, March 19, 2015 - 9:05:18 AM - Hubert Trzewik Back To Top

The author described perfectly how shared schedules work. But still - most difficult stays in open question "Should I Use a Separate Schedule Per Job?".


Wednesday, March 18, 2015 - 10:07:29 AM - Ritesh Back To Top

you have written such a useful articles which is providing good information thank you


Wednesday, March 18, 2015 - 7:09:56 AM - Deepak Sharma Back To Top

Brian,

 

That is very good feature. I have created many jobs but never used that option.

Thank you for sharing this.



download

























get free sql tips

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



Learn more about SQL Server tools