About Me

Barking, Essex, United Kingdom
MCITP DBA ,MCITP BI & Oracle OCP 11G

Sunday, October 6, 2013

To detect the Active open transaction

--dbcc opentran()

--select @@trancount

select AT.transaction_id,AT.name,AT.transaction_begin_time,
case AT.transaction_type
when 1 then 'Read/write'
when 2 then 'Read'
when 3 then 'System'
when 4 then 'Distributed transaction'
End as transaction_type,
case AT.transaction_state
whenThen 'transaction has not been completely initialized yet'
whenThen 'transaction has been initialized but has not started'
whenThen 'transaction is active'
whenthen 'transaction has ended. This is used for read-only transactions'
whenthen 'commit process has been initiated on the distributed transaction'
whenthen 'transaction is in a prepared state and waiting resolution'
whenthen 'transaction has been committed'
whenthen 'transaction is being rolled back'
whenthen 'transaction has been rolled back'
End as transaction_state,
ST.session_id,
--ES.login_name as Activetranuser,
DT.database_id as DBid
--ES.client_interface_name as Programname,
--DB_name(ER.database_id) as DBName
from sys.dm_tran_active_transactions as AT
inner join
sys.dm_tran_database_transactions as DT
on AT.transaction_id=DT.transaction_id
--inner join sys.dm_exec_requests as ER
----on ER.database_id=DT.database_id
inner join sys.dm_tran_session_transactions as ST
on AT.transaction_id=ST.transaction_id
--inner join sys.dm_exec_sessions as ES
--on ST.session_id=ES.session_id


--select * from sys.dm_tran_active_transactions

No comments:

Post a Comment