Tuesday, November 18, 2008

Daylight Saving Time and SQL Server Job

We encountered an issue with Daylight Savings Time in our hourly transaction log after the Nov 2, 2008 Daylight Saving Time change.

The transaction log is set to occur every hour, then copied over to standby servers for automatic restore

There was a transaction log for Nov 2, 2008 1:00 AM EDT (e.g. 200811020100.trn)

Next transaction log should be Nov 2, 2008 2:00 AM EDT (200811020200.trn), but since DST ended by one hour back at 2 am, 2 am EDT is now effectively 1 am EST
The OS time is now Nov 2, 2008 1:00 AM EST, and guess what, the transaction log file name is again 200811020100.trn, effectively overwrote the original 1 am log

How We Fixed It?
We ended up applying Differential backups since the last Full backup to bring the databases to the newest state, and then apply all the transaction logs after to catch up

How To Prevent It?
We change these servers to use UTC timestamp (namely GMT Reykjavik timezone in Windows), and increase current SQL Jobs by 5 hours ahead to run in the "correct" low traffic hours. Now we do not need to worry about the gap between 1 am & 3 am for Spring, and 1 am & 1 am for Fall.

This problem ONLY occurs for regular-interval jobs, not for specific-time driven jobs
And supposedly the OS will never reach 2 am but 1:59:59 am EST, and then 1:00:00 am EDT so that the job will not run twice (and that SQL will adjust any job which ran between 1 and 2 am EDT for you)

In short, it's shocking it's still happening, and I know why IT people love to use UTC now despite it's confusing for East Coast employees. Microsoft's How to prepare SQL Server 2005 and SQL Server 2000 for changes to daylight saving time in 2007 article.

No comments:

Post a Comment