Hi all, has anyone encountered sql server agent not actually running the jobs it is schdeduled to run?!
It was recently brought to my attention that some daily jobs have not been running. When i looked in Job Activity Monitor, the jobs were showing a last run date of 1st April and a next run date of 2nd April. It shows the same again even now, and the job history confirms there have been no runs since 1st April.
The jobs are enabled, their schedules are enabled and there is no end date set on the schedule. But sql server agent is showing a next run date in the past.
It is seemingly ... "stuck". I can give it a push by running the job manually, at which point the job's schedule comes back to life and sql server agent runs the job again as per the schedule.
In other words i can "fix" this issue by starting every job manually! I can also get all jobs running again by restarting sql server agent. But i am keen to know the underlying reason behind this.
We are running sql server 2008 SP2 (10.0.4000) x64 Enterprise Edition, running on on a Windows 2008 R2 OS. We have 3 instances on an active-passive cluster, which is made up of 2 VMs.
Please note: this behaviour is the same for all 3 sql instances running on this cluster (and the same VM). 1st April is the key date for all of the instances - the last jobs running just before midnight on 2nd April.
There has not been a failover or, to my knowledge, a sql agent restart since mid February.
Up to and for a few days after 1st April there were a number of very minor (less than 1 second) system time changes recorded in the system event log, including one at 23:56 on 1st April. So i am wondering if this is at the heart of the problem.
Has anyone seen sql server agent behave in this way, i.e. not actually running pending jobs, and did you pin it down to anything, e.g. virtual machines, system time changes, sql clustering, a sql/OS version/patch?