0

Hello whenever I select in a new query from the system view sys.dm_tran_session_transactions I get always 0 rows

select * from sys.dm_tran_session_transactions

But people on some other answers use this table.

SQL Server 2005 : map Transaction_ID to @@SPID

Why have I empty this view?

1 Answers1

0

The sys.dm_tran_session_transactions DMV will return sessions with open transactions so it seems there were no such sessions when you ran the query. Start a transaction to see the an example of data returned:

BEGIN TRAN;
SELECT * FROM sys.dm_tran_session_transactions;
ROLLBACK;
Dan Guzman
  • 43,250
  • 3
  • 46
  • 71
  • A bit strange that is needed as a `SELECT` running in auto commit mode is already a transaction and is returned from `sys.dm_tran_current_transaction` and `sys.dm_tran_database_transactions` https://i.stack.imgur.com/65nJd.png – Martin Smith Jul 25 '20 at 12:04
  • @MartinSmith, yes, the exact meaning of transaction depends on the context. Another ambiguity is with regards to a snapshot isolation level transaction. One might assume the snapshot view of data starts with the `BEGIN TRAN` but in practice it begins with the first query within that explict transaction. – Dan Guzman Jul 25 '20 at 12:17