Re: Lost hour changing from daylight savings to standard time

Tech-Archive recommends: Speed Up your PC by fixing your registry

From: David Gugick (davidg-nospam_at_imceda.com)
Date: 11/01/04 

Date: Mon, 1 Nov 2004 17:16:41 -0500

Eric Tishkoff wrote:
> I was curious about what happens with time stamps (not the T-SQL
> datatype, but rather saving absolute time of insert/modification)
> during the changeover from daylight savings to standard time. Since
> we "fall back," the one o'clock hour gets repeated, once under
> daylight savings and a second time under standard time.
>
> As an experiment, I set up a table called TEMP_TimeChange
>
> CREATE TABLE [dbo].[TEMP_TimeChange] (
> [Id] [int] IDENTITY (1, 1) NOT NULL ,
> [ServerTime] [datetime] NULL ,
> [UTCTimeStamp] [datetime] NULL
> ) ON [PRIMARY]
> GO
>
> and job to run every five minutes starting at about 1:00 am (daylight
> savings time) on Sunday morning. The job simply inserted a new record
> into TEMP_TimeChange each time it ran.
>
> insert into TEMP_TimeChange (ServerTime) values (getdate())
>
>
> I expected to see the ServerTime cycle through the one o'clock hour
> twice like this:
>
> ServerTime UTCTimeStamp
> -------------------- --------------------
> Oct 31 2004 1:45AM Oct 31 2004 8:45AM
> Oct 31 2004 1:50AM Oct 31 2004 8:50AM
> Oct 31 2004 1:55AM Oct 31 2004 8:55AM
> Oct 31 2004 1:00AM Oct 31 2004 9:00AM
> Oct 31 2004 1:05AM Oct 31 2004 9:05AM
> Oct 31 2004 1:10AM Oct 31 2004 9:10AM
> ...
> Oct 31 2004 1:45AM Oct 31 2004 9:45AM
> Oct 31 2004 1:50AM Oct 31 2004 9:50AM
> Oct 31 2004 1:55AM Oct 31 2004 9:55AM
> Oct 31 2004 2:00AM Oct 31 2004 10:00AM
> Oct 31 2004 2:05AM Oct 31 2004 10:05AM
> Oct 31 2004 2:10AM Oct 31 2004 10:10AM
>
>
> Instead, the job did not run the second time through the one o'clock
> hour (as indicated by a one hour gap in the UTC time stamp). Here is
> the actual result:
>
> ServerTime UTCTimeStamp
> -------------------- --------------------
> Oct 31 2004 1:45AM Oct 31 2004 8:45AM
> Oct 31 2004 1:50AM Oct 31 2004 8:50AM
> Oct 31 2004 1:55AM Oct 31 2004 8:55AM
> Oct 31 2004 2:00AM Oct 31 2004 10:00AM
> Oct 31 2004 2:05AM Oct 31 2004 10:05AM
> Oct 31 2004 2:10AM Oct 31 2004 10:10AM
>
>
> The interesting/concerning thing is that the job did not run for an
> hour. The Windows task scheduler appears to have worked the same way
> as evidenced by the numerous pages our ops people received starting a
> few minutes after 1:00 am standard time. The concern is that critical
> jobs don't run. For example, We have a task that backs up the
> transaction log every 10 mins. It did not run at all during that
> hour. If we had experienced a severe database problem, at 1:55 am
> standard time, we would have lost all new data for the prior 65
> minutes, dating back to the last transatcion log backup that occurred
> at 1:50 am daylight savings time.
>
> To couch this as a question, is there MS documentation that discusses
> this issue and/or work arounds to ensure that critical jobs run as
> expected?
>
> et

The problem is that the job was logged as having run at 2am and
when the clock was returned to 1am, that time was before the next
scheduled run date (2:05pm).

To prevent this, you can update the job schedule for each job and each
schedule by running sp_update_jobschedule with the job name and schedule
name:

msdb..sp_update_jobschedule
@job_name = 'fghfghfdg',
@name = 'sched 1'

I suppose you could set this procedure to run twice a year at 2am and
dynamically query all the jobs and schedules from sysjobschedules in
msdb:

select * from msdb.dbo.sysjobschedules 

-- 
David Gugick
Imceda Software
www.imceda.com