Job Scheduling Overview
Job scheduling in SQL Server enables you to automate routine tasks such as backups, maintenance, and data processing. Schedules are associated with jobs and define when those jobs will run.
Schedule Types
| Type | Description |
|---|---|
| One-time | Executes a job once at a specific date and time. |
| Recurring | Executes a job on a recurring basis (daily, weekly, monthly). |
| Start automatically when SQL Server Agent starts | Runs the job each time the Agent service starts. |
Create a Schedule
Use the following T‑SQL to create a recurring daily schedule:
CREATE SCHEDULE [DailyMidnight]
ON
FREQUENCY = DAILY,
INTERVAL = 1,
ACTIVE_START_TIME = 000000;
ON
FREQUENCY = DAILY,
INTERVAL = 1,
ACTIVE_START_TIME = 000000;
Modify a Schedule
To change a schedule, use ALTER SCHEDULE:
ALTER SCHEDULE [DailyMidnight]
ACTIVE_END_TIME = 235959;
ACTIVE_END_TIME = 235959;
Disable a Schedule
Disabling a schedule prevents the associated job from running without deleting the schedule.
EXEC msdb.dbo.sp_update_schedule
@schedule_name = N'DailyMidnight',
@enabled = 0;
@schedule_name = N'DailyMidnight',
@enabled = 0;
Monitoring Schedules
View schedule details with the following query:
SELECT name, enabled, freq_type, freq_interval, active_start_time, active_end_time
FROM msdb.dbo.sysjobschedules
JOIN msdb.dbo.sysschedules ON sysjobschedules.schedule_id = sysschedules.schedule_id;
FROM msdb.dbo.sysjobschedules
JOIN msdb.dbo.sysschedules ON sysjobschedules.schedule_id = sysschedules.schedule_id;