SQL Agent scheduler first run date calculation bug (when "start date" < creation date of job) - by Charles F

Status : 

  Won't Fix<br /><br />
		Due to several factors the product team decided to focus its efforts on other items.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.


18
0
Sign in
to vote
ID 795880 Comments
Status Closed Workarounds
Type Bug Repros 7
Opened 8/2/2013 12:49:40 AM
Access Restriction Public

Description

In our company, we encountered the exact same bug that was reported in this Connect Bug report:
https://connect.microsoft.com/SQLServer/feedback/details/620914/sqlagent-scheduling-every-6-months#details

In short, a SQL Agent job was moved from one SQL Instance to another.
The "Duration - start date" was set to the original value that was used on the old instance, being "1/1/2011".
The Frequency is set to "Day 1 of every 12 months".
When this job was created on the "old" server in the year 2010, then the job is executed at 1/1/2011 as expected (and then yearly on the 1st of Jan from there onwards).

When moving this job to the new instance (using TSQL script), then the job gets created without any errors, so you think all is fine.
Instead, the job gets executed the 1 day of the month after it has been created, which was the 1st of August in our case.
If the "Duration - start date" would be set to "1/1/2014", then the job would be executed on the 1st of January 2014 as expected.

There are numerous issues with this situation:
- First of all, this is unpredicatable behaviour, which is simple unacceptable for a database server product.

- The bug that was reported in the Connect Bug Report above was said to be fixed in future releases. We checked if the bug is still present in SQL2012 and I can confirm that it is.
The promise in the bug report above was not met.
- As this bug can cause major issues by having jobs executed at the completely wrong time of the year (we are now having major issues because of it), I expect that the right attitude from Microsoft would be to at least fix this in SQL versions that are still in mainstream support (SQL2008, SQL2008R2, SQL2012).

A simple fix would be to not accept any "Duration - Start Date" that occurs in the past when creating a Agent Job/scheduler, and to raise an error instead...

Note: we encountered the issue on "SQL2008R2 SP2 RTM", but this is not an available option in the Version selector of the Connect bug-report webpage, so I selected "SQL2008 R2 SP2 CTP" instead.
Sign in to post a comment.
Posted by Charles F on 3/12/2015 at 1:06 AM
Hi GrangerATS,
Thanks for confirming the bug I reported. Maybe it will make MS rethink their decision to not fix this bug.
I have seen numerous Connect-reported bugs that were closed as "won't fix"... I have kinda given up on asking why they won't fix product defects... It's a waste of time...

/Charles
Posted by GrangerATS on 3/11/2015 at 3:13 PM
Why was this bug marked as "Won't Fix" without any explanation? I just reproduced this problem on Sql 2012 SP2 (11.0.5058) about 15 minutes ago.

Steps:
1. Create a new job and add a schedule.
2. Set it to recur monthly, every 12 months on {pick today's day}. (E.g. I set it to "11" because today is March 11, 2015)
3. Set to occur once at {pick a time about 1-2 minutes in the future}.
4. Set the start-date for {any date in the past}.
5. Click the "OK" buttons to create the job.
Watch as the job executes in 1-2 minutes from now, instead of 12 months from the start-date you picked.

This is a Sql Agent bug, but it's illuminated by how SSRS 2012 uses Sql Agent to implement its Subscriptions. When someone alters an existing "Monthly" report subscription to run during month that is already in the past for the current calendar year, it creates a Sql Agent Job schedule like up above. There is a workaround, but it doesn't change the fact that this is a bug.

The bug appears to simply be that Sql Agent is calculating its "next run date" based off the internal "create date" of the job instead of the start-date chosen.


The SSRS workaround is to simply make sure that whenever a schedule is edited through SSRS, after you finish with the schedule, you also go to the subscription's "start-date" and make sure it's set to today or in the future. Unfortunately for some people I know, there is going to be a lot of anger about this since there are 50-100 subscriptions they'll have to manually "fix" in order to affect this workaround.
Posted by Microsoft on 8/2/2013 at 10:03 AM
Hello Charles. Thank you for bringing this to our attention. We really do appreciate the feedback. We’ll investigate and get back to you.

-Walter A Jokiel, Program Manager, SQL Server (wajokiel@microsoft.com)