Have you ever wondered how SQL Agent Job Schedule Got Changed Automatically for one job while you changed the schedule of another job.
Problem Summary:
Your team got a requirement to change the schedule of a job to fix the conflict with another job/process. Team changed the schedule, but another job starts failing/taking longer to complete. Now you are troubleshooting. After scratching your head for quite sometime you realized that it is running on a different schedule. You checked with your team and got confirmation that nobody changed the schedule of the this job.
How SQL Agent Job Schedule Got Changed Automatically:
First thing to check is all recent changes on the SQL Instances. Important point to realize is no matter how minor change you are doing in production environment, you should always have a change task to track.
Straightaway you found a change task performed to create new job. Now this is first clue to check the activity. In our case we found that script of another job was used in the SQL Instance to create the new Job.
Now let’s see what happens when you script out a job. In the following demo setup I have one job named “Agent Job A”. Will script out the job and create another job “Agent Job B”.
Right Click on the job and script out:
The below highlighted portion of the job code is for Schedule.
Let’s see what happens if we just change the job name in the script to create another job and then change the schedule of the new job.
Execute the script and you can see another job is created with name “Agent Job B”
Now let’s check the schedule of both the jobs. It should be same as we haven’t changed anything.
You can get the script from the following link Script To List SQL Agent Job Schedule to get the output as above. As you can see both jobs have exactly same schedule. Now let’s manually change the schedule of “Agent Job B” to run only on Saturday as an example.
Right Click on the job “Agent Job B” > Go to Properties > Schedules > Edit
After making the change click on Ok and then again Ok. Now execute the same query again to check the schedules:
You can see “Agent Job A” schedule also same without even touching the job. Continue reading to know the reason, fix and how to prevent it.
