--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
No comments:
Post a Comment