Sample Header Ad - 728x90

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
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
enter image description here 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.
Asked by Marcello Miorelli (17274 rep)
Nov 26, 2024, 02:47 PM
Last activity: Nov 26, 2024, 10:02 PM