By: K. Brian Kelley | Comments (5) | Related: > 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:
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:
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.
When we click OK, we should see the new schedule listed (note the ID of "New"):
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:
Let's create a second job and again go to the Schedules page but this time click the Pick button:
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:
Now if we go back to Manage Schedules we'll see two jobs listed against the schedule in question:
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:
Change the time in some way. Here I'm changing when it runs from 12 AM to 6 AM:
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 go back to Manage Schedules, we will see the change there as well.
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
- Read how to generate a job schedule report.
- Learn more about sharing job schedules.
About the author
K. Brian Kelley is a SQL Server author and columnist focusing primarily on SQL Server security.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
