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