SQL: Audit to find identical schedules on multiple jobs

At one point it surprised me to see a SQL Agent job schedule in more than one job. A lot of things can cause this, especially if you script and clone jobs.

Here’s the audit you can use to find the jobs which share the exact same schedule. If you change it in one place, ooops, it changes everywhere!


/*
Audit to find identical schedules on multiple jobs
*/

SELECT  [j].[name] [JobName],
        [s].[name] [DupScheduleName],
		[sjs].[schedule_id]
FROM    [msdb].[dbo].[sysjobschedules] [sjs]
JOIN    [msdb].[dbo].[sysschedules] [s]
ON      [s].[schedule_id] = [sjs].[schedule_id]
JOIN    [msdb].[dbo].[sysjobs] [j]
ON      [j].[job_id] = [sjs].[job_id]
WHERE   [sjs].[schedule_id] IN (SELECT   [sjs].[schedule_id]
                               FROM     [msdb].[dbo].[sysjobschedules] [sjs]
                               JOIN     [msdb].[dbo].[sysschedules] [s]
                               ON       [s].[schedule_id] = [sjs].[schedule_id]
                               GROUP BY [sjs].[schedule_id]
                               HAVING   COUNT(*) > 1)
 

After you find them, you will have to drop the dups and recreate using a different name. Possibly just recreating and getting a new schedule_id would work as well.