DMV-12 : Retain in Session & Transaction……..sys.dm_tran_session_transactions

sys.dm_tran_session_transactions DMV (Dynamic Management View) has been described by BOL as follows:

• http://msdn.microsoft.com/en-us/library/ms188739.aspx

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

SELECT
ST.SESSION_ID,
ST.TRANSACTION_ID,
DB_NAME(SP.DBID) DB_NAME,
CASE IS_USER_TRANSACTION
WHEN 0 THEN ‘SYSTEM TRANSACTION’
WHEN 1 THEN ‘USER TRANSACTION’ END AS USER_OR_SYSTEM_TRANSACTION,
CASE IS_LOCAL
WHEN 0 THEN ‘DISTRIBUTED TRANSACTION’
WHEN 1 THEN ‘LOCAL TRANSACTION’ END AS TRANSACTION_ORIGIN,
SP.HOSTNAME,
SP.LOGINAME,
SP.STATUS,
SP.LASTWAITTYPE,
SQLT.TEXT
FROM
SYS.DM_TRAN_SESSION_TRANSACTIONS ST
JOIN
SYS.SYSPROCESSES SP
ON SP.SPID = ST.SESSION_ID
CROSS APPLY
SYS.DM_EXEC_SQL_TEXT(SP.SQL_HANDLE) SQLT

Sample Result

Remarks

1. Meaning of int value for is_user_transaction column 
• 1 = The transaction was initiated by a user request.
• 0 = System transaction.
2. All possible state of transactions for is_local coulmn
• 1 = Local transaction.
• 0 = Distributed transaction or an enlisted bound session transaction.
3. To use this DMV, User required VIEW SERVER STATE permission on the server.
4. 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/)