How to get the transaction mode of a spid.

• vsamantha35

  SSChampion

  Points: 11551

  More actions

  February 21, 2023 at 3:18 pm

  Go to Answer

  #4152366

  Hi All,

  Which DMV will tell me the transaction mode of a SPID?

  I did check MSDN but couldn't get the answer.

  https://learn.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-tran-session-transactions-transact-sql?view=sql-server-ver16

  - Autocommit transaction

  - Implicit transaction

  - Explicit transaction

  - Batch-scoped transaction

  - Distributed transaction

  Regards,

  Sam

• Ant-Green

  SSC Guru

  Points: 113507

  More actions

  February 21, 2023 at 4:45 pm

  Answer

  #4152393

  sys.dm_tran_active_transactions

  Should give you most of what you need here, the name will be "implicit_transaction" for implicit, the state will have 4 for a distributed transaction, etc.

• vsamantha35

  SSChampion

  Points: 11551

  More actions

  February 22, 2023 at 10:53 am

  #4152733

  Thank you very much.