Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

SQL Server Agent jobs not running as scheduled Expand / Collapse
Author
Message
Posted Wednesday, April 20, 2011 5:59 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, August 20, 2014 3:45 AM
Points: 41, Visits: 459
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?

Many thanks
Post #1096245
Posted Wednesday, April 20, 2011 6:48 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Yesterday @ 4:11 PM
Points: 15,646, Visits: 28,027
On this one, I'd get in touch with Microsoft. That sounds like you've run into something serious.

----------------------------------------------------
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood..." Theodore Roosevelt
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1096282
Posted Wednesday, April 20, 2011 6:51 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 2:45 AM
Points: 21,397, Visits: 9,610
I'll be listening on this thread... I've had that problem once on sql 2000 a couple years ago. I just manually restarted the schedule but I never understood why I had that problem.
Post #1096285
Posted Thursday, April 21, 2011 12:48 PM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Monday, July 18, 2011 3:54 AM
Points: 492, Visits: 481
That is a serious issue,did you check to bounce the agent service,

for the time being create an sp and include all jobs start script as

sp_start_job 'test1'
sp_start_job 'test2'
and so on......

and then create a batch file with SQLCMD and schedule it on OS level

Regards,

Syed Jahanzaib Bin Hassan
MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA

My Blog
www.aureus-salah.com


Regards,
Syed Jahanzaib Bin Hassan
BSCS | MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA

My Blog
www.aureus-salah.com
Post #1097193
Posted Thursday, April 21, 2011 2:19 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 2:45 AM
Points: 21,397, Visits: 9,610
Syed Jahanzaib Bin hassan (4/21/2011)
That is a serious issue,did you check to bounce the agent service,

for the time being create an sp and include all jobs start script as

sp_start_job 'test1'
sp_start_job 'test2'
and so on......

and then create a batch file with SQLCMD and schedule it on OS level

Regards,

Syed Jahanzaib Bin Hassan
MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA

My Blog
www.aureus-salah.com



Wow talk about yet ANOTHER moronic post (see his last 5-10 posts). What about the consequence of running a job at the wrong time or taking production down because you just overloaded the server?
Post #1097236
Posted Saturday, April 23, 2011 3:38 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Monday, July 18, 2011 3:54 AM
Points: 492, Visits: 481
Hey Ninja Turtle,you are again talking about non sense and stupid but why Ninja Turtle why


I'll be listening on this thread... I've had that problem once on sql 2000 a couple years ago. I just manually restarted the schedule but I never understood why I had that problem.


Wow talk about yet ANOTHER moronic post (see his last 5-10 posts). What about the consequence of running a job at the wrong time or taking production down because you just overloaded the server?

you are here to provide solution,if you dont have answer then dont try to increase your points ,no body ask about your funny and non technical stories here

Regards,

Syed Jahanzaib Bin Hassan
MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA

My Blog
www.aureus-salah.com


Regards,
Syed Jahanzaib Bin Hassan
BSCS | MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA

My Blog
www.aureus-salah.com
Post #1097727
Posted Saturday, April 23, 2011 9:28 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 2:45 AM
Points: 21,397, Visits: 9,610
Syed Jahanzaib Bin hassan (4/23/2011)
Hey Ninja Turtle,you are again talking about non sense and stupid but why Ninja Turtle why


I'll be listening on this thread... I've had that problem once on sql 2000 a couple years ago. I just manually restarted the schedule but I never understood why I had that problem.


Wow talk about yet ANOTHER moronic post (see his last 5-10 posts). What about the consequence of running a job at the wrong time or taking production down because you just overloaded the server?

you are here to provide solution,if you dont have answer then dont try to increase your points ,no body ask about your funny and non technical stories here

Regards,

Syed Jahanzaib Bin Hassan
MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA

My Blog
www.aureus-salah.com


Your solution has the potential to destroy a working environement depending on what jobs are in place and what they are doing. Your solution is to just run everything I'm pointing out the danger in that.

If you can't understand that then I strongly recommend you change career.

Post #1097760
Posted Tuesday, April 26, 2011 4:51 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Monday, July 18, 2011 3:54 AM
Points: 492, Visits: 481
That solution for manual jobs execute automatically,It will check by the DBA which one is resource full and etc,I will not suggest you to change the career because I love challanges but I would like to add in the end just change your way of talking and attitude,that is unprofessional,be a professional ok

Regards,
Syed Jahanzaib Bin Hassan
MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA

My Blog
www.aureus-salah.com


Regards,
Syed Jahanzaib Bin Hassan
BSCS | MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA

My Blog
www.aureus-salah.com
Post #1098498
Posted Tuesday, April 26, 2011 5:05 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, August 20, 2014 3:45 AM
Points: 41, Visits: 459
Just to say that I did indeed restart sql server agent (tecnically speaking, as it is a cluster i took the sql server agent resource offline and brought it online again). This worked - that is to say sql server agent did starting running jobs again as per the schedules. As i mentioned in the original post i was also able to get an individual job running on its schedule again by running the job manually, seemingly bringing the job or its schedule back to life in some way!

Anyway, as it's a non-production system the feeling here is that i put this one to bed and keep an eye on this cluster and the others on the same virtual machine host. If i ever see this again i shall log a call with MS.

Cheers all.
Post #1098511
Posted Thursday, June 30, 2011 8:01 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, August 6, 2014 1:43 PM
Points: 31, Visits: 245
Just saw this thread, I've encountered this problem on my staging server (not clustered) and today on my production server (clustered). For the record, both servers are 2008 R2. It seems that this happens after I restart the services (or take offline/bring online on the cluster), the SQL Server Agent service appears to be online, says it's online and if I close and re-open the Job Activity Monitor it opens fine and shows my jobs are enabled and ready to run, but the time passes and the jobs don't start.

I'm interested if anyone with this issue found a solution. Restarting the Agent Service usually does get things rolling.
Post #1134467
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse