transactions without a corresponding session in sql server
2
votes
1
answer
115
views
I was looking at some `worktable'
from the query below by my friend Martin
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.
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

Asked by Marcello Miorelli
(17274 rep)
Nov 26, 2024, 02:47 PM
Last activity: Nov 26, 2024, 10:02 PM
Last activity: Nov 26, 2024, 10:02 PM