November 8, 2010 at 3:31 pm
Following up on my post, in case the details are interesting to anyone:
- Googling says that Windows is supposed to change from 1:59:59.999 to 1:00:00 on the DST switch; it should never hit 2:00:00.000 the first time.
- Mine clearly did: from the first run in the job history "Started: 2:00:00 AM"
- The fact that my daily job takes more than an hour is part of the equation: at the end of each job the Next Run Date is reset, and had this finished in less than an hour it would have reset to 2:00 am the next day and wouldn't have run twice.
- When 11/7 2:00 am rolled around the second time, I believe the Next Run date was still 11/7 2:00 am because the first run had not completed, so it ran again.
- Querying the SQL job history tables, the run_duration of the first job was "-954440872" (That value should have been 10506 which would be 1:05:06 (hh:mm:ss) This seems like a glitch due to time changing while the job was executing: "I ran for 65 minutes but it's only 5 minutes since the time I started so I'm confused"
My conclusions:
- Start jobs at 2:05 am, not 2:00... give the clock a few minutes to change
- Try to not have a job that starts before 2:00 and ends after.
- Understand that jobs may run twice between 1am and 2am on fall-back day (depending on their schedule)... and may not run at all on spring-forward day.
- Or schedule all jobs to run under UTC time as mentioned elsewhere.
November 9, 2010 at 11:17 am
It seems impractical to simply not run jobs between 1AM and 3AM Sunday mornings, but that would take care of both Fall Back and Spring Forward.
--------------------------------------
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
November 9, 2010 at 11:25 am
david.beav (11/8/2010)
Following up on my post, in case the details are interesting to anyone:- Googling says that Windows is supposed to change from 1:59:59.999 to 1:00:00 on the DST switch; it should never hit 2:00:00.000 the first time.
- Mine clearly did: from the first run in the job history "Started: 2:00:00 AM"
- The fact that my daily job takes more than an hour is part of the equation: at the end of each job the Next Run Date is reset, and had this finished in less than an hour it would have reset to 2:00 am the next day and wouldn't have run twice.
- When 11/7 2:00 am rolled around the second time, I believe the Next Run date was still 11/7 2:00 am because the first run had not completed, so it ran again.
- Querying the SQL job history tables, the run_duration of the first job was "-954440872" (That value should have been 10506 which would be 1:05:06 (hh:mm:ss) This seems like a glitch due to time changing while the job was executing: "I ran for 65 minutes but it's only 5 minutes since the time I started so I'm confused"
My conclusions:
- Start jobs at 2:05 am, not 2:00... give the clock a few minutes to change
- Try to not have a job that starts before 2:00 and ends after.
- Understand that jobs may run twice between 1am and 2am on fall-back day (depending on their schedule)... and may not run at all on spring-forward day.
- Or schedule all jobs to run under UTC time as mentioned elsewhere.
That's odd. What are the miliseconds on the job that started at 2:00 AM?
--------------------------------------
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
November 9, 2010 at 11:44 am
The log values only go to seconds, no mS
November 9, 2010 at 12:01 pm
david.beav (11/9/2010)
The log values only go to seconds, no mS
That's a shame. I'm wondering if it actually started off at 1:59:59:9998 or something, but reported it as 2:00:00.
--------------------------------------
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
November 4, 2012 at 10:32 am
Here's a fun thing, in case anyone happens upon this thread.
If your job is scheduled to run once a day at 2AM, it will indeed run twice if your server falls back from 2 to 1. I had thought it would immediately set the next run for the next day, but it didn't, it ran twice. I'm not entirely sure how to handle this.
--------------------------------------
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
November 4, 2012 at 4:44 pm
Stefan Krzywicki (11/4/2012)
Here's a fun thing, in case anyone happens upon this thread.If your job is scheduled to run once a day at 2AM, it will indeed run twice if your server falls back from 2 to 1. I had thought it would immediately set the next run for the next day, but it didn't, it ran twice. I'm not entirely sure how to handle this.
Jobs scheduled from 1 to 2 am will run twice when the server falls back from 2 to 1 when it switches from daylight saving time to standard time.
Jobs scheduled from 2 to 3 will be skipped when the server time goes forward from 2 to 3 when it switches from standard time to daylight saving time.
The easiest way to avoid this is to schedule jobs before 1 am or after 3 am, or don't schedule it to run on Sunday, since the time change (in the US) is always on Sunday morning.
November 4, 2012 at 4:57 pm
Michael Valentine Jones (11/4/2012)
Stefan Krzywicki (11/4/2012)
Here's a fun thing, in case anyone happens upon this thread.If your job is scheduled to run once a day at 2AM, it will indeed run twice if your server falls back from 2 to 1. I had thought it would immediately set the next run for the next day, but it didn't, it ran twice. I'm not entirely sure how to handle this.
Jobs scheduled from 1 to 2 am will run twice when the server falls back from 2 to 1 when it switches from daylight saving time to standard time.
Jobs scheduled from 2 to 3 will be skipped when the server time goes forward from 2 to 3 when it switches from standard time to daylight saving time.
The easiest way to avoid this is to schedule jobs before 1 am or after 3 am, or don't schedule it to run on Sunday, since the time change (in the US) is always on Sunday morning.
I don't think that's right. I think it only happens if the time change happens as the job starts. Any other time, SQL Server sets the next run when the job starts. I had a job run at 1:30 that didn't run a second time.
This reminds me though, other research I've been doing shows that Next Run is the current schedule until the job is finished. So maybe SQL Server doesn't actually set the "Next Run" until the job is finished. That would mean that the only time it'd be a problem is if the job is such that it would start at or before the change and end within an hour. I'll have to do some more research.
--------------------------------------
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 8 posts - 46 through 52 (of 52 total)
You must be logged in to reply to this topic. Login to reply