how to handle daylights savings time???
-
I have been given a list by my boss to go through 73 sql servers and evaluate for jobs that may conflict with Daylights savings time.
I have a simple query started for sysjobs and sysjobschedules...however I am stuck on the logic to filter the jobs down.
Mainly I am trying to figure out how to say just jobs that will run on sunday and excecute between 01:00 and 2:00.
based on what I have seen in the sysjobschedules table I am unable to figure out how to do it. I am thinking I would need to only look for jobs that run on an hourly basis or jobs that are scheduled for a specific time...
Would I need anything else?
if anyone has any expereice with Daylights savings time would you please give me your advice to handle the above issue???
Many thanks,
Leeland
-
I have heard people call it "Daylight Savings Time" before... never heard them call it "Daylights Savings Time"...
The correct reference is "Daylight Saving Time"... as in there is more daylight during the day, so you are "saving" daylight time.
Now, for your answer, I will leave that for someone who actually has experience in that area. But I am curious as to why the manager thinks this is important. The boxes are probably running a modern enough OS to actually change the time for you... a Microsoft Operating System, a Microsoft Database...
I wonder what the problem might be?
Thank-you,
David Russell
Any Cloud, Any Database, Oracle since 1982 -
I didn't even realize I spelled it incorrectly...thanks for pointing that out....
The jobs are running on a schedule so any job that kicks off during the window of 1 to 2 am will run again when the time gets set back an hour.
Not a big deal if it is a backup or something like that...but some of the stuff that is run is only supposed to post once otherwise it gets duplicated and causes problems...
-
Lee,
I never faced this issue... [Rtaher not though on it
]. But what do u think on the below stretagy?1. Leave the time on Windows Time Server. All 2K3/2K boxes are in sync with the time servers using W32Time.
2. When the time changed [That is a one time incident when server changes the time ].. disable all jobs for next 1 hour. So that they won't be refired in that 1 hour window.
3. It s better if u use some automation to disable / enable all jobs in a box in a shot. Otherwise u may have to spend some manual effort
Let me know if it helps you.
Regards
Utsab
-
"disable all jobs for next 1 hour. So that they won't be refired in that 1 hour window."....would it not be simpler to just disable the SQLAgent for 1 hour???...at what would be ther 1st time 1am is hit...and then when the 1st 2am is hit, reset the clock back to 1am, and then re-enable the SQLAgent?
-
SQL Agent should handle almost everything automatically.
Consider:
1) You have a job scheduled to run hourly, at 5 minutes past the hour.
At 01:05, it runs normally and on completion sets the "Next Run" time to be 02:05. At 02:00, the clocks go back to 01:00. At 01:05, the job DOES NOT RUN AGAIN, as its Next Run time is already set to 02:05. All that happens is that the job stays idle for an extra hour (which may or may not be a problem)
2) You have a job scheduled to run hourly at 5 minutes to the hour.
At 01:55, it runs normally and on completion sets the "Next Run" time to be 02:55. At 02:00, the clocks go back to 01:00. At 01:55, the job DOES NOT RUN AGAIN as its Next Run time is already set to 02:55. Again, the job stays idle for an extra hour.
3) You have a job scheduled to run every 10 mins, that takes 7 minutes to run. At 01:55, it starts running. At 02:00, the job is still running, but the clocks go back to 01:00. At 01:02, the job completes, but the Next Run time REMAINS AT 01:55. i.e. SQL Agent recognizes that the system clock has moved backwards to before the last start time, so cannot calculate the next run time accurately.
This scenario is likely to be the most problematic, as a frequent job will now remain idle for nearly 1 hour.
-
Just leave the server on gmt... the accounts department love interesting reports like 'bob withdrew $1000 at 01:10 but he had never deposited money until 01:30'... or audited records where integer based invoice numbers jump all over the shop on a time sequence!
-
-
Excellent point made...I will ask my boss as to his reasoning. In the past everytime something like this he has always had a good reason. I will mention your senerios to see what his view is on the topic. My previous experience has always been that he has a good reason for asking me to do stuff like this. That does not mean that he is right, but I wouldn't bet against him.
It doesn't help that he is on vacation the rest of the week so even if I had a good argument for not doing this I can't speak to him...so for this daylights saving time I will be checking the job schedules
-
Ye... That is better. Don't know how it never clicked in my mind
-
I don't think you need to be there to check on the jobs. If it makes you feel any better, then you can simulate the scenario by changing the time (testing server PLS). And see how the jobs are not refired for one hour. If it makes you feel any better, you can always pop in Sunday morning and see if everything ran OK. But I wouldn't waste much sleep on that one if I were you
. -
Good answer Philip Yale!!!
Like I said... Modern OS, Modern DB...
That frequent job is something to be concerned with perhaps.
I'm with Ninja... not going to lose any sleep
Thank-you,
David Russell
Any Cloud, Any Database, Oracle since 1982 -
For the sake of conversation...I will pose the question to my boss when he returns on Monday...and give him the examples listed as an arguement (not literally)...
He usually has some very insightful reasons for things so it should be interesting. I will reply when I got something...
-
I think it was a smart thing on his part to think about the hour change and the possible job failures (especially if he's not a programmer / dba). However in this case I think his fears were not founded (most likely that MS had thaught and designed for this scenario). But I'm sure he would have figured it out if he could have set up a test environement for himself.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply