November 1, 2010 at 2:30 pm
george sibbald (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?
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.
Good to hear. Thanks!
--------------------------------------
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 1, 2010 at 2:31 pm
WayneS (11/1/2010)
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!!!)
Fortunately, all my jobs right now are daily jobs with a few weekly thrown in. I'll have to keep that in mind though.
--------------------------------------
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 2, 2010 at 4:56 am
the effects may be OS dependant, SQL seems to blithely carry on. I have a log shipping set up with jobs running every 15 mins and I enclose a doco with the actual logs output. Unfortunately the job history does not go back far enough though. This set up happens to be SQL2000 on windows 2000 (bless it) so is not exactly cutting edge, but is good in this case as it is probably worst case scenario as to what can happen.
Note how the timestamp on the logs was one hour before the date modified in explorer, but after the clocks went back they are in synch. Our clocks went back 2am on the 31st. I have a continuous set of logs though and had no problems.
---------------------------------------------------------------------
November 2, 2010 at 7:54 am
WayneS (11/1/2010)
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!!!)
And if it's scheduled to run once every 15 minutes, running during the "extra hour" every 15 minutes will be just fine, won't it?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 2, 2010 at 8:02 am
GSquared (11/2/2010)
WayneS (11/1/2010)
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!!!)
And if it's scheduled to run once every 15 minutes, running during the "extra hour" every 15 minutes will be just fine, won't it?
The problem is that it may go an hour without running.
Let's take Wayne's example and shift it forward by 1 minute.
At 1:46 AM, the job runs and when it completes, SQL Server Agent set the time at which it will run again to 2:01 AM.
At 2:00 AM, the time rolls back to 1:00 AM.
We now have to wait 61 minutes for the job to run again because it was scheduled to run again at 2:01 AM, even though the is job scheduled to run every 15 minutes.
This is the expected behavior based on what I have observed. Will it actually happen this way? I can't be 100% certain without actually witnessing 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]
November 2, 2010 at 8:03 am
I think Wayne's right. Basically, anything that's scheduled to run between 01:00 and 01:59 will execute at the first occurence of that time. No job schedules at all run between the "second" 01:00 and 01:59 unless they are manually scheduled after the change, so this is indeed a problem for jobs scheduled to run every 15 minutes etc. as the second 01:00 to 01:59 is effectively a ghost hour in which nothing runs, so e.g. for log backups etc, they wouldn't occur on the normal schedule.
November 2, 2010 at 8:46 am
Alvin Ramard (11/2/2010)
At 2:00 AM, the time rolls back to 1:00 AM.
Technically, the clock "advances" from 01:59:59.997 (however many decimal positions you want to use) to 01:00:00.000. 02:00 is only hit once.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
November 2, 2010 at 8:59 am
It should be worse the other way round to be honest. When you move into Daylight Saving Time, there is no 01:00 to 01:59, so I would assume that any job scheduled during this window simply will not start on that day, which could screw up a lot of daily batch jobs.
Having said that, I'm sure I would have come across it by now if it was the case - there's surprisingly little discussion or documentation I can find that says what the behaviour is in this scenario.
November 2, 2010 at 9:02 am
HowardW (11/2/2010)
It should be worse the other way round to be honest. When you move into Daylight Saving Time, there is no 01:00 to 01:59, so I would assume that any job scheduled during this window simply will not start on that day, which could screw up a lot of daily batch jobs.Having said that, I'm sure I would have come across it by now if it was the case - there's surprisingly little discussion or documentation I can find that says what the behaviour is in this scenario.
That's why I made this post. I've set up a number of jobs to run specifically when daylight savings ends to see what happens. I'll do the same when it begins again, but that's a number of months off.
--------------------------------------
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 2, 2010 at 9:07 am
Alvin Ramard (11/2/2010)
The problem is that it may go an hour without running.Let's take Wayne's example and shift it forward by 1 minute.
At 1:46 AM, the job runs and when it completes, SQL Server Agent set the time at which it will run again to 2:01 AM.
At 2:00 AM, the time rolls back to 1:00 AM.
We now have to wait 61 minutes for the job to run again because it was scheduled to run again at 2:01 AM, even though the is job scheduled to run every 15 minutes.
This is the expected behavior based on what I have observed. Will it actually happen this way? I can't be 100% certain without actually witnessing it.
I've had jobs running every 15 minutes across DaylightSavingTime changes and they've run every 15 minutes - no gaps. This was on SQL 2000 (other releases may be different). I don't remember doing anything special to achieve this (it was more than 8 years ago that I scripted the jobs in question, although they were still running every 15 minutes in 2008 and hadn't yet missed any runs due to DST). I also had jobs running every 1, 2, 3 or 5 minutes for a couple of years (which interval varied according to hardware configuration) and we'd have noticed if they had missed any runs (because customers would have been on the phone very loudly if an hours-worth of runs had ever been missed - especially as the change was in what was peak time for a good proportion of customers, small hours of the morning being high usage time for entertainment systems in hotels) - I don't remember doing anything special for those either, but again it was a long time ago. I think that maybe SQLServerAgent worked internally in UTC, not in local time, but anyway "every so many minutes" seemed to mean that even when the clock changed.
Tom
November 2, 2010 at 9:52 am
I'd agree with tom here, I've never noticed a problem, clocks going back or forward, other than negative run times recorded in SQL2000. The doco I enclosed with my previous post seems to support that.
---------------------------------------------------------------------
November 2, 2010 at 9:58 am
george sibbald (11/2/2010)
I'd agree with tom here, I've never noticed a problem, clocks going back or forward, other than negative run times recorded in SQL2000. The doco I enclosed with my previous post seems to support that.
I'm using the observations of the people that have posted here as my basis for not changing the times of the jobs. I just thought it'd be interesting to see what happens running some jobs that just record the time they run.
--------------------------------------
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 2, 2010 at 11:04 am
george sibbald (11/2/2010)
I'd agree with tom here, I've never noticed a problem, clocks going back or forward, other than negative run times recorded in SQL2000. The doco I enclosed with my previous post seems to support that.
Well, here's someone having problems when a job scheduled to run every 10 seconds stopped for an hour when changing from BST to GMT yesterday.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
November 2, 2010 at 11:37 am
Gawd, there's always one. I did say I had never had any problems, which is true :-), and also true for the vast majority
running every 10 seconds is unusual and not something you could do until SQL2008.
---------------------------------------------------------------------
November 2, 2010 at 11:39 am
WayneS (11/2/2010)
george sibbald (11/2/2010)
I'd agree with tom here, I've never noticed a problem, clocks going back or forward, other than negative run times recorded in SQL2000. The doco I enclosed with my previous post seems to support that.Well, here's someone having problems when a job scheduled to run every 10 seconds stopped for an hour when changing from BST to GMT yesterday.
Is there any way to schedule processes to run other than the Agent?
--------------------------------------
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 - 16 through 30 (of 52 total)
You must be logged in to reply to this topic. Login to reply