Click here to Skip to main content
13,088,711 members (54,649 online)
Click here to Skip to main content
Add your own
alternative version

Tagged as

Stats

4.2K views
1 bookmarked
Posted 31 Jan 2014

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

, 31 Jan 2014
Rate this:
Please Sign up or sign in to vote.
Returns correlation information for associated transactions and sessions.

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

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/)


License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

No Biography provided

You may also be interested in...

Comments and Discussions

 
-- There are no messages in this forum --
Permalink | Advertise | Privacy | Terms of Use | Mobile
Web01 | 2.8.170813.1 | Last Updated 31 Jan 2014
Article Copyright 2014 by Rohitmssqlfun
Everything else Copyright © CodeProject, 1999-2017
Layout: fixed | fluid