Post reply

Daylight saving time and SQL agent job

  • 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

  • March 12, 2013 at 3:21 am

    #1595857

    When are you facing this problem?

    Regards,
    Kumar

  • March 12, 2013 at 10:02 am

    #1596108

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

  • March 12, 2013 at 10:13 am

    #1596115

    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.

  • March 12, 2013 at 10:22 am

    #1596123

    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

  • March 12, 2013 at 10:27 am

    #1596128

    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/

  • March 12, 2013 at 10:49 am

    #1596155

    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.

    .

  • March 12, 2013 at 11:56 am

    #1596196

    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

  • March 12, 2013 at 11:58 am

    #1596199

    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

  • March 12, 2013 at 12:05 pm

    #1596206

    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.

  • March 12, 2013 at 12:10 pm

    #1596213

    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

  • March 12, 2013 at 12:50 pm

    #1596236

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

  • March 12, 2013 at 12:56 pm

    #1596238

    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

  • March 12, 2013 at 1:19 pm

    #1596245

    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

  • March 12, 2013 at 1:47 pm

    #1596251

    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.

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

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