SQL Agent Job not finishing

Question

SQL Agent Job not finishing

Casper101

Hall of Fame

Points: 3903
More actions
February 10, 2014 at 7:42 am

#289336

Hi,
Why would a scheduled SQL agent job not finish and go into SUSPENDED mode?
The job usually takes only a couple of seconds to complete and runs during the night when there is not much activity on the DB.
But it has happened that the job does not finish and at 8 AM when business opens it then causes loads of problems with deadlocks, forcing me to Kill the job.
What might be the cause of this?
How can I troubleshoot this?

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

HanShi SSC-Dedicated Points: 33508 More actions · Answer 1

What does the job do?

If it calls an external service (for example a third party tool), it could be that this service fails to return the signal when finished (service could hang...). The job keeps sitting there, waiting on a signal that will never come.

** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **

MyDoggieJessie SSC-Forever Points: 44486 More actions · Answer 2

Please post the TSQL for the jobs steps, as HanShi has mentioned, it could very well be related to an "external" process like xp_cmdshell, an executeable, etc.

______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience

Casper101 Hall of Fame Points: 3903 More actions · Answer 3

Nope there are no external sources / references.

It is an UPDATE and INSERT statement from 1 DB to another to keep the 2 in synch - UPDATE if the records match, INSERT if the record is missing

HanShi SSC-Dedicated Points: 33508 More actions · Answer 4

Casper101 (2/10/2014)
Hi,
...it has happened that the job does not finish and at 8 AM when business opens it then causes loads of problems with deadlocks, forcing me to Kill the job...

I guess you mean you notice a lot of BLOCKING (deadlocks will be handled by SQL without user intervention). Did you confirm the action from the job is the HEADblocker and is not blocked by another process?

I've encountered problems where a process (B) was blocked by another process (A) and the blocked process (B) caused massive blocking for other processes. Stopping the headblocker (A) instead of the first blocked process (B) solved the issue. The blocked process (B) could finish within miliseconds after which all blockings were gone. Of course, stopping process B would also solve the blocking issue, but process B was not the root-cause.

** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **