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.

4 thoughts on “How does daylight savings time affect disaster recovery?

  1. Do we know if there are any changes in Agent behavior for 2008 around DST – or will the expected behavior be the same?

  2. (From Paul – this comment was accidentally removed while fixing the comments problem on the site. Manually re-entering it)

    Pardon me if this sounds ignorant, but can’t you just set the server to use GMT time with no daylight saving (the GMT Casablanca, Monrovia, Reykjavik time zone in Windows). That way, your clock will not change the entire year.

    Also, while it may not be applicable for all applications, I also believe that all date time values stored in a database should be stored in GMT. It is the responsibility of the presentation layer to then convert the stored date time value into whatever time zone and format the uses chooses (or has their machine set to).

  3. Yes, I believe you can do that but I suspect the presentation layer logic may get tricky around the time that DST ends. Then again, the presentation layer is going to have to deal with DST ending in some way probably anyway…

Leave a Reply

Your email address will not be published. Required fields are marked *

Other articles

Some thoughts on courage

(This is also the Ponderings – editorial – in today’s SQLskills newsletter.) I want to start out this post by sincerely thanking everyone who emailed

Explore

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.