There was a bit of chat today on the SQL Community slack about UTC and Daylight Saving Time. It reminded me I have a post in draft somewhere about the complications of dealing with timezones in SQL that I should get around to finishing.
It also reminded me of a fact I found in an old Paul Randall post from 2007 that not a lot of people seem to know so I thought it was worth sharing again.
I discovered it when I was working on a kind of hand-cranked version of log shipping with a twist for SQL Server. Before you ask me why… don’t! I promise it was for a good reason. At least it seemed like a good idea at the time…
Anyway, I was aware that I needed to work out what I was going to do when the clocks go back in October of each year.
I decided to try and find out what the out of the box log shipping does to cater for this. I started with Google and came across this 2007 article by Paul Randall:
How does daylight savings time affect disaster recovery? – Paul S. Randal
Apparently SQL Server Agent pauses for an hour after the clocks go back. In the UK the clocks go back at 2 AM on the last Sunday in October. So all the times from 1 AM to 2 AM effectively happen twice. SQL Server Agent will run jobs from 1 AM to 2 AM as usual, then when at 2 AM the clocks go back to 1 AM, SQL Agent will pause for an hour (i.e. no new jobs will be started) and only resume the second time it gets to 2 AM.
Sounds a bit crazy in some ways, but I tend to believe most things Paul Randall says and it does seem to have been proved since in practice.
It means that you don’t get a confusing sequence of times in your job history and other events that don’t really make sense, and it also means that jobs won’t happen twice e.g. if you had an index rebuild in that time.
It could have issues for DR though, imagine that this period is actually an active one for your application and that you have an RPO (recovery point objective) of a maximum of (let’s say) 5 minutes data loss. During that hour, no LOG backups are being taken. Suddenly you’re vulnerable to not being able to meet your DR service agreement. Could be something to watch out for.
For me though (at the time I learnt this) it was great as I didn’t need to worry about coding a workaround in my log restore code!
Hi Matthew,
That makes sense for the ‘fall back’ switch but how does the Agent handle the ‘spring forward’ change when 1AM to 2AM does not exist (i.e.at 1AM the system time is switched to 2AM)?
I assume that any actions scheduled in this ‘non-existent’ slot will be run as soon as the clock changes but that could result in collisions.
Hi David,
That’s a really good question! I suspect that in that scenario, if you have a job scheduled for 1:30 AM then it simply will not run, but not 100% sure – would need to test. It wouldn’t be a problem for the DR scenario as there still would be a log backup withe same regularity in real time. But if you had other scheduled mainenance I can see that being a problem.
I’ll look into it. If you find out the answer first let me know!
So I tested this and my guess was wrong. The job scheduled for 01:30 does run. It kicks in as soon as the clocks go forward (i.e. at what is now 2AM). So in theory that means we don’t have to worry about the clocks going in forward, though in practice that could mean that a bunch of stuff gets run together as soon as it happens.
Thanks for checking this.It is good to know this.
I guess this behavior is preferable to jobs not running at all but, as you say, there could be overlapping workloads just as the clocks change. That could could be an issue where sequencing is important.
BTW it is ‘Daylight Saving’ not ‘Daylight Savings’
Lol! Good spot, thanks