SQL Server sys.dm_tran_active_transactions long running transaction worktable

Question

today I used the system object sys.dm_tran_active_transactions (to be honest for the first time). I had an issue with distributed transactions (Microsoft DTC) and went digging there after I had no clue with the sp_whoIsActive oputput alone.This issue is solved but now I see that there are quite old transactions in the table with type "worktable". Here's the output for the following query

SELECT  *,
 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 
ORDER BY transaction_begin_time

The transaction_begin_time happens to correlate with the time the SQL Server Service was last restarted.

Should I be concerned about this in any way? I did have a look at https://www.sqlservergeeks.com/sys-dm_tran_active_transactions/ to learn more about the dmv. However it just mentions that worktables are used for storing temporary query results in tempdb. However my understanding is that if I kick off a query whose execution plan decides to spool to tempdb and use a worktable a new worktable is created each time it is needed. Therefore it seems odd that therer are 6 worktables so old. I could hypothesize that these are tables reused since service restart for monitoring queries which run constantly or internal SQL server stuff. Anyway the transaction type read only seems to be harmless and I noticed no sign of blocking or version store growth.

Thanks in advance for your assistance

Martin

Answer 1

I see the same thing on my 2016 instance - exactly 6 worktable transactions, that started just a few seconds after the server started up. If I join to sys.dm_tran_database_transactions, I can see that these transactions exist in tempdb (database_id = 2), as one might expect for worktables:

SELECT 
    dtat.transaction_id,
    dtat.[name],
    dtat.transaction_begin_time,
    dtdt.database_id
FROM sys.dm_tran_active_transactions dtat
    INNER JOIN sys.dm_tran_database_transactions dtdt
        ON dtat.transaction_id = dtdt.transaction_id;

This also pretty closely lines up with the log messages indicating that tempdb was being cleared and started up during instance startup:

I can't find any way through the DMVs to link these "transactions" to a particular session, but I think it's safe to say they are a system process and not something to worry about.

---

Since you mentioned that you didn't see these in sp_WhoIsActive, it's worth pointing out that system session's are filtered out of that procedures output by default. You can see them by passing an extra parameter:

EXEC sp_WhoIsActive @show_system_spids = 1;