Search code examples
sql-servert-sqlsql-agent-job

Need to split two SQL Server Agent Jobs into different schedules

I have two SQL Agent jobs that share the same schedule due to an error I made during the creation of the second job. I generated a script in SSMS and edited some values, but I left the schedule_uid the same. Now it turns out that while those two jobs are running at the same time, they are corrupting each other's data.

What I need to do is leave the original job alone, but create a new schedule and have the second job use this new schedule. However, my searches for the correct way to do this have all resulted in dead-ends.

None of this can be done using a UI .. it all must be scripted so it can be run during a maintenance window without me present.

Thanks in advance for any assistance.

Solution

  • Use msdb.dbo.sp_detach_schedule followed by msdb.dbo.sp_add_jobschedule.