I was looking at some `worktable' from the query below by my friend Martin
SELECT st.session_id
,at.*,
case transaction_type
when 1 then 'Read/Write'
when 2 then 'Read-Only'
when 3 then 'System'
when 4 then 'Distributed'
else 'Unknown - ' + convert(varchar(20), transaction_type)
end as tranType,
case transaction_state
when 0 then 'Uninitialized'
when 1 then 'Not Yet Started'
when 2 then 'Active'
when 3 then 'Ended (Read-Only)'
when 4 then 'Committing'
when 5 then 'Prepared'
when 6 then 'Committed'
when 7 then 'Rolling Back'
when 8 then 'Rolled Back'
else 'Unknown - ' + convert(varchar(20), transaction_state)
end as tranState,
case dtc_state
when 0 then NULL
when 1 then 'Active'
when 2 then 'Prepared'
when 3 then 'Committed'
when 4 then 'Aborted'
when 5 then 'Recovered'
else 'Unknown - ' + convert(varchar(20), dtc_state)
end as dtcState
FROM sys.dm_tran_active_transactions at
left JOIN sys.dm_tran_session_transactions st ON st.transaction_id = at.transaction_id
ORDER BY at.transaction_begin_time
one thing that called my attention is the fact that these transactions don't have a corresponding session?
how can I find out where they are coming from?
There is no session associated with them.
