Daylight saving time and SQL agent job

Question

Post reply

Daylight saving time and SQL agent job

sqlfriend

SSC Guru

Points: 52781
More actions
March 11, 2013 at 12:34 pm

#275106

I have a SQL agent job scheduled at 2:00 am on Sunday.
But it was triggered at 2:59:50 am instead of 2:00 am, I think this is related with Daylight saving time,
Because that is exactly the time - 2:00 am should change to 3:00 am for that.
So my job delayed with corresponding job failed.
My question is:
I think the computer should adjust quickly itself, and I see now the clock is correct on the computer.
So what could be the problem when the job ran and delayed?
How can I fix this to avoid next day light saving time?
THanks

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply

KumarVelayutham SSCrazy Points: 2204 More actions · Answer 1

KumarVelayutham

SSCrazy

Points: 2204

March 12, 2013 at 3:21 am

#1595857

When are you facing this problem?

Regards,
Kumar

sqlfriend SSC Guru Points: 52781 More actions · Answer 2

sqlfriend

SSC Guru

Points: 52781

March 12, 2013 at 10:02 am

#1596108

the job is scheduled at 2:00 am on 3/10/2013

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

Not really understanding the problem. Daylight Savings Time (spring forward, fall back) is one of those times that DBA's need to be proactive to ensure that jobs run appropriately. It isn't something that is handled automatically by the system.

SQLRNNR SSC Guru Points: 281334 More actions · Answer 4

You will see various job failures like this when the system clock moves forward an hour. As Lynn said, be ready for the time change and adjust jobs as necessary for that one night each year when time "springs" forward.

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

Michael L John One Orange Chip Points: 27060 More actions · Answer 5

Becasue of this, and other situations that may be out of my hands, I never set up any SQL jobs to run at the top or bottom of an hour.

For example, my log backups run at 20 past the hour, the full backups run at 40 past the hour, and any other maintenance runs at 10 past the hour.

I may not be able to avoid running jobs on the top or bottom of an hour, but I try to avoid it at all costs.

Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/

Bill (DBAOnTheGo) Hall of Fame Points: 3841 More actions · Answer 6

you could also just not schedule anything during these times, set it to 1:59, or even just know this is something to watch for.

.

sqlfriend SSC Guru Points: 52781 More actions · Answer 7

Also if according to spring forward, fall back, if job scheduled to 1:40 am for example, there could be problem in fall too. In fall, it will move 2 to 1 o'clock for end of daylight saving, does it mean the job will run twice?

Thanks

SQLRNNR SSC Guru Points: 281334 More actions · Answer 8

Yes it will. That is another day when you need to be proactive.

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

sqlfriend SSC Guru Points: 52781 More actions · Answer 9

So maybe I should conclude as this;

for jobs scheduled on both daylight saving starting day and end day , I should be proactive to adjust jobs that scheduled between 1:00am to 3:00 am.

SQLRNNR SSC Guru Points: 281334 More actions · Answer 10

Yup

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

dan-572483 SSCoach Points: 15870 More actions · Answer 11

Good general rule: Jobs that need to run once but NOT more than once should not be scheduled between 1 & 3am on Sunday mornings.

sqlfriend SSC Guru Points: 52781 More actions · Answer 12

dan-572483 (3/12/2013)
Good general rule: Jobs that need to run once but NOT more than once should not be scheduled between 1 & 3am on Sunday mornings.

But I don't think any of jobs need to run twice

SQLRNNR SSC Guru Points: 281334 More actions · Answer 13

sqlfriends (3/12/2013)
dan-572483 (3/12/2013)
Good general rule: Jobs that need to run once but NOT more than once should not be scheduled between 1 & 3am on Sunday mornings.
But I don't think any of jobs need to run twice

You are both correct.

Dan is just saying to avoid scheduling the "1-time" nightly / weekly jobs to not run during the time frame of 1am to 3am. This would help to prevent issues with the time change.

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

dan-572483 SSCoach Points: 15870 More actions · Answer 14

sqlfriends (3/12/2013)
dan-572483 (3/12/2013)
Good general rule: Jobs that need to run once but NOT more than once should not be scheduled between 1 & 3am on Sunday mornings.
But I don't think any of jobs need to run twice

I was thinking of transaction log backups, which we run several times an hour. Within the context of this discussion we don't need to worry about them.