Transaction related Dynamic Management Views

from http://www.sqlserverspecialists.com/2013/03/transaction-related-dynamic-management.html

 

sys.dm_tran_active_snapshot_database_transactions

 

Returns a virtual table for each active transaction that could potentially generate access row versions. Each transaction it returns has an XSN (transaction sequence number) which is given to any transaction that accesses a version store.

 

sys.dm_tran_active_transactions

 

Returns information about transactions (such as the state of the transaction, when it began, whether it is read-only or not, and so forth) executing within the SQL Server instance.

 

sys.dm_tran_current_snapshot

 

Returns a virtual table of all active XSNs currently running when the current snapshot transaction starts. Returns no rows if the current transaction is not a snapshot transaction.

 

sys.dm_tran_current_transaction

 

Returns a single row that displays the state and snapshot sequence information of a transaction in the current session. Not useful for transactions running in isolation levels other than snapshot isolation level.

 

sys.dm_tran_database_transactions

 

Returns information about transactions at the database level, especially transaction log information such as the log sequence number (LSN), log bytes used, and transaction type.

 

sys.dm_tran_locks

 

Returns information about currently active requests to the lock manager, broken into a resource group and a request group. The request status may be active, convert, or may be waiting (wait) and includes details such as the resource on where the lock request wants a log (for the resource group) or the lock request itself (for the request group).

 

sys.dm_tran_session_transactions

 

Returns correlation information for associated transactions and sessions, especially useful for monitoring distributed transactions.

 

sys.dm_tran_top_version_generators

 

Returns a virtual table for the objects that are producing the most versions in the version store, as found in the sys.dm_tran_version_store system view. Use with caution. This is a very resource intensive DMV.

 

sys.dm_tran_transactions_snapshot

 

Returns invormation about active transactions when each snapshot transaction starts. Using this, you can find how many snapshot transaction are currently active and identify any data modifications that are ignored by any given snapshot transaction.

 

sys.dm_tran_version_store

 

Returns each versioned record, both its XSN and its binary version sequence number. Because the version store can be quite large, this DMV can be very resource intensive.

 

To find all active distributed transactions on the SQL Server instance

 

SELECT *

FROM sys.dm_tran_active_transactions

WHERE transaction_type = 4;

To seeing blocked and blocking transactions on the server:

SELECT t.resource_type,

t.resource_database_id,

t.resource_associated_entity_id,

t.request_mode,

t.request_session_id,

w.blocking_session_id

FROM sys.dm_tran_locks as t1

INNER JOIN sys.dm_os_waiting_tasks AS w

ON t.lock_owner_address = w.resource_address;