DMVs instead of DBCC Opentran

Those who are using SQL Server must be aware of dbcc opentran
After 2005 edition , SQL Server  is providing reach information in DMV.
For transactions 2 DMVs are available in SQL Server



1.dm_tran_database_transactions
Provide transaction information at database level
2.dm_tran_session_transactions
Provide transaction information at session level.

Lets look at how can we use it instead of dbcc opentran

first lets run

DBCC OPENTRAN

similar details we can get from DMVS , which provides much rich information thatn dbcc opentran

Lets look into this

SELECT dd.transaction_id,

       ds.session_id,

       database_transaction_begin_time,

       CASE database_transaction_type

         WHEN 1 THEN 'Read/write transaction'

         WHEN 2 THEN 'Read-only transaction'

         WHEN 3 THEN 'System transaction'

       END database_transaction_type,

       CASE database_transaction_state

         WHEN 1 THEN 'The transaction has not been initialized.'

         WHEN 3 THEN 'The transaction has been initialized but has not generated any log records.'

         WHEN 4 THEN 'The transaction has generated log records.'

         WHEN 5 THEN 'The transaction has been prepared.'

         WHEN 10 THEN 'The transaction has been committed.'

         WHEN 11 THEN 'The transaction has been rolled back.'

         WHEN 12 THEN 'The transaction is being committed. In this state the log record is being generated, but it has not been materialized or persisted'

       END database_transaction_state,

       database_transaction_log_bytes_used,

       database_transaction_log_bytes_reserved,

       database_transaction_begin_lsn,

       database_transaction_last_lsn

FROM   sys.dm_tran_database_transactions dd

       INNER JOIN sys.dm_tran_session_transactions ds

           ON ds.transaction_id = dd.transaction_id

WHERE  dd.database_id = 5

We can see that this is much more information than we can get from dbcc opentran
with  same details its also provides database transaction type , database transaction state ,
log bytes used, log bytes reserved , lsn related details also