Are these transactions normal?

Question

When I run this query

SELECT transaction_id, name, transaction_begin_time, transaction_state 
FROM sys.dm_tran_active_transactions

I always have these 6 rows in the result set

transaction_id	name	transaction_begin_time	transaction_state
496	worktable	29/10/2024 00:11:56	2
499	worktable	29/10/2024 00:11:56	2
501	worktable	29/10/2024 00:11:56	2
503	worktable	29/10/2024 00:11:56	2
506	worktable	29/10/2024 00:11:56	2
509	worktable	29/10/2024 00:11:56	2

Is this normal?
And if not, how can I find where they originate from so I can check out what is going on?

Based on the answer from john, I tried this query

SELECT GETDATE() as now,
       DATEDIFF(SECOND, transaction_begin_time, GETDATE()) as tran_elapsed_time_seconds,
       st.session_id,
       txt.text,
       at.transaction_id,
       at.transaction_begin_time,
       at.transaction_type,
       at.transaction_status
FROM   sys.dm_tran_active_transactions at
  left JOIN sys.dm_tran_session_transactions st ON st.transaction_id = at.transaction_id
  LEFT OUTER JOIN sys.dm_exec_sessions sess ON st.session_id = sess.session_id
  LEFT OUTER JOIN sys.dm_exec_connections conn ON conn.session_id = sess.session_id
  OUTER APPLY sys.dm_exec_sql_text(conn.most_recent_sql_handle)  AS txt
where at.transaction_id in (496, 499, 501, 503, 506, 509)
ORDER BY
   tran_elapsed_time_seconds DESC

and it gives me this result

now	tran_elapsed_time_seconds	session_id	transaction_begin_time	transaction_type
26/11/2024 11:01:57	2458201	496	29/10/2024 00:11:56	2
26/11/2024 11:01:57	2458201	499	29/10/2024 00:11:56	2
26/11/2024 11:01:57	2458201	501	29/10/2024 00:11:56	2
26/11/2024 11:01:57	2458201	503	29/10/2024 00:11:56	2
26/11/2024 11:01:57	2458201	506	29/10/2024 00:11:56	2
26/11/2024 11:01:57	2458201	509	29/10/2024 00:11:56	2

When I use an inner join for JOIN sys.dm_tran_session_transactions st I get no rows at all.

I don't know how or what more information I can give about this, does the information in this question makes sense to anyone here ?

Edit
I installed WhoIsActive (as proposed in a comment) but it returns no rows while these transactions are the only ones, so I have no help there either

score 1 · Answer 1 · answered Nov 26 '24 at 08:12

As per the documentation for sys.dm_tran_active_transactions (Transact-SQL) you might want to run the following statement to determine who is responsible or what might be going on:

 SELECT
   GETDATE() as now,
   DATEDIFF(SECOND, transaction_begin_time, GETDATE()) as tran_elapsed_time_seconds,
   st.session_id,
   txt.text, 
   *
 FROM
   sys.dm_tran_active_transactions at
   INNER JOIN sys.dm_tran_session_transactions st ON st.transaction_id = at.transaction_id
   LEFT OUTER JOIN sys.dm_exec_sessions sess ON st.session_id = sess.session_id
   LEFT OUTER JOIN sys.dm_exec_connections conn ON conn.session_id = sess.session_id
     OUTER APPLY sys.dm_exec_sql_text(conn.most_recent_sql_handle)  AS txt
 ORDER BY
   tran_elapsed_time_seconds DESC;

Without further information your question can probably only be answered with a (well educated) guess.

score 1 · Answer 2 · answered Nov 26 '24 at 16:33

SELECT
   at.transaction_id, 
   at.name,
   at.transaction_begin_time,
   at.transaction_state,
   DB_NAME(db.database_id) AS dbName
FROM
   sys.dm_tran_active_transactions at
LEFT JOIN sys.dm_tran_database_transactions db
    ON at.transaction_id = db.transaction_id
WHERE name = 'worktable'

The query above returns the database where the transactions occur. In this case, the database is tempdb, and these transactions are associated with worktables.

This reference gives us some info about worktable: "Worktables are internal tables that are created in the tempdb database and are not visible to users."

I observed the same six transactions in my system:

Based on my understanding, these transactions are likely related to internal processes and should not be a significant concern.

Also, I noticed the transaction begin time was closed to when the instance startup:

Are these transactions normal?

2 Answers2