Results 1 to 6 of 6
  1. #1
    Join Date
    Aug 2003
    Location
    austin,tx
    Posts
    104

    Unanswered: scheduled jobs and daylight savings time

    This must've been convered before, but I can seem to find a solution to it. Daylight Savings Time is about to end in my area, at which time the clocks will go backwards from 1:59am to 1:00am.
    I have scheduled jobs that run every minute, and I'm expecting that when the clock rolls back, they will stop running for the gained hour until the clock catches back up.
    So, I need an automated way to correct this.

    I was looking at the msdb..sysjobschedules table, at the next_run_time column. But it doesn't make much sense. That value will periodically update, but it is always a good bit behind the next run time reported in EM. And manually updating it seems to have no effect.
    Is there a way to get a job to run or to recalculate the next run time via tsql script?

  2. #2
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    you can start a job via tsql with a call to msdb.dbo.sp_start_job.

    it's got an entry in BOL.

    If my memory serves correctly, EM figures out when the job is running next by calling msdb.dbo.sp_help_job (also in BOL). EDIT: you could verify this by turning on profiler and then opening up the job activity UI in EM.
    Last edited by jezemine; 10-26-06 at 17:14.

  3. #3
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    A googled about a bit, and found an article that seems to explain it all. The next_run_time in the system table is "left hanging" by the time change. The next_run_time is reset to 2:01, and SQL Agent dutifully waits until 2:01 arrives, even it if is an hour away from 2:00. If you get the job to run at the second 1:01 AM, it should execute normally for the rest of the extra hour. The only problem is, getting it to run at that time. I don't suppose you have a server kicking about that does not change it's time automatically?

  4. #4
    Join Date
    Aug 2003
    Location
    austin,tx
    Posts
    104
    Well, I can't seem to get it working.
    On a test server I stopped my jobs (they run every minute). I then set the clock back and hour. I waited several minutes and as I expected, the jobs didn't start. So I executed sp_start_job, which did start the job. I expected that when the job completed, it would re-calculate the next-run time, but that didn't happen. So I ran sp_help_job. No change. I then manually updated the next_run_time and set the value to 0, then manually started the job again. Still no change. I even stopped and restarted the SQL Server Agent. Nothing.
    I can't seem to get it to recalculate the next-run time. Frustrating.
    There must be an easy solution, it seems like it would be a somewhat common problem.

  5. #5
    Join Date
    Aug 2003
    Location
    austin,tx
    Posts
    104
    Found a way to trick it. If I run sp_update_jobschedule and disable and re-enable the schedule, it re-calcs the time and my jobs start back on schedule.

    sp_update_jobschedule @name = 'schedulename', @job_name = 'jobname', @enabled = 0
    sp_update_jobschedule @name = 'schedulename', @job_name = 'jobname', @enabled = 1

    Thanks for the replies so far.

  6. #6
    Join Date
    Aug 2003
    Location
    austin,tx
    Posts
    104
    Just in case anyone needs it, here is a script which forces all the enabled jobs to re-calc their start times.

    DECLARE @jobname varchar(128)
    DECLARE @schedname varchar(128)

    DECLARE tableCursor INSENSITIVE CURSOR FOR
    SELECT job.name, sched.name
    FROM msdb.dbo.sysjobs job, msdb.dbo.sysjobschedules sched
    WHERE job.job_id = sched.job_id
    AND job.enabled = 1
    AND sched.enabled = 1

    OPEN tableCursor
    IF @@CURSOR_ROWS <> 0
    BEGIN
    FETCH NEXT FROM tableCursor INTO @jobname, @schedname

    WHILE (@@FETCH_STATUS = 0)
    BEGIN
    ------------------------------------------
    EXEC msdb.dbo.sp_update_jobschedule @name = @schedname, @job_name = @jobname, @enabled = 0
    EXEC msdb.dbo.sp_update_jobschedule @name = @schedname, @job_name = @jobname, @enabled = 1
    ------------------------------------------
    FETCH NEXT FROM tableCursor INTO @jobname, @schedname
    END -- WHILE (@@FETCH_STATUS = 0)
    END -- IF @@CURSOR_ROWS <> 0

    CLOSE tableCursor
    DEALLOCATE tableCursor

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •