Share via

SQL Server - How to display all SQL Statements of a sleeping uncommitted transaction?

Kumar, Sunil 6 Reputation points
2021-08-23T15:41:26.317+00:00

Often noticed applications execute SQL Statements within implicit transaction and end up in blocking other active sessions/transactions. The blocking session status would be SLEEPING, and AWAITING COMMAND. Most recent SQL statement of the blocking session ususally displays an unrelated SELECT statment on non-suspicious table/object. Though it is possible to identify the lockings on different underlying objects based on the session id, and the transaction id, is there a way find all the syntaxes of the blocking session in the same order in which they got executed, and the records affected by the statement, along with locking details? IS there any DMV that can list the activities of a sleeping transaction, or any such feature to be made available in future edition of SQL Server?

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,361 questions
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
0 comments

3 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Tom Phillips 17,721 Reputation points
    2021-08-23T16:13:59.697+00:00
  2. Erland Sommarskog 107.2K Reputation points
    2021-08-23T21:01:51.75+00:00

    Only, if you have set up a trace or extended-event session in advance. And I recommend against that, as tracing every statement being executed can be expensive and have a significant negative impact on the performance of the server.

  3. AmeliaGu-MSFT 13,971 Reputation points Microsoft Vendor
    2021-08-24T06:07:45.913+00:00

    Hi KumarSunil-0372,
    You can try to use sp_whoisactive.sql to help you find the sleeping and blocking transactions.
    Please refer to sp_whoisactive Documentation for more details.

    Best Regards,
    Amelia

    If the answer is helpful, please click "Accept Answer" and upvote it.
    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.