At the start of Daylight Saving Time (DST), which is this weekend in the UK, the clocks will move forward by one hour at 1am. Apart from the fact that we all lose an hour of our night’s sleep, what effect will this have on your scheduled jobs in SQL Server?
As we saw in my last blog post, we know that the SQL Server engine always carries on regardless of when the clocks change, it knows that this has happened, but nothing unusual occurs in the engine itself. However, SQL Server Agent, which runs scheduled tasks, is not the same, as this directly impacts how it works. When the clocks go back one hour, we know that the “first” hour runs as usual, then the agent goes to sleep/pauses during the “second” hour, then carries on.
But what about when an hour goes missing? I ran two tests last year to see how it works.
The first test was a scheduled job that ran every minute:
2021-03-28 00:57:00
2021-03-28 00:58:01
2021-03-28 00:59:00
2021-03-28 02:00:00
2021-03-28 02:01:00
2021-03-28 02:02:00
We can see that 1am never happens, but instead goes straight to 2am, which is what we would expect, really, because what else could have been the case?
But, what if we are running something less frequently, such as once between 1am and 2am. Would it just not run, or does it get handled?
The second test was an hourly backup job that ran every hour on the half-hour:
2021-03-27 23:30:00
2021-03-28 00:30:00
2021-03-28 02:00:00
2021-03-28 02:30:00
2021-03-28 03:30:00
We can see that the scheduled execution at 1:30am did not happen, as 1am to 2am did not exist. What SQL Server Agent did do was to run it at the first opportunity after the adjustment, so that execution was instead run at 2am. The schedule then continued back on track from 2:30am onwards.
So what does this mean for your servers?
If you have scheduled workloads that depend on running frequently and need to execute in a particular order, then you need to be aware that all jobs scheduled to run between 1am and 2am will not run at 1am, but will run once at 2am, before resuming their schedule. So jobs won’t mysteriously not run during that hour, but you will get one execution at 2am.
So check your schedules to see if this will impact you before the clocks change.