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

Question

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?

Answer 1

Please see:
https://learn.microsoft.com/en-us/answers/questions/100075/sleeping-sessions-with-old-open-transactions-issue.html

Answer 2

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.

Answer 3

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.