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

Today’s post is a real incident that happened with me this week. I want to share it with you all while details are still fresh in my memory. It’s an interesting case of interrupted scheduled SQL agent jobs after time zone modifications on server running SQL Server. I would like to share the story behind the issue, in case you don’t have spare time or not interested in the story, you may skip it and move directly to the issue and its resolution.

The story behind:

My first task was on Backups. The firm uses third party tools to take backups directly to tapes. This tool backups everything including VM Snapshots, Exchange servers, and Organizations data etc. of over 50 TB. To my surprise the backup strategy involved taking incremental/differential backups from Monday to Thursday and then taking one single full backup from Friday to Sunday. The reason I found for this single full backup for three days is that there are only two tape controllers where tapes can be mounted and the devices have lot of work to back up all the organization data within 3 days (Fri-Sun). Well whatsoever may be the cause but the gaps are scary. Just in case there is crash on any one of these days, we may lose more than 24 hours of data. This sounds too big mistake for a banking firm operating world-wide. Immediate action was suggested to enable copy only backups from Fri to Sun while a better backup strategy is planned keeping implementation costs in mind. I worked with stakeholders on deciding time window for backups to take place to avoid existing full backup job schedule, business hours on Friday or any other maintenance job conflicts over weekend. Time window was provided to me in IST which I very quickly converted to Client’s time zone and got approvals.

The mistake:

The Issue:

The SQL jobs were not running and several job runs were missed delaying the complete process and piling up the unprocessed files on initial folder. Clients found stale data of over 30 minutes which is when they started complaining.

The Cause:

Resolution:

I tried opening the job schedule from GUI, clicking OK/Save and I also tried to update the next scheduled run time column in various system tables within MSDB database but nothing worked.

  • 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.

Sometimes we do simple mistakes which carry and cause big issues. Resolutions to these are also pretty simple rather than something complex and deep within system settings. Though I am always careful, it was learning to be a bit more careful while running activities on Production servers.

I shared the story intentionally to make sure this issue resides in your memory for long.

Hope today’s learning was useful as well as fun. Happy Learning folks!!

--

--

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.

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

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.