| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2006-01-11 : 08:13:05
|
| Bryan writes "I'm in Indiana where we have never observed DST, but starting in April 2006 we will start observing it. My question is what happens to scheduled jobs in SQL Server 2000 that are scheduled during the hour that is skipped (in the spring)?For example, if a job is scheduled to run at 2:30am will it be skipped and not run at all when the time skips from 2:00 to 3:00?Thanks" |
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2006-01-11 : 08:58:44
|
| You could experiment? But my guess (and a small one at that) would be no. I think the launching of the schedule objects occurs at the changeover of each minute....and that it looks for jobs to be scheduled in a particular 'minute' slot....and not for all jobs 'not executed since the last minute boundary was crossed'. |
 |
|
|
jeroena
Yak Posting Veteran
76 Posts |
Posted - 2006-07-27 : 09:19:15
|
Or even worse: does it run the job twice when at three o'clock, the clock is switched back to two o'clock?Does anyone has a conclusive answer for this? We are running a DWH job at 02:00  Jeroen |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-07-27 : 09:29:25
|
| My recollection is that a job runs when it is overdue (e.g. you stop the server for 12 hours, restart it, and all jobs run!) and then stores the next anniversary of the run time as the next-time-to-start. So I reckon its somewhat immune to both forward & backward clock changes.However I worry about jobs that transfer data based on creation/modified date/time stored in the record - "All data more recent than the most recent record on the remote" or "everything in the last hour" - putting the clocks back an hour wrecks that because new records get dated earlier than the most-recently-transferred record.Web-services too - transferring "all data new since the last run"Personally I think it is better for SQL boxes to NOT observe DSTBut then what about minor adjustments to the system clock time?Kristen |
|
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2006-07-27 : 09:47:23
|
| question is... what does the job do? whatever you need to adjust, it should be captured at the tsql levelor if you are really particular, in my previous employment we had a synchronization application that propagates to the servers automatically--------------------keeping it simple... |
|
|
|
jeroena
Yak Posting Veteran
76 Posts |
Posted - 2006-07-27 : 10:01:25
|
| Thnx! Hehe... does not sound really conclusive Kristen, "My recollection is...". I do agree methods with datetime stamps are risky. In our DWH, we take the data of the last two months and than determine increments of those records using the key's and the timestamp column against our staging tables.Jen, I really think the first question should be: "Will my job run or not", because if it doesn't run, there's no TSQL to run at all :)Jeroen |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-07-27 : 10:07:33
|
| "does not sound really conclusive Kristen, "My recollection is..."."Yup, you are quite right, but I don't have the time right now to double check I'm afraid. But if you get that answer from a number of sources the degree-of-confidence might increase!Kristen |
|
|
|
jeroena
Yak Posting Veteran
76 Posts |
Posted - 2006-07-27 : 10:11:04
|
| Okay, I'll do some testing on uhm, my collegue's box and let you know...Jeroen |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-07-27 : 10:17:03
|
"my collegue's box"I can visualise his screams as his days work evaporates  |
|
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2006-07-27 : 22:14:38
|
| your job will run, sql schedule is based on the system time if I analyzed correctly, the concern is what will happen if the job runs on the said time but assuming it should not because of the adjustment?so, you may need to look into the job tasks, if you are retrieving data based on a timestamp, then you may need to identify which is to be taken for est and dst, how does sql server know which time stamp is correct?if retrieval, you can always modify, but if you're updating or saving or transferring records, that's a bigger problemok... babbling...stick to my signature...--------------------keeping it simple... |
|
|
|
|
Scheduled Jobs and Daylight Saving Time