Share via

Process got stuck - what can I do to investigate - production server

Naomi 7,366 Reputation points
2022-04-10T20:19:57.193+00:00

Hi everybody,

We have established process we run every day. Today I see that the process seems to stuck, there is an entry in our audit table around 12:47pm (almost 3 hours ago) and nothing after that for one particular stored procedure. What may I do at this point to get the process resumed? I don't have much rights on a production server, I can only reset status for some jobs.

Thanks in advance.

Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,601 questions

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Erland Sommarskog 107.2K Reputation points
    2022-04-10T20:34:54.72+00:00

    I would run my beta_lockinfo to see what is going on. This will answer which of the two options that applies:

    1. The process is blocked.
    2. The process is running a slow query.
      In the latter case, beta_lockinfo will give you the query plan of the current statement. If you are on SQL 2019 this plan will have the actual values so far - great to find misestimations. You can also get this on SQL 2016/2017, but this requires that TF7412 is in force.

    Would the process be running a loop, another tool on my web site can help you to get more information. With sp_sqltrace you can snoop the spid for some time (default ten seconds) and sp_sqltrace will aggregate recurring statements so that you get totals.

    Not surprisingly, both these tools requires permission. For beta_lockinfo, you need VIEW SERVER STATE and for sp_sqltrace you need ALTER TRACE. If you don't have these permissions, you need to pester your DBA to run them for you. If you pester her/him often enough, you may eventually get these permissions.

    On the other hand, if you don't have these permissions, you can claim that it is not your job to deal with these situations.

    1 person found this answer helpful.
  2. Bert Zhou-msft 3,421 Reputation points
    2022-04-11T03:27:36.327+00:00

    Hi，@Naomi
    Welcome to Microsoft T-SQL Q&A Forum!

    I'm sorry to see you encounter these problems. I don't have a deep understanding of this kind of problem, because most of the problems you encounter tend to be practical application scenarios. But I still give my suggestion, have you checked the connection logs in NWA (NWA->SOA>Logs and Traces>Connectivity Logs and Traces), if you can't restart and view, it is recommended to consult your DBA, let Did they try to kill the process and rollback again to see if the process was able to run.

    Best regards,
    Bert Zhou

    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
    Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    0 comments