Why do Sql Server Jobs Hang?

Question

Hi all,

I have done a search on the reasons why the sql jobs are in executing mode but never completes.
What could be the reason. Is there a way to find out the reason from sql server Agent.
Few of the jobs in our system always hangs after business hours . So I would like to know the actual reason and stop it through script if it hangs.

Answer 1

We don't know your databases and your jobs, so it's difficult to guess the cause.

Can be locks on the table which the jobs want's to access: https://gallery.technet.microsoft.com/List-all-Locks-of-the-2a751879

Or the jobs don't hang, but are simply slow.

Answer 2

What sort of jobs are we talking about? T-SQL jobs steps? That's not any different from any other query.

You can use my beta_lockinfo to look into the activity on your server. This will tell you if there is any blocking, what queries they are running. And you will see if the they are T-SQL job steps.
http://www.sommarskog.se/sqlutil/beta_lockinfo.html

Answer 3

To summarize, I can come up with 3 reasons why a job is keep being in executing state:

1. You do something which takes a long time to do. Like working on large sets of data, modifying many many rows. You would have to troubleshoot whatever it is you are doing. If it is T-SQL code, then there are many ways to find out what is executing on a SQL Server.
2. You have some T-SQL code which is blocked. This is basically a sub-category of above. Here you need to troubleshoot a blocking situation, as suggestes by both Erland and Olaf.
3. Agent incorrectly repors this job as still executing, even though it has finished. This is less likely. Your first task now is to determine what this job does and based on that investigate if it indeed is still running or nor. I.e., find if the executable is running, the T-SQL code is running, or whatever it is you are doing in the job.