When you create a SQL Server Agent job, you have the option of creating a new schedule for that job or using one that already exists.

Job schedules can be managed independently of jobs. You can use sp_add_schedule to create the schedule and sp_attach_schedule to attach the schedule to a job.

This enables you to attach the same schedule to multiple jobs.

Example

Suppose we create a job using the following code:

USE msdb;  
GO  
EXEC sp_add_job  
    @job_name = N'BackupPetHotelDB' ;  
GO  
EXEC sp_add_jobstep  
    @job_name = N'BackupPetHotelDB',  
    @step_name = N'Run the backup',  
    @subsystem = N'TSQL',  
    @command = N'BACKUP DATABASE [PetHotel] TO DISK = N''C:\Backups\PetHotel.bak'' WITH CHECKSUM',
    @retry_attempts = 5,  
    @retry_interval = 5;  
GO
EXEC sp_add_schedule 
    @schedule_name = N'RunDailyAM',
    @freq_type = 4,
    @freq_interval = 4,
    @active_start_time = 041000;
GO  
EXEC sp_attach_schedule  
   @job_name = N'BackupPetHotelDB',  
   @schedule_name = N'RunDailyAM';  
GO  
EXEC sp_add_jobserver  
    @job_name = N'BackupPetHotelDB';  
GO

That code included the sp_add_schedule stored procedure. This procedure creates a schedule that can be used by any number of jobs. In our case, we called the schedule RunDailyAM.

We then used sp_attach_schedule to attach that schedule to the SQL Server Agent job that we just created. This is the stored procedure that we can use to attach the same schedule to subsequent jobs.

Now let’s create another SQL Server Agent job, but this time we won’t include the sp_add_schedule procedure:

USE msdb;  
GO  
EXEC dbo.sp_add_job  
    @job_name = N'BackupWorldDB' ;  
GO  
EXEC sp_add_jobstep  
    @job_name = N'BackupWorldDB',  
    @step_name = N'Run the backup',  
    @subsystem = N'TSQL',  
    @command = N'BACKUP DATABASE [World] TO DISK = N''C:\Backups\World.bak'' WITH CHECKSUM',
    @retry_attempts = 5,  
    @retry_interval = 5;  
GO
EXEC sp_attach_schedule  
   @job_name = N'BackupWorldDB',  
   @schedule_name = N'RunDailyAM';  
GO  
EXEC sp_add_jobserver  
    @job_name = N'BackupWorldDB',
    @server_name = N'(LOCAL)';  
GO

We didn’t use the the sp_add_schedule procedure, because we used the schedule that had already been created in the previous job.

We did however, use the sp_attach_schedule stored procedure, because we needed to attach the previously created schedule to our new job.

So both jobs will now run on the same schedule.

Find out How Many Jobs that a Schedule is Attached to

You can use the sp_help_jobcount procedure to return the number of jobs that a given schedule is attached to. Just provide the schedule name or ID when calling the procedure.

Example:

EXEC sp_help_jobcount @schedule_name = 'RunDailyAM';

Result:

JobCount
--------
 2