SQL Server sys.dm_tran_active_transactions long running transaction worktable

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

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;

StackExchange Database Administrators Q#243283, answer score: 4