October 30, 2013 at 7:24 am
I have an hourly payroll process (sql job) which needs to be disabled at 2 AM this sunday, and then renabled after the time change. I know the process to disable (run disable job once at 1:55 AM), but having brain freeze about renabling after the change takes place
October 30, 2013 at 7:25 am
Run sp_update_job @job_name = 'name of job', @enabled = 1
October 30, 2013 at 7:31 am
that part I get the issue is how do you scheduled the piece to renable. I do not want it to run at a 2 AM the first time, but after the clocks have gone back. In an automated world if I schedule the job to run when the clocks go back it won't run
October 30, 2013 at 10:34 am
Try using the GETUTCDATE() function to determine the actual time to decide if it's time to eneable the job, as in:
IF GETUTCDATE() > '2013-11-03 06:00:00'
/* Adjust the time above to the UTC time when the "2nd"
2am in your time zone acutally occurs */
BEGIN
exec sp_update_job @job_name = 'name of job', @enabled = 1
END
October 31, 2013 at 7:22 am
I'd schedule a job that waits several seconds, then creates the new job you want using the T-SQL script for the job.
November 1, 2013 at 8:41 am
have the disabling job create another job scheduled to run at 01:50 which will enable the original job.
November 1, 2013 at 9:50 am
Fall is not the time of year to worry about this. When we shift the clocks back an hour, 1:59:59 is followed by 1:00:00 instead of 2:00:00, so your 2:00 job will not run twice because 2:00 only occurs once. Spring can be a problem, when 1:59:59 is followed by 3:00:00. Some of my employers have had policies of not scheduling automated jobs in the 2:00 a.m. hour to avoid complications on that one day of the year when the hour 2:00:00 - 2:59:59 doesn't occur.
Jason Wolfkill
November 1, 2013 at 10:11 am
The issue of the clock going forward can usually be totally avoided by scheduling your job to not run exactly on the hour. Running the 2am job at 01:59 would mean it should still run.
The clocks going back in the winter i.e. as they did last weekend means you need to ensure it does not run twice (shouldn't be an issue for an hourly job but nightly ones would only be expected to run once).
You could schedule it to run at 2:01 but that would mean it wouldn't run at all when the clocks go forward in the spring.
Setting up the enable and disable jobs would mean it would continue to run as expected.
Alternatively avoid the problem by not scheduling jobs in the 01:00-03:00 window but that is a significant portion of the time usually available for overnight jobs.
The other option would be to have the server that the jobs are running on not apply daylight saving but that may cause confusion for anyone logging on to it and not expecting that behaviour.
Of course if you have servers in different time zones things could get more complicated - especially if those other time zones apply daylight saving on different dates like the US.
November 1, 2013 at 10:21 am
Of course, the entirely foolproof way to deal with this is to set the servers to UTC time, which doesn't change for daylight savings. It's usually trivial to make the proper conversions to and from local time in the front end when it's done at the outset, and in 2013, all client-server software should have this functionality to account for the very likely possibility that servers will be in different time zones than clients. Adapting legacy systems can be tricky, but can be done.
Jason Wolfkill
November 1, 2013 at 11:13 am
crmitchell (11/1/2013)
The clocks going back in the winter i.e. as they did last weekend
Which timezone? Here in the states we are looking at the time change to happen this upcoming weekend.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
November 1, 2013 at 11:13 am
dan-572483 (10/30/2013)
Try using the GETUTCDATE() function to determine the actual time to decide if it's time to eneable the job, as in:
IF GETUTCDATE() > '2013-11-03 06:00:00'
/* Adjust the time above to the UTC time when the "2nd"
2am in your time zone acutally occurs */
BEGIN
exec sp_update_job @job_name = 'name of job', @enabled = 1
END
I like the suggestion of using UTC to figure out when to re-enable the job.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
November 4, 2013 at 2:25 am
SQLRNNR (11/1/2013)
crmitchell (11/1/2013)
The clocks going back in the winter i.e. as they did last weekend
Which timezone? Here in the states we are looking at the time change to happen this upcoming weekend.
Take your pick.
Everywhere except the US changes last 4 and last 10.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply