SQL Server Agent Got an Extra Hour of Sleep, Too

If you live in the United States, you likely had to remember to set your clocks back 1 hour last Sunday at 2 a.m. (except for those lucky folks in Arizona, most of Indiana, and Hawaii). By now, you've probably managed to change all your clocks—except that pesky one in your car dashboard—and enjoyed your extra hour of sleep. But did you know that SQL Server Agent also got an extra hour of sleep?

SQL Server MVP Ron Talmage shared with me an interesting tidbit about how the change from Daylight Saving Time (DST) affects SQL Server Agent on servers that have the DST switch set on. By Sunday at 2:01 a.m. Pacific Standard Time (ST), Ron had spent the past hour verifying that no SQL Server Agent jobs scheduled to run at 2 a.m.—none on his local server, none on his remote servers—had run. The reason is that at 1:59:59.999 DST, all remaining scheduled jobs had Next Run Dates and Times of 2 a.m. or greater. So when the server time changed from 2 a.m. to 1 a.m., no scheduled jobs ran for 1 hour. This break happens in the Northern Hemisphere only and will recur this spring in the Southern Hemisphere.

So, across the Northern Hemisphere, in time zones that "fell back" from DST to ST on October 31 and for SQL Servers whose server time is set to observe DST, all scheduled SQL Server Agent jobs stopped running for 1 hour Sunday. Put another way, in the United States, time zone by time zone Sunday, there was one time zone that had no scheduled SQL Server Agent jobs running during the hour starting at 2 a.m. DST. As 2 a.m. DST changed to 1 a.m. ST, in a sliding time scale from East Coast to West Coast, SQL Server Agent on DST servers didn't execute any scheduled jobs for an hour.

For fun, Ron stayed up late and manually forced a job run during the repeated hour. Although the run shows up in the job history, the execution of the job didn't change the Next Run Date/Time.

Did the time change affect any of your scheduled jobs? If so, you might have figured out the reason by now. And just to be safe, if you run jobs of any kind during the weekend, you might want to double-check that SQL Server Agent's extra hour of sleep didn't cause any problems, such as a missed backup, in your environment.