Why do Sql Server Jobs Hang?

Sreelatha 41 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.

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
8,578 questions
No comments
{count} votes

2 additional answers

Sort by: Most helpful
  1. Erland Sommarskog 68,371 Reputation points Microsoft MVP
    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

    No comments

  2. tibor_karaszi@hotmail.com 4,291 Reputation points Microsoft MVP
    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.
    No comments