November 2, 2010 at 11:53 am
Stefan Krzywicki (11/2/2010)
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?
There's the Windows Scheduler. I'm pretty sure there's 3rd party tools.
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 11:54 am
george sibbald (11/2/2010)
Gawd, there's always one. I did say I had never had any problems, which is true :-), and also true for the vast majorityrunning every 10 seconds is unusual and not something you could do until SQL2008.
But you could do one every minute. And as I pointed out, if the frequency is less than hourly, there will be an hour skip between the last time ran prior to 01:59:59.997 and the first time run on/after 02:00:00.000.
Now, how would things work when jumping from 01:59:59.997 to 03:00:00.000 with jobs scheduled to run between 02:00:00.000 and 02:59:59.997? I can see the agent spotting that it's "overdue" and kicking it off, but I think that the agent has to be running at the time of the scheduled start in order to kick it off - which might mean that these jobs stop running at all.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
November 2, 2010 at 11:58 am
Alvin Ramard (11/2/2010)
Stefan Krzywicki (11/2/2010)
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?
There's the Windows Scheduler. I'm pretty sure there's 3rd party tools.
The problem is, of course, that these will all rely on the system time to run. I'm thinking you might need to write a flag and have the process check the flag to see if it should run even when it thinks it already ran for that time. I don't think there's any way for that to work in SQL Server Agent, but third party tools might allow for 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
November 2, 2010 at 12:08 pm
You could have a procedure that runs on start-up and runs in a loop checking the execution of the jobs. I wouldn't recommend it, but it's an option.
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 12:16 pm
Alvin Ramard (11/2/2010)
You could have a procedure that runs on start-up and runs in a loop checking the execution of the jobs. I wouldn't recommend it, but it's an option.
If I were going to do anything special for either clock change, I'd only run it at those times of year anyway. I just checked and fortunately there's nothing here that will be harmed by not running for an hour.
--------------------------------------
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 12:20 pm
Stefan Krzywicki (11/2/2010)
Alvin Ramard (11/2/2010)
You could have a procedure that runs on start-up and runs in a loop checking the execution of the jobs. I wouldn't recommend it, but it's an option.If I were going to do anything special for either clock change, I'd only run it at those times of year anyway. I just checked and fortunately there's nothing here that will be harmed by not running for an hour.
Good point. Schedule it to only run on those days and start at 1:59 AM. It could have instructions to run till 2:01 AM or 3:01 AM, depending on whether time is springing ahead or falling back.
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 1:49 pm
I'm thinking I should just setup all my servers to use GMT no DST from now on...that's not way to not have to deal w/ any of it 🙂
November 2, 2010 at 1:53 pm
Jon.Morisi (11/2/2010)
I'm thinking I should just setup all my servers to use GMT no DST from now on...that's not way to not have to deal w/ any of it 🙂
Sure, but you're likely to confuse the hell out of your users. I don't even want to think about the nonsense you'd have to go through to properly insert current date and/or time.
--------------------------------------
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 2:45 pm
I must say I'm suddenly quite happy to be living in AZ. We don't mess with that old farmer's wit of a time system. Savings time... bah! It's not like you put it in a bank!
AZ and IN, the only states that DON'T bother with this silly PST/PDT stuff. 😛
(I was wondering why I hadn't dealt with it before as this thread went on... then I remembered. :w00t: )
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
November 2, 2010 at 3:40 pm
Craig Farrell (11/2/2010)
I must say I'm suddenly quite happy to be living in AZ. We don't mess with that old farmer's wit of a time system. Savings time... bah! It's not like you put it in a bank!AZ and IN, the only states that DON'T bother with this silly PST/PDT stuff. 😛
(I was wondering why I hadn't dealt with it before as this thread went on... then I remembered. :w00t: )
Actually, parts of IN didn't. They were the craziest of all because each county decided on its own. Since 2006 they observe it.
Hawaii does not, nor does Puerto Rico.
--------------------------------------
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:13 pm
george sibbald (11/2/2010)
running every 10 seconds is unusual and not something you could do until SQL2008.
Actually you could do it in SQL2000. Although the EM GUI doesn't allow a schedule to repeat more than once per minute, the start time has a resolution of 1 second. So just give the job 6 schedules, all activated, all repeating at 1 minute, with start times at 10 second intervals. So in principle you could give a job 60 active schedules (I'm not aware of any limit other than that the schedule_id is an int) and get down to 1 second repeats.
Incidentally, it may be possible to do it in SQL2000 with a single schedule, but not using the EM GUI, by manually setting freq-subday_type to 2 and freq_subday_interval to 2 and freq_subday_interval to 10 in the sysjobschedules row for the schedule - I don't know whether that will work or not (I never tried it, as the above technique is good enough), but I think it's unlikely as neither sp_add_jobschedule nor sp-update_jobschedule accepted 2 for that parameter.
Tom
November 3, 2010 at 4:51 am
We have a log shipping job that runs every 15 minutes, and while the job itself didn't seem to be affected by the change of time, the alerts associated with it were; we got 45 minutes' worth of alerts from 2am to 2:45am saying the log shipping was out of sync, even though it wasn't!
November 8, 2010 at 11:29 am
I had strange problems on Sunday... here's what I posted in the Backups forum. (This is with SQL 2005)
----------------
One of our systems backs up at 2am. 8 databases, total about 150GB, backup takes just over an hour. Uses Standard SQL Server maintenance plan. Only keeps the most-current backup; deletes older files.
Sunday morning's backup, with the time change, didn't go well. It ran at the "first" 2am, in just over an hour, but the Duration in job history instead of being 01:06:25 like usual, was -3976.20:09:23 (whatever the heck date/time that represents).
Then it ran again at the "second" 2am after the time change, but it didn't delete the previous backup files (I presume because their time stamp of say 2:45 am was newer than the time the job was running). So our backup drive filled up and everything ground to a halt.
It's a once-a-year problem and it was easy to fix. But I suppose based on this I'd recommend avoiding running backups or other SQL jobs between 2am and 3am. ??
November 8, 2010 at 11:31 am
george sibbald (11/1/2010)
for what its worth, mine ran fine as scheduled. I have seen some weird negative run times reported, but only under SQL2000.
Crap, I just noticed the same thing on one of my 2005 servers and had no idea why.
Damn DST 🙂
November 8, 2010 at 2:34 pm
My tlogs back up every 30 minutes at :00 and :30. Somehow my 2am TRN got deleted. As a result my 3:30am restore on another server failed when it tried to restore the 2:30am TRN. The 2:00am sql job finished, it says so in the sql log, but I got no email notification. It also shows up in
select backup_start_date,name,first_lsn,last_lsn from msdb.dbo.backupset b where database_name = 'MYDBNAME' and type = 'L'
and backup_start_date >= '2010-11-06 22:00' and backup_start_date < '2010-11-07 04:00'
order by b.first_lsn
...which shows the LSNs I'm missing, and the reason the 2:30am restore failed
BakTmNameFirst_LSNLast_LSN
01:00XXX_backup_20101107010003226093000000473500001226094000000756000001
01:30XXX_backup_20101107013004226094000000756000001226095000001120500001
02:00XXX_backup_20101107010004226095000001120500001226096000000026100001
02:30XXX_backup_20101107023004226096000000026100001226097000001102700001
Of note, the TRNs after 2am have not had their DateModified dates altered but those before...excluding the 1am TRN**... have their DateModifieds shifted to an hour earlier. As long as you restore them in order of the YYYYMMDDHHMM in the file name you succeed (at least until you hit the missing TRN); if you go by DateModified it will fail due to the ** above.
I cannot explain *why* my 2am TRN is missing but I suspect it's something to do with the LastModified twiddling the OS or SQL is doing.
Viewing 15 posts - 31 through 45 (of 52 total)
You must be logged in to reply to this topic. Login to reply