Switching to Daylight savings time (+1 hour) issue

jeffgonnering

All of my monitored servers this morning have a "Connection Failed (Bad data)" and when I click on the log it states this:

"The supplied DateTime represents an invalid time. For example, when the clock is adjusted forward, any time in the period that is skipped is invalid. Parameter name: dateTime"

I am sure it has to do with switching off of standard time to daylight savings time yesterday. The error persists even when retrying the connection and restarting the base monitor service. Our monitoring is down for now. Thanks.

oderks

hmmm... If this is an issue I'd like to have the possible solution or preventive actions before March 27th.....

jeffgonnering

update: the connection failures (bad data) errors seem to have went away finally, not sure if this was due to my retry connection attempts or they just corrected themselves. I will watch to see if they stay up. Thanks.

Ben C

Hi Jeff

For reliability, SQL Monitor internally represents all times in UTC. This has several advantages:

- Times from different servers can easily be compared with each other (as long as your clocks are synchronized!)
- UTC does not have DST so there are no ambiguous times (when time repeats itself in the fall) or invalid times (when time jumps ahead in the spring)

Unfortunately, some sources of data only give us local times, and we have to convert these to UTC before storing them. While UTC to local conversion always succeeds, local to UTC conversion will fail if the data source provides us with an invalid time.

Most of the timestamped data we collect from SQL Server is subject to this conversion, including SQL Server error log events, job history, backup history, integrity check history, etc.

For example, one of my SQL Server instances has a job which it says ran at 2010-28-03 01:00:00 (GMT Standard Time). However, this time never existed, as the clocks actually went from 00:59:59 to 02:00:00 on that date.

When we encounter such data there are two approaches that we could take: either guess which of 00:59:59 and 02:00:00 SQL Server meant, or just drop the data. We currently just drop the data.

This should not result in a permanent error, but there was a bug in the SQL Server error log reader in 2.2 that prevented us from reading further error log lines. The bug has already been fixed for 2.3, but it should correct itself when the error log cycles. This might be what happened to you.

Does that make sense?

Thanks
Ben

jeffgonnering

yes, thank you for the explanation. The errors have gone away and the status of all my servers are back to the green check mark (monitoring, connected). Thanks again.

Ben C

Just to update on this issue: the behaviour has been changed for the next version of SQL Monitor (2.3). If SQL Server gives us an invalid local time (e.g. 01:13 when the clocks actually jumped from 00:59 to 02:00) we now find the next valid time (e.g. 02:00) and convert that to UTC instead. We decided that this behaviour was better than the alternative of just dropping the data.

oderks

FYI: had not problems at all last weekend with the clocks jumping an hour forward. Curious what happens with an hour jumping back at the end of the year, but we probably are running version 3 then?

priyasinha

Hi Oderks,

Dont worry about the clock jumping backward. This has been tested by us. Also, when the clock jumped back last year, SQL Monitor was being used by beta users also. So there should be no surprises

Regards,
Priya