question

KumarSunil-0372 avatar image
1 Vote"
KumarSunil-0372 asked ErlandSommarskog commented

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

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-generalsql-server-transact-sql
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

TomPhillips-1744 avatar image
0 Votes"
TomPhillips-1744 answered KumarSunil-0372 commented
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thanks for sharing the information. I am looking for a syntax that can pull out all the syntaxes executed within a transaction of the sleeping blocking session.

0 Votes 0 ·
ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered ErlandSommarskog commented

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.

· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thanks for sharing the information. I didn't want to setup trace/profiler or extended event being resource intensive, and hence looking for a syntax that can pull out all the syntaxes executed within a transaction of the sleeping blocking session (there are some third party tools available, but looking for a nearest solution that may be available with SQL itself)

0 Votes 0 ·

But you will not find those "syntaxes" with less setting up a trace or X-event session. SQL Server does not track that information automatically. Exactly because that would be too expensive.

0 Votes 0 ·
AmeliaGu-msft avatar image
0 Votes"
AmeliaGu-msft answered ErlandSommarskog commented

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.


· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thanks for sharing this information. Who is active is being used, but in this specific case, it is just listing the most recent SELECT statement inside the transaction and not listing all the syntaxes that are involved in locking the tables (either exclusive or intent exclusive).

0 Votes 0 ·

As we have told you a couple of times, that information is not available. (Unless you have trace or X-Event running.)

0 Votes 0 ·