The job was due at 02:00 and never ran: troubleshooting SQL Agent schedules

#Symptom

The job has no history entry for the expected time, or it ran at the wrong time. No failure is recorded, because nothing ran.

#Step 1: is everything enabled?

DECLARE @job sysname = N'Backup - FULL - UserDBs';

SELECT j.name, j.enabled AS job_enabled,
       s.name AS schedule_name, s.enabled AS schedule_enabled,
       s.active_start_date, s.active_end_date, s.active_start_time,
       js.next_run_date, js.next_run_time
FROM msdb.dbo.sysjobs j
LEFT JOIN msdb.dbo.sysjobschedules js ON js.job_id = j.job_id
LEFT JOIN msdb.dbo.sysschedules s ON s.schedule_id = js.schedule_id
WHERE j.name = @job;

#Step 2: was Agent running?

SELECT session_id, agent_start_date FROM msdb.dbo.syssessions ORDER BY session_id DESC;

If Agent started at 02:10, the 02:00 run was missed. Agent does not catch up on missed runs after a restart. Look at the Windows System log for service stops, patching or a reboot.

#Step 3: was the previous run still going?

Agent never starts a job while it is still running. The scheduled start is skipped silently.

SELECT TOP (10) msdb.dbo.agent_datetime(run_date, run_time) AS started,
       run_duration, run_status
FROM msdb.dbo.sysjobhistory h
JOIN msdb.dbo.sysjobs j ON j.job_id = h.job_id
WHERE j.name = N'Backup - FULL - UserDBs' AND h.step_id = 0
ORDER BY h.instance_id DESC;

If the previous run lasted longer than the interval, that explains the gap. (agent_datetime is an undocumented msdb helper. It works, but don't build critical code on it.)

#Step 4: daylight saving and time zone

Schedules use the server's local time. On the night the clocks change:

• Spring forward: a job scheduled at 02:30 may not run (that time doesn't exist).
• Fall back: a job at 01:30 may run twice.

Also check whether someone changed the server time zone: SELECT SYSDATETIMEOFFSET();.

#Step 5: frequency settings that confuse

#Step 6: the job ran but under another name or on another node

On a failover cluster or AG, check which node was active. Jobs exist on each replica. A job that only runs on the primary will look like it "didn't run" when you read history on a secondary.

#Step 7: force Agent to recalculate

After fixing a schedule, check next_run_date. If it's still stale, disable and re-enable the schedule, or restart Agent in a quiet period.

Tip

Add a "missed run" check to monitoring: any critical job whose last successful run is older than its interval plus a margin. It catches every cause above.

#Prevent it next time

• Avoid the 01:00–03:00 window for critical jobs in time zones with daylight saving.
• Size intervals to the job's real duration with headroom.
• Monitor "last success age" per critical job, not only failures.