Why do Sql Server Jobs Hang?

Sreelatha 46 Reputation points

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.
12,607 questions
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,546 questions
0 comments No comments
{count} vote

2 additional answers

Sort by: Most helpful
  1. Erland Sommarskog 100.8K Reputation points MVP

    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.

    1 person found this answer helpful.
    0 comments No comments

  2. tibor_karaszi@hotmail.com 4,301 Reputation points

    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