Why do Sql Server Jobs Hang?

Sreelatha 46 Reputation points
2020-09-29T13:17:53.42+00:00

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.

Developer technologies | Transact-SQL
SQL Server | Other
0 comments No comments
{count} vote

Accepted answer
  1. Olaf Helper 47,516 Reputation points
    2020-09-29T13:48:57.673+00:00

    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.

    1 person found this answer helpful.
    0 comments No comments

2 additional answers

Sort by: Most helpful
  1. Erland Sommarskog 121.9K Reputation points MVP Volunteer Moderator
    2020-09-29T22:11:30.993+00:00

    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

    1 person found this answer helpful.
    0 comments No comments

  2. tibor_karaszi@hotmail.com 4,316 Reputation points
    2020-09-30T06:41:31.313+00:00

    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.
    0 comments No comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.