Daylight Savings time change effect on jobs

Question

Daylight Savings time change effect on jobs

Bill Nye

Hall of Fame

Points: 3248
More actions
October 27, 2004 at 10:39 am

#89739

I have several jobs scheduled to run at 1am. What should happen when the time changes from 2am to 1am this upcoming Sunday?
I ran a quick test by setting a job to run at 1am and setting the clock ahead to Sunday 1:59am. The job did not run. So, does SQL Server somehow know not to re-run jobs when the time changes? Then again, I reset the clock to before 1am and it didn't run the job when it became 1am. All these time adjustments may have confused my pc, so I'm going to reboot and set the time to 12:59, let it run the job, and then see if it runs it again after the time changes at 2.
Any thoughts?
Thanks.
Bill

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Greg Charles SSC-Forever Points: 45403 More actions · Answer 1

There's another thread about this in the Backups forum: http://qa.sqlservercentral.com/forums/shwmessage.aspx?forumid=24&messageid=142470#bm142884

Greg

Bill Nye Hall of Fame Points: 3248 More actions · Answer 2

Thanks Greg.

After further testing, it appears that SQL Server sees that the job has already been run based on the date last run, and does not run it again.

(I set up a job that appends text to a file - it writes the first time, but not the second).

sswords SSCrazy Eights Points: 8207 More actions · Answer 3

Thanks Bill, you proved what I thought when I started the other thread. Just out of curiosity, have you set any kind of backup to run as a scheduled job to see what happens? I would assume the same result, but haven't been able to test here yet. Thanks.

My hovercraft is full of eels.

SQL Draggon Right there with Babe Points: 716 More actions · Answer 4

To keep this from becoming an issue with any scheduled job or process, I never schedule a job to start between 12:55AM. and 2:05 AM. This takes care of the switch from and to dayligh saving time. for the job that runs at 1:00 AM reschedule it to run at 12:55 AM, I don't think running the job 5 min earlier will make a difference.

Good luck.

Don't count what you do, do what counts.

SQL Draggon

Bill Nye Hall of Fame Points: 3248 More actions · Answer 5

No, I didn't try a backup. If I understand corrrectly, a main concern is the overwriting of the actual 1am tran log back up.

So, you could set up a job to rename it, ie:

Declare @cmd varchar(250),@oldfile varchar(150),@newfile varchar(50)

set @oldfile ='c:\log.trn'

set @newfile='log_pretimechange.trn'

set @cmd = 'ren ' + @oldFile + ' ' + @newfile

EXEC master..xp_cmdshell @cmd

Then, under the notifications tab, check the box for delete job on success - that way you ensure that this job won't run again after the time change.

By the way, if your server's local time settings are set to adjust for DST, I don't think you actually get to 2 - the clock seemed to jump from 1:59:59 to 1:00:00.