sys.dm_tran_session_transactions DMV (Dynamic Management View) has been described by BOL as follows:
Returns correlation information for associated transactions and sessions.
This DMV helps in correlating the relation between session & transaction. We can get several details about transaction by correcting it will other DMVs & system catalogs.
Query 1 : Query to check Transaction & Session details
WHEN 0 THEN ‘SYSTEM TRANSACTION’
WHEN 1 THEN ‘USER TRANSACTION’ END AS USER_OR_SYSTEM_TRANSACTION,
WHEN 0 THEN ‘DISTRIBUTED TRANSACTION’
WHEN 1 THEN ‘LOCAL TRANSACTION’ END AS TRANSACTION_ORIGIN,
ON SP.SPID = ST.SESSION_ID
- Meaning of
int value for
All possible state of transactions for
- 1 = The transaction was initiated by a user request.
- 0 = System transaction.
To use this DMV, User required
- 1 = Local transaction.
- 0 = Distributed transaction or an enlisted bound session transaction.
VIEW SERVER STATE permission on the server.
TRANSACTION_ID > 1000 & SESSION_ID > 50 is used for user transactions & sessions.
If you liked this post, do like on Facebook at http://www.facebook.com/mssqlfun
Reference: Rohit Garg (http://mssqlfun.com/)