Configure schedule for SQL Server Agent job

Applies to: SQL Server Azure SQL Managed Instance

This article describes how to configure a schedule for a job for the SQL Server Agent to run on SQL Server or Azure SQL Managed Instance. Configure a SQL Server Agent job schedule by using SQL Server Management Studio (SSMS), Transact-SQL, or SQL Server Management Objects (SMO).

Important

On Azure SQL Managed Instance, most, but not all SQL Server Agent features are currently supported. See Azure SQL Managed Instance T-SQL differences from SQL Server for details.

Security

For detailed information, see Implement SQL Server Agent Security.

Prerequisites

To configure a schedule for a job, you should have already created a job. If you need to create a job, see Create a Job.

Configure schedule for a job

You can configure a schedule for a SQL Agent job by using SQL Server Management Studio (SSMS), Transact-SQL, or SQL Server Management Objects (SMO).

SQL Server Management Studio (SSMS)

To create a schedule in SQL Server Management Studio, follow these steps:

1. Open SQL Server Management Studio (SSMS).

2. In Object Explorer, connect to an instance of the SQL Server, and then expand that instance.

3. Expand SQL Server Agent, expand Jobs, right-click the job you want to schedule, and select Properties.

4. Select the Schedules page, and then select New.

5. In the Name box, type a name for the new schedule.

6. Clear the Enabled check box if you don't want the schedule to take effect immediately following its creation.

7. For Schedule Type, select one of the following:

   • Select Start automatically when SQL Server Agent starts to start the job when the SQL Server Agent service is started.

   • Select Start whenever the CPUs become idle to start the job when the CPUs reach an idle condition.

   • Select Recurring if you want a schedule to run repeatedly. To set the recurring schedule, complete the Frequency, Daily Frequency, and Duration groups on the dialog.

   • Select One time if you want the schedule to run only once. To set the One time schedule, complete the One-time occurrence group on the dialog.

To attach a schedule to a job in SSMS, follow these steps:

1. In Object Explorer, connect to an instance of SQL Server, and then expand that instance.

2. Expand SQL Server Agent, expand Jobs, right-click the job that you want to schedule, and select Properties.

3. Select the Schedules page, and then select Pick.

4. Select the schedule that you want to attach, and then select OK.

5. In the Job Properties dialog box, double-click the attached schedule.

6. Verify that Start date is set correctly. If it isn't, set the date when you want for the schedule to start, and then select OK.

7. In the Job Properties dialog box, select OK.

Transact-SQL

Use sp_add_schedule to create a schedule and then use sp_attach_schedule to attach the schedule to an existing job.

The following Transact-SQL example schedules a job (named UpdateStats) to run on a schedule (named NightlyJob) every day at 1:00 A.M:

USE msdb ;
GO
-- creates a schedule named NightlyJobs.
-- Jobs that use this schedule execute every day when the time on the server is 01:00.
EXEC sp_add_schedule
    @schedule_name = N'NightlyJobs' ,
    @freq_type = 4,
    @freq_interval = 1,
    @active_start_time = 010000 ;
GO
-- attaches the schedule to the job UpdateStats
EXEC sp_attach_schedule
    @job_name = N'UpdateStats',
    @schedule_name = N'NightlyJobs' ;
GO

SQL Server Management Objects (SMO)

Use the JobSchedule class by using a programming language that you choose, such as Visual Basic, Visual C#, or PowerShell. For more information, see SQL Server Management Objects (SMO).

Related content

• Create a job
• SQL Server Agent