Many countries follow the standard of Daylight Saving Time (DST), where twice each year, the clocks will move either forward or backwards by one hour to maximise the evening daylight in the spring, summer, and autumn months. For countries that follow this, there is a specific side-effect of this change, which is how it will affect computer systems. And from my particular area of interest - SQL Server.
We will deal in this post with the end of DST, where the clocks go back an hour. DST ending is handled automatically by Windows Server when at 02:00 the system moves back to 01:00 so that this one hour period will happen twice. We humans like this a lot, unless you work on a night shift, as an additional hour gets created whilst you sleep.
But what does this mean for SQL Server? To measure this, I ran a script in a query window that simply logged a record into a table every minute to record the current time and the time containing the time zone. I also created a SQL Agent job to do the same.
The query window output looked like this:
And this is what we saw from the SQL Agent job:
From this, we can see that SQL Server (specifically, the engine) ran just fine, and appeared oblivious to the change and just carried on running between 1am and 2am for the second time. But SQL Agent is a different matter. It effectively pauses at the 2am time for one hour whilst the world catches up, and then continues normally when the “second” 2am appears. The row IDs match in the two tables above, so you can see for row 230 that the engine continues for another hour but SQL Agent freezes at that point and only continues when the additional hour has passed.
Do we need to be worried about this? Not necessarily, it would only be where you have very specific dependencies and requirements to worry about, where a high-frequency job suddenly not running for an hour would cause you problems.
Things like:
How do you handle these cases? You need to create workarounds using other methods, which could be running T-SQL in a loop in a query window (although a risk if the instance/server restarts), Windows scheduled tasks, or a third-party scheduling tool that does not fall asleep for an hour.
For most of the environments that I have managed during my career, I don’t remember many cases where I needed to take protective measures, but it pays to do a quick review before DST starts or ends in your timezone, so that you don’t get any surprises. As a DBA I will always factor in Murphy's Law to these situations, as it would be me explaining why instead of losing five minutes of database changes we lost an hour's worth, as log backups were not running and the server chose the wrong time to explode. Manage the risk where you have these scenarios, and plan time in advance to ensure that you are ready for the change.