This article is in continuation of How SQL Agent Job Schedule Got Changed Automatically. Here we’ll see how to find the root cause and resolve the issue. Please go through the first article if you have directly landed here to understand what issue we are talking about.

Script to check the SQL Agent Job Schedule details

Execute the below script on the SQL Instance:

SELECT sj.name, jc.schedule_id, jc.job_id 
FROM msdb..sysjobschedules jc
INNER JOIN msdb..sysjobs sj
ON jc.job_id = sj.job_id

Output:

SQL Agent Job Schedule got Changed
Fig: 1

As you can see the schedule_id (9 in this example) is same for both the jobs “Agent Job A” and “Agent Job B”.  This is the point often overlooked. To put it another way, when you script out one job to create another, the schedule_id remains same. Hence whenever you change schedule of one job, it will change all other job’s schedule having the same schedule_id. Now lets see how to fix the issue.

Script to change the schedule_id of a SQL Agent Job

Execute the below query to change the schedule_id of the job “Agent Job B”. Not to mention that you need to change the cheduler_id and job_id in the script.

UPDATE msdb..sysjobschedules
SET schedule_id=1  --Put a unique value
WHERE job_id='9AC70265-8620-402E-BAD2-87F77D528B5C'  -- Change the job_id as per your job

Now if you run the first script again to check the SQL Agent Job Schedule Id, you can see change as highlighted in the screen shot.

SQL Agent Job Schedule got Changed
Fig: 2

After you change the schedule_id, you can edit the “Agent Job B” schedule  without affecting “Agent Job A”.

Until now we have seen how to resolve the existing issue. Now let me show you how to prevent this while creating a job from another job script.

How to prevent having same schedule_id

Yes, you can very well avoid this by minor change while executing the script of another job. In this example I am going to create another job named ” Agent Job C” by scripting out “Agent Job A”.

Script out the job:

SQL Agent Job Schedule got Changed
Fig: 3

You just need to comment/Delete the reference of @schedule_uid in the job code as shown below:

SQL Agent Job Schedule got Changed
Fig: 4

Now, let’s execute the first query provided in this article again to check the schedule_id of the jobs:

SQL Agent Job Schedule got Changed
Fig: 5
Summary:

SQL Agent Job schedules are tied with Schedule_id. If you ever come across a situation when changing the schedule of one SQL Agent job affects other Job schedules you need to change the schedule_id of one of the jobs. Always remember to comment out/delete the reference of schedule_uid whenever you script out a job to create another job.

<<Previous