How does daylight savings time affect disaster recovery?

It’s common knowledge that SQL Server copes with daylight savings time (DST) correctly so why should you care?

Well, it’s not so common knowledge that at the end of DST when the clocks go back an hour (always at 02:00 in the U.S.), SQL Agent essentially pauses for an hour (in at least SQL Server 2000 onward). This means that if you have a job that’s doing something every 15 minutes, there will be a gap of 75 minutes between the job execution at 01:45 and the job execution at 02:00. This happens because at 02:00, the time is set back to 01:00 but the next run time of all the jobs remains the same – so your job cannot execute until it’s next scheduled time of 02:00. So, in the northern hemisphere every Fall, and in the southern hemisphere every Spring, you lose an hour’s worth of SQL Agent jobs. Still, why should you care?

Well, it depends what the jobs are that get delayed by an hour. If you have a job that takes a log backup every 15 mins then on the day DST ends, there’s actually a gap of 75 minutes between log backups. If you have a Service Level Agreement (SLA) that limits the maximum amount of lost work to 15 minutes in the event of a disaster, then for those 75 minutes you’re exposed to potentially not being able to meet that SLA!

That could be a pretty big deal, especially if something goes wrong during that hour (no more or less likely than something going wrong at any other time, but still possible). In that case, you need to come up with an alternative solution. A couple of ways to get around the problem I can think of:

  • Have someone stay up late during that hour and take manual log backups.
  • Switch over to database mirroring, which continually streams the log to the redundant server and so isn’t affected the DST issue.

Both of these are viable solutions but I think the best one is to create a SQL Agent job that runs at 01:59 and creates extra backup jobs to run at 01:00, 01:15, 01:30, and 01:45. I don’t see why this shouldn’t be possible. At 10:36 this morning I created a simple agent job to print the date to a file and set it to execute at 09:40 – in the past. I then set my system time back one hour and the job executed perfectly. The only downside of this solution is that you need to create and schedule the extra jobs using the T-SQL Agent SPs embedded in job steps for your 01:59 job – tedious but not hard. Maybe someone could send me a script and I’ll blog it as a follow-on?

So with DST coming to an end on November 4th this is definitely something for you to be aware of even if you don’t want to go to the trouble of coping with the extra hour’s exposure. As an aside – the dates when DST starts and ends changed this year. KB article 931975 discusses which parts of SQL Server aren’t aware of the changed dates and what you can do about it.

Other articles

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.