8

Context

I have no idea where this is documented and was shocked that I could not find a duplicate. I only know it from seeing it go wrong several years in a row.

The Problem

On the day when the clocks go back an hour, SQL Server will repeat any Agent Jobs that run at a fixed time each day. This means that if I have a job run at 01:05 each day, then it will run twice in the same day if the clocks go back an hour at 02:00.

Is there any means within SQL Server Agent to prevent this? It appears that AWS EventBridge Schedules can do it.

The Q & A Daylight Saving time is related, but their jobs run on a regular frequency (once every 15 minutes). Mine runs at a specific time.

The problem has been witnessed several years in a row across more than one server.

Paul White
  • 94,921
  • 30
  • 437
  • 687
J. Mini
  • 1,161
  • 8
  • 32

6 Answers6

12

Use UTC when setting up your SQL Server.

If the platform you're serving is large enough that this is an issue, then you're probably doing business in more than one time zone. If you never plan on getting much bigger, then go ahead and use a workaround.

If you expect to keep growing and continuing to expand your east–west footprint, consider changing the system time of your SQL Server host to UTC on your next major version upgrade. It will probably suck — but it will only suck once.

You've already begun to experience the pain of local time zones. I promise there are more to come. UTC never has DST; and I for one prefer my reference source of truth to be constant.

Community
  • 1
Peter Vandivier
  • 5,485
  • 1
  • 25
  • 49
7

Don't start jobs between the times when clocks change in your location.

For example In the UK the clocks go forward 1 hour at 1am on the last Sunday in March, and back 1 hour at 2am on the last Sunday in October. Therefore it is only 01:nn times that can happen twice or not at all due to DST changes. So you can change the start time to 00:55 or 02:05 to completely avoid this issue (and document why you have made the change so someone doesn't change it back).

I don't like the idea of ignoring a whole hour of my maintenance window in order to defend myself from a problem that only happens once a year.

Moving from 01:05 to 00:55 is a 10 minute difference and once the job starts it will continue running during the clock change. Another alternative is to run the job at 01:05 except on the days when there is a clock change and use a different start time on those days. - MT0

Martin Smith
  • 87,941
  • 15
  • 255
  • 354
comment answers
  • 1
  • 2
5

Write your jobs in a way, that it doesn't matter if they run twice on the same day.

  • E. g. If you are processing today's orders, flag all the processed rows (or store them in table somewhere) and ignore those on subsequent runs.
  • If you are inserting new row, check whether they not already exist and perhaps do MERGE or UPDATE instead.
  • If you are doing maintenance, check whether it is really needed at this moment.

This is also useful for debugging and further developing the jobs, because you can just run then again and again as needed.

Also, note that if the job is still running when it is scheduled to run again, it won't execute twice.

Yano_of_Queenscastle
  • 1,851
  • 7
  • 24
3

Logging:

You could add logging, so your job logs its completion to a table, with a timestamp of when job started, and when job ended.

Then, at the beginning of your job, add a code that first checks that logging table. If job already has been run today, don't run rest of code (or next steps) in a job.

MSDB

Update: even simpler, you can implement checking msdb..sysjobhistory table before job's execution, look for run_date column. If job already has been run today, don't run rest of code (or next steps) in a job.

This is the code that can be used to check whether job has already been executed today:

if exists (

select jh.*
from msdb..sysjobhistory jh
    join msdb..sysjobs j on
        jh.job_id = j.job_id
        and j.[name] = '<job name >'    -- type your job name here
where and run_status = 1
 and cast((substring(cast(run_date as varchar(10)),1,4) + '-' + substring(cast(run_date as varchar(10)),5,2) + '-' + substring(cast(run_date as varchar(10)),7,2)) as date) = cast(getdate() as date)


)


return   -- this will make sure no more code is executed
Aleksey Vitsko
  • 6,148
  • 5
  • 39
  • 70
2

Ah yes, what to do on those days in the year when there are 23 or 25 hours in a day, and when hours can either be skipped completely or recur twice.

And should a once-daily job be handled differently than an hourly job? Presumably you want an hourly job to recur twice (once for each occurence of the same hour), whereas you want the once-daily job to occur only once that day - so there isn't necessarily one rule that fits all.

Personally, I would suggest that the best approach is simply not to schedule things in the 0100-0159 period, when possible. Once a year on a certain date, times in this range simply do not occur, and once a year on a certain (different) date, they occur twice on the same day.

If you have to schedule things in this period normally, then you need to think explicitly about what you do (if anything) on the day when no such time occurs and there are only 23 hours in that day.

And on the day when the hour occurs twice and the day therefore has 25 hours, you need to think explicitly about whether you run the job twice, or whether it runs only once (and if so, then whether it runs once in 0100-0159-a or once in 0100-0159-b).

I believe the Romans had a superstition about any timekeeping that was overly-regular, as it leads to civic activity being over-trained to a particular schedule, and a lack of consideration for how exceptional disruption will be handled.

Consider daylight-savings to be a similar innoculating practice, that not only challenges the idea that every day consists of 24 hours, but forces you to think about how a rare upset is actually handled!

Steve
  • 867
  • 5
  • 7
0

We had the exact opposite issue.

If you have a job that runs every 15 minutes, there will be a gap of 75 minutes, where the job DOES NOT run at all.

We tested on one SSIS job (triggered by a sql agent), on the night of the 27th, with a specific different job to trigger the SSIS package and it looks like this:

This did run multiple times, because we forced it

While other SSIS packages just look like this:

And this did not run again

Same happened on my own machine when testing, job runs -> Sets a "next run date", if you turn back time, it will not run again before reaching this "next run date"

This has been documented by different people over time as well.

dba stackexchange

SQL Skills

Here as well

Yannick Liekens
  • 1,781
  • 7
  • 16