SQL Server — SQL Agent jobs not running on schedule after time zone change

The story behind:

So here it is. I am in database consultant role for all the clients that my organization supports. I am being on boarded to a renowned banking firm which operates worldwide. I was introduced to East Asia region databases for this firm a month back and I have completed my initial evaluation of the environment by now. I have identified various actions items that we need to perform to make sure the databases are maintained in a standard way, following security compliances and performing optimally with minimum downtime. Well, all that’s said in one line is months of work. After a series of approvals working with senior management and clients, we agreed upon actions, related changes and timelines.

The mistake:

I was working on server to configure the job schedule which is when I looked the server time and found that the Server’s time zone is configured as IST. At this point I was cancelling the time zone change settings for server and due to network/server lag my cancel click somehow passed onto Ok button. Damn! The server time got changed, there wasn’t any confirmation message, no alert! I quickly reverted the change. I checked the server but couldn’t figure out any impact on server due to this until calls started flowing in after 30 minutes from client team complaining about the issue.

The Issue:

The complaints were about stopped file processing which was configured on server using SQL agent jobs. There were number of SQL Agent jobs configured on the server running every 5–10 minutes which were interconnected. One jobs executes, processes something and calls another one. A complex settings which I am sure is for a complex banking scenario.

The Cause:

I investigated and found the though the jobs were enabled and scheduled properly and the time zone was also reverted within seconds but the damage was already done. As soon as the time change happened, the Agent job which were scheduled to run that very second was triggered and got completed. The job ran at 1:30 PM IST which is 3:30 PM in client’s time zone. The job triggered when server clock was 3:30 PM, this changed the next scheduled run time to 3:35 PM. Despite the time zone change to IST the next scheduled run time was still showing 3:35 PM for which we still had about 1.5 hours to go.

Resolution:

The issue could have self-resolved after waiting for SQL jobs to run at next scheduled run time i.e. 3:35 PM IST — a gap of 1.5 hours more. Which is definitely a suicide and you understand why!!

  • What worked was quite simple. The issue got fixed when I disabled the schedule from jobs and saved it and later re-enabled the schedule and saved the jobs.
  • Another resolution to this problem would be to add a new schedule within same job to run and make it for the duration between current time and next schedule runtime.

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Dataguy! - databare.com

Dataguy! - databare.com

11 Followers

Hi! I am Amit your DataGuy. Folks do call me ‘AD’ as well. I have worked over a decade with into multiple roles - developer, dba, data engineer, data architect.