Posts Tagged ‘Leap Second’

Leap Second coming this June 2015, will it affect SQL Server?

June 13, 2015 Leave a comment

And it’s not big and concerning like Y2K !

Every few years a Leap Second needs to be added to the Coordinated Universal Time (UTC), so that we keep the time of the day as close to the mean solar time as is possible. On a Leap Second day, an extra second ss is added to our clocks at midnight. This is done to match the time shown by the official atomic clocks. Last time it happened in 2012.

So, officially the time actually goes from 23:59:59 to 23:59:60 instead of directly to 00:00:00.

–> So, on June 30 2015 midnight the Leap Second aware and Windows clock will tick like this:

– Leap Second clock : 23:59:59 , 23:59:60 , 00:00:00 , 00:00:01

– Windows clock       : 23:59:59 ,                    00:00:00 , 00:00:01

–> Now question comes will it affect SQL Server and the time it stores?

No, because SQL Server gets time from Windows and Windows does not honor Leap Seconds. Windows just skips the 23:59:60 time and thus its clock becomes 1 second faster. Which slowly gets synced-up with the correct time using Network Time Protocol (NTP). That’s why you can also not set or store this time on Windows and in SQL Server.

–> Impact on SQL Azure (SQL Database):

No, Azure service has been designed to be resilient to clock discrepancies across Microsoft’s numerous infrastructure components and regions. Azure has proven application compatibility for handling Leap Seconds given it uses the Windows time-synchronization protocol, which is used by all Windows systems including the Windows client OS, Windows Server, Windows Phone, and Hyper-V.

The KB Article 909614 also explains how Window treats Leap Second:

“The Windows Time service that is working as a Network Time Protocol (NTP) client does not indicate the value of the Leap Indicator when the Windows Time service receives a packet that includes a leap second. (The Leap Indicator indicates whether an impending leap second is to be inserted or deleted in the last minute of the current day.) Therefore, after the leap second occurs, the NTP client that is running Windows Time service is one second faster than the actual time. This time difference is resolved at the next time synchronization.”

–> Check here on MSDN Blogs for more info:

1. What is Leap Second:

2. Leap Second and Windows:

3. Impact on Microsoft products:

Categories: Windows Tags: