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

1 comment:

Post a Comment

Amazon Contextual Product Ads

Popular Posts