Home Dashboard Directory Help
Search

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


Status: 

Active


17
0
Sign in
to vote
Type: Bug
ID: 795880
Opened: 8/2/2013 12:49:40 AM
Access Restriction: Public
1
Workaround(s)
view
5
User(s) can reproduce this bug

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.
Details
Sign in to post a comment.
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)
Sign in to post a workaround.
Posted by Harshit verma on 8/2/2013 at 2:36 AM
Here is workaround :

check all jobs having monthly schedules as these jobs can cause such issue

select sj.name from sysschedules as sch, sysjobschedules as sjs,sysjobs as sj where sch.schedule_id = sjs.schedule_id and freq_type =16 and sj.job_id=sjs.job_id

Here you will find all job names having monthly schedule and then check schedule of jobs start date tab,if start date is less than created/modified date then update start date with first actual execution date of job. After changing start date with first execution date, it will run on that day and next execution date will be calulated on the basis of that day.