Interview Question of the Week #035 – Different Ways to Identify the Open Transactions

Finding an open transaction is one of those DBA tasks that looks easy until the owner stops running a request. The session may be sleeping while the transaction still holds locks or prevents log reuse.

Question: What are the different ways to identify open transactions in SQL Server?

Answer: The original quick answers were DBCC OPENTRAN, sys.sysprocesses, and sys.dm_tran_session_transactions. They serve different purposes. DBCC OPENTRAN reports oldest active transaction information for the selected database. sys.sysprocesses is a legacy compatibility view. The transaction DMVs joined to session information are better for locating current owners, including sleeping sessions. @@TRANCOUNT only tells you the depth in your own session.

For a particular database, run DBCC OPENTRAN there. Then use the modern DMVs to list transaction owners. Appropriate permissions are needed: these transaction DMVs generally require VIEW SERVER STATE on older SQL Server releases or VIEW SERVER PERFORMANCE STATE on SQL Server 2022 and later. DBCC OPENTRAN has its own sysadmin or database-owner permission requirement:

DBCC OPENTRAN;

SELECT st.session_id, s.status, s.open_transaction_count,
       at.transaction_begin_time, s.program_name, s.host_name
FROM sys.dm_tran_session_transactions AS st
JOIN sys.dm_exec_sessions AS s
  ON s.session_id = st.session_id
JOIN sys.dm_tran_active_transactions AS at
  ON at.transaction_id = st.transaction_id
JOIN sys.dm_tran_database_transactions AS dt
  ON dt.transaction_id = st.transaction_id
WHERE dt.database_id = DB_ID()
  AND s.open_transaction_count > 0
ORDER BY at.transaction_begin_time;

For an older system, the original compatibility-view check is SELECT * FROM sys.sysprocesses WHERE open_tran > 0;. Use greater than zero rather than one so a nested open count is not missed. ACTIVE_TRANSACTION in sys.databases.log_reuse_wait_desc is another clue; the owning connection’s latest submitted statement can be obtained through sys.dm_exec_connections.most_recent_sql_handle and sys.dm_exec_sql_text. That statement is a clue, not necessarily the statement that began the transaction. Here is a real result from a controlled two-session scratch demonstration. To make the screenshot, one connection held a transaction open and the SSMS connection ran this exact narrower query. The transaction was rolled back after capture:

USE BlogScreenshotLab_20260929;
GO
SELECT s.session_id AS SessionId,
       s.status AS SessionStatus,
       s.open_transaction_count AS OpenTransactions,
       at.transaction_begin_time AS BeganAt
FROM sys.dm_tran_session_transactions AS st
JOIN sys.dm_exec_sessions AS s ON s.session_id = st.session_id
JOIN sys.dm_tran_active_transactions AS at
  ON at.transaction_id = st.transaction_id
JOIN sys.dm_tran_database_transactions AS dt
  ON dt.transaction_id = st.transaction_id
WHERE dt.database_id = DB_ID()
  AND s.program_name = N'SQLAuthority Screenshot Demo';

The screenshot proves that a session can be sleeping while still owning a transaction. Its timestamp belongs to the demonstration, not to the reader’s server. Once you identify the owner, coordinate a commit or rollback with the application owner. Terminating a session is a last resort because rollback may take time.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.