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

when 0  Then 'transaction has not been completely initialized yet'

when 1  Then 'transaction has been initialized but has not started'

when 2  Then 'transaction is active'

when 3  then 'transaction has ended. This is used for read-only transactions'

when 4  then 'commit process has been initiated on the distributed transaction'

when 5  then 'transaction is in a prepared state and waiting resolution'

when 6  then 'transaction has been committed'

when 7  then 'transaction is being rolled back'

when 8  then '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