Daylight Savings Time

Question

Post reply

Daylight Savings Time

Sioban Krzywicki

One Orange Chip

Points: 27770
More actions
November 1, 2010 at 11:04 am

#228581

Does the SQL Server Agent take daylight savings time into account? Or am I going to wake up Sunday morning to find that any jobs scheduled to run between 1 and 2 AM this Sunday have run twice?
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams

Viewing 15 posts - 1 through 15 (of 52 total)

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

george sibbald SSC Guru Points: 104210 More actions · Answer 1

I'd use dbcc timewarp on that, especially as its already monday. 🙂

...er to explain, we had daylight savings weekend just gone.

---------------------------------------------------------------------

Alvin Ramard SSC-Forever Points: 41190 More actions · Answer 2

Stefan Krzywicki (11/1/2010)
Does the SQL Server Agent take daylight savings time into account? Or am I going to wake up Sunday morning to find that any jobs scheduled to run between 1 and 2 AM this Sunday have run twice?

No to both question.

As far as 2nd question is concerned, from what I have observed, when a job completes, or the Agent starts up, the Agent determines the next time the job should run. For a daily job, this will have the next day's date. This will prevent the job from running twice.

Alvin Ramard
Memphis PASS Chapter[/url]

All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

george sibbald SSC Guru Points: 104210 More actions · Answer 3

for what its worth, mine ran fine as scheduled. I have seen some weird negative run times reported, but only under SQL2000.

---------------------------------------------------------------------

Sioban Krzywicki One Orange Chip Points: 27770 More actions · Answer 4

Thank you both for the helpful information. Now I can go on vacation this weekend without worrying. : -)

George, where are you that you've already had daylight savings?

--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams

Alvin Ramard SSC-Forever Points: 41190 More actions · Answer 5

Stefan Krzywicki (11/1/2010)
Thank you both for the helpful information. Now I can go on vacation this weekend without worrying. : -)
George, where are you that you've already had daylight savings?

Know how to be extra sure? Don't schedule jobs to start during that period. 🙂

Alvin Ramard
Memphis PASS Chapter[/url]

All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

TomThomson SSC Guru Points: 104773 More actions · Answer 6

There could be a similar interesting question about the other daylight saving time shift shift (in 6 months time). Will jobs scheduled to start between 0200 and 0300 fail to run because 0200 never happens? Alvin's workaround can avoid that question too.

Tom

Sioban Krzywicki One Orange Chip Points: 27770 More actions · Answer 7

Alvin Ramard (11/1/2010)
Stefan Krzywicki (11/1/2010)
Thank you both for the helpful information. Now I can go on vacation this weekend without worrying. : -)
George, where are you that you've already had daylight savings?
Know how to be extra sure? Don't schedule jobs to start during that period. 🙂

That had been my plan, go through all my regular jobs and reschedule anything that falls then for one week.

--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams

jcrawf02 SSC-Insane Points: 24198 More actions · Answer 8

Tom.Thomson (11/1/2010)
There could be a similar interesting question about the other daylight saving time shift shift (in 6 months time). Will jobs scheduled to start between 0200 and 0300 fail to run because 0200 never happens? Alvin's workaround can avoid that question too.

Technically, it's an UN-workaround

---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]

"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."

Sioban Krzywicki One Orange Chip Points: 27770 More actions · Answer 9

jcrawf02 (11/1/2010)
Tom.Thomson (11/1/2010)
There could be a similar interesting question about the other daylight saving time shift shift (in 6 months time). Will jobs scheduled to start between 0200 and 0300 fail to run because 0200 never happens? Alvin's workaround can avoid that question too.
Technically, it's an UN-workaround

I know it'll work in any country that uses daylight savings, but I don't think the UN has anything to do with it...

--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams

jcrawf02 SSC-Insane Points: 24198 More actions · Answer 10

Stefan Krzywicki (11/1/2010)
jcrawf02 (11/1/2010)
Tom.Thomson (11/1/2010)
There could be a similar interesting question about the other daylight saving time shift shift (in 6 months time). Will jobs scheduled to start between 0200 and 0300 fail to run because 0200 never happens? Alvin's workaround can avoid that question too.
Technically, it's an UN-workaround
I know it'll work in any country that uses daylight savings, but I don't think the UN has anything to do with it...

That's what I thought too, but then the peace-keeping forces showed up, so I shut my trap...

---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]

"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."

TomThomson SSC Guru Points: 104773 More actions · Answer 11

Stefan Krzywicki (11/1/2010)
jcrawf02 (11/1/2010)
Tom.Thomson (11/1/2010)
There could be a similar interesting question about the other daylight saving time shift shift (in 6 months time). Will jobs scheduled to start between 0200 and 0300 fail to run because 0200 never happens? Alvin's workaround can avoid that question too.
Technically, it's an UN-workaround
I know it'll work in any country that uses daylight savings, but I don't think the UN has anything to do with it...

Yeah, if they had it wouldn't work anywhere - it would be nearly as bad as letting the UK civil service bureaucracy into the act. :w00t:

Tom

george sibbald SSC Guru Points: 104210 More actions · Answer 12

Stefan Krzywicki (11/1/2010)
Thank you both for the helpful information. Now I can go on vacation this weekend without worrying. : -)
George, where are you that you've already had daylight savings?

the UK. I've been thru a few servers, no adverse effects anywhere.

a job that was still running when the clocks went back would not kick off again anyway as it was already running, and a job that had completed would have a later next run date.

In the good old mainframe days we used to quiesce the box for the hour, but that was then.

---------------------------------------------------------------------

WayneS SSC Guru Points: 95460 More actions · Answer 13

But... you could have a different problem.

If your job is scheduled to run, say, every 15 minutes. When it runs @ 1:45 (the first time), it gets set to run next at 2:00. But at 1:59:59, the clock rolls over to 1:00:00, and it won't run again until 2:00. So you'll go an extra hour before it fires up again. (FYI, this would include log shipping jobs that back up the database... make sure you can handle going an extra hour here!!!)

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

LutzM SSC Guru Points: 107049 More actions · Answer 14

I need to talk to our db admins...

We changed to DST last weekend, too (but not at 1am, time changed from 3am to 2am on Sunday. Location: Germany).

Since we did run production starting Monday as usual, they did handle it pretty well, obviously.

But speaking of transaction logs (every 10min), I'd have to reconfirm.

It might be real "fun" to do a point in time recovery to Sunday, 2:30am... :pinch:

Anybody out there ever tried it?

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]