SQL Jobs – Daylight Savings Risks

For those of us not lucky enough to live in Arizona (where daylight savings is ignored and clocks stay the same all year-round), we need to consider what time our SQL jobs are run. What happens with jobs that are scheduled for 2:30am when we spring the clocks ahead? They are skipped! What happens to jobs scheduled for 1:30am when the clocks fall behind? They are run twice.

Think through the possible ramifications of some of your jobs running twice or not at all. You might want to seek out these jobs ahead of time and account for it when we change the clocks. This is not otherwise accounted for within SQL Server.

If you want a script to quickly find those jobs, here is the one that I use.

/*
--  Return jobs that run between 1am and 3am.  These jobs run the risk of being skipped
--  or run twice, depending on what time of year it is.  This will return jobs to be
--  watched.
*/
 
USE [msdb]
Go
 
SELECT DISTINCT
    [Job Name]
    , [Time Run]
    , [Schedule Name]
    , CASE
        WHEN q.[Active_start_time] <= 20000 and q.[Active_start_time] >= 10000 THEN 'Fall - May Run Twice'
        WHEN q.[Active_start_time] <= 30000 and q.[Active_start_time] >= 20000 THEN 'Spring - May Be Skipped'
    END [Desc]
FROM (
 
        SELECT  sj.name AS [Job Name],
                ss.name as [Schedule Name],
                ss.active_start_time,
                 left(stuff((stuff((replicate('0', 6 - len(ss.active_start_time)))+ convert(varchar(6),ss.active_start_time),3,0,':')),6,0,':'),8) [Time Run]
        FROM sysschedules ss
            INNER JOIN dbo.sysjobschedules sjs ON sjs.schedule_id = ss.schedule_id
            INNER JOIN dbo.sysjobs sj ON sj.job_id = sjs.job_id
        WHERE
            ss.freq_type NOT IN (0,1,64,128) /*unused, one time, sql agent starts, computer idle */
            AND ss.active_start_time >= 10000 and ss.active_start_time <= 30000
            --AND (ss.freq_type <> 4 OR ss.freq_interval = 1) -- This should only pick up Sunday jobs - can't prove it works yet.
 
) q