In our company, we encountered the exact same bug that was reported in this Connect Bug report:
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.