Scheduling an Agent Job for Weekday Office Hours Only

Weekday office hours fit in one SQL Server Agent schedule. Pick a weekly schedule, set the weekday mask to 62, and give it a start and end time. The window controls when the job may start. It does not stop a run that is already going.

Why the job should sleep at night

Say a monitoring job checks a business application every 15 minutes, all day and all night. At 3 AM it finds a harmless blip and sends an alert. Someone wakes up, looks, and goes back to sleep angry. After two or three nights like that, people mute the alerts. Now the real one gets missed.

If nobody can act on the check outside office hours, do not run it outside office hours. One schedule can do that. Let me build one in a demo, with the job and the schedule both disabled, so nothing really fires.

This demo creates server-level objects in msdb, one job and one schedule, and the last block removes both. SQL Server Agent does not need to be running for the demo.

Create the weekday schedule

A schedule needs a few numbers. Type 8 means weekly. Interval 62 is the weekday mask, which I explain below. Sub-day type 4 with interval 15 means every 15 minutes. The window is 08:00 to 18:00, written as 080000 and 180000.

IF EXISTS (SELECT 1 FROM msdb.dbo.sysjobs WHERE name = N'OfficeHoursDemo')
    EXEC msdb.dbo.sp_delete_job @job_name = N'OfficeHoursDemo', @delete_unused_schedule = 1;
IF EXISTS (SELECT 1 FROM msdb.dbo.sysschedules WHERE name = N'Weekdays 8 to 6')
    EXEC msdb.dbo.sp_delete_schedule @schedule_name = N'Weekdays 8 to 6';

EXEC msdb.dbo.sp_add_job
     @job_name = N'OfficeHoursDemo', @enabled = 0;

EXEC msdb.dbo.sp_add_schedule
     @schedule_name = N'Weekdays 8 to 6', @enabled = 0,
     @freq_type = 8, @freq_interval = 62, @freq_recurrence_factor = 1,
     @freq_subday_type = 4, @freq_subday_interval = 15,
     @active_start_time = 080000, @active_end_time = 180000;

EXEC msdb.dbo.sp_attach_schedule
     @job_name = N'OfficeHoursDemo', @schedule_name = N'Weekdays 8 to 6';

Read the settings back

Do not trust the schedule you meant to create. Read what SQL Server stored. This query joins the job to its schedule. Both enabled flags are 0, freq_type is 8, freq_interval is 62, and the interval is 15 minutes. The two times are 08:00 and 18:00.

SELECT j.enabled AS JobEnabled,
       s.enabled AS ScheduleEnabled,
       s.freq_type, s.freq_interval, s.freq_subday_interval,
       TIMEFROMPARTS(s.active_start_time / 10000, s.active_start_time / 100 % 100, 0, 0, 0) AS StartTime,
       TIMEFROMPARTS(s.active_end_time / 10000, s.active_end_time / 100 % 100, 0, 0, 0) AS EndTime
FROM msdb.dbo.sysjobs AS j
JOIN msdb.dbo.sysjobschedules AS js ON js.job_id = j.job_id
JOIN msdb.dbo.sysschedules AS s ON s.schedule_id = js.schedule_id
WHERE j.name = N'OfficeHoursDemo';

Decode the 62

The weekday mask adds up one number per day: Sunday 1, Monday 2, Tuesday 4, Wednesday 8, Thursday 16, Friday 32, Saturday 64. Monday to Friday is 2 + 4 + 8 + 16 + 32, which is 62. This query checks each day against the stored value with a bitwise AND, so you can see the weekend is out.

SELECT d.DayName,
       CASE WHEN s.freq_interval & d.Bit > 0 THEN 'Yes' ELSE 'No' END AS CanStart
FROM msdb.dbo.sysschedules AS s
CROSS JOIN (VALUES (1, 'Sunday'), (2, 'Monday'), (4, 'Tuesday'), (8, 'Wednesday'),
                   (16, 'Thursday'), (32, 'Friday'), (64, 'Saturday')) AS d (Bit, DayName)
WHERE s.name = N'Weekdays 8 to 6'
ORDER BY d.Bit;

What the window does not cover

A start window is not a stop button. If a run begins at 17:45 and takes two hours, it ends at 19:45. The schedule never kills it. If that matters, put a time check or a timeout inside the job.

Also check a few more things. The job may have other schedules that still fire at night. Agent uses the server’s clock, not yours. Holidays need their own rule, and so do manual starts. Run this to see every schedule on the job, and the server clock.

SELECT s.name AS ScheduleName, s.enabled
FROM msdb.dbo.sysjobs AS j
JOIN msdb.dbo.sysjobschedules AS js ON js.job_id = j.job_id
JOIN msdb.dbo.sysschedules AS s ON s.schedule_id = js.schedule_id
WHERE j.name = N'OfficeHoursDemo';

SELECT SYSDATETIMEOFFSET() AS ServerClock;

Your demo shows one schedule, and a clock value that is yours alone. On a real job, a second schedule named something like “Nightly” is the usual culprit. Daylight saving changes are worth a test too. Check the first and last start of the day around that date. Then remove the demo.

EXEC msdb.dbo.sp_delete_job @job_name = N'OfficeHoursDemo', @delete_unused_schedule = 1;

SELECT
    (SELECT COUNT(*) FROM msdb.dbo.sysjobs WHERE name = N'OfficeHoursDemo') AS JobsLeft,
    (SELECT COUNT(*) FROM msdb.dbo.sysschedules WHERE name = N'Weekdays 8 to 6') AS SchedulesLeft;

Give the job a bedtime, and keep it from waking anyone who cannot help.

A quiet schedule is not a stopped job, it is an allowed start window.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.