Find Open Transactions on a SQL Server

Understanding DBCC OPENTRAN

DBCC OPENTRAN is a SQL Server database console command (DBCC) that lets administrators to access information about the database’s oldest current transaction, as well as the oldest distributed and non-distributed replicated transactions. It gives information on open transactions, which are those that have been launched but have not yet been committed or rolled back.

Checking Open Transactions

To check for open transactions in a SQL Server database, we can execute the ‘DBCC OPENTRAN’ command. The syntax for this command is as follows:

DBCC OPENTRAN ([database_name])

By specifying the database name as the parameter, we can limit the results to a specific database.
If no database name is provided, the command operates on the current database.

When executed, ‘DBCC OPENTRAN’ retrieves information about the oldest open transaction and presents the result, including the transaction ID (XID), transaction status, transaction start time, and the transaction name (if available). The transaction status can be one of the following:

• “Transaction information not available” indicates that there are no open transactions.
• “Oldest active transaction” indicates the presence of an open transaction.

Using DBCC INPUTBUFFER

Once we identify an open transaction using ‘DBCC OPENTRAN,’ we can obtain more details about the transaction by utilizing ‘DBCC INPUTBUFFER’ with the SPID (Server Process ID) associated with the transaction.

The syntax for ‘DBCC INPUTBUFFER’ is as follows:

DBCC INPUTBUFFER (spid)

Replace ‘spid’ with the actual SPID obtained from the ‘DBCC OPENTRAN’ command.

Executing ‘DBCC INPUTBUFFER’ retrieves the input buffer or the current SQL statement for the specified SPID. This can help in understanding the nature of the transaction and the query being executed.

By combining the information obtained from ‘DBCC OPENTRAN’ with the detailed SQL statement provided by ‘DBCC INPUTBUFFER,’ administrators can gain insights into open transactions, analyze their impact on the database, and take appropriate actions if necessary.

Using T-SQL statements

The belwo T-SQL statement is another approach to achieve a similar goal. It utilizes system views and functions such as ‘sys.sysprocesses’ and ‘sys.dm_exec_input_buffer’ to retrieve information about open transactions, session details, CPU usage, and other relevant attributes.

By combining the various columns in the SELECT statement, administrators can retrieve information such as Session ID, Login Name, Host Name, Database Name, Input Buffer Value, Open Transactions, Blocked Status, CPU Usage, Login Time, and Status for sessions with open transactions.

-- To check all open transactions from all databases
SELECT 
    sp.spid AS SessionID, -- Session ID
    sp.loginame AS LoginName, -- Login name
    sp.hostname AS HostName, -- Host name
    DB_NAME(sp.dbid) AS DatabaseName, -- Database name
    ib.event_info AS InputBufferValue, -- Input buffer value
    sp.open_tran AS OpenTransactions, -- Open transactions
    sp.blocked AS IsBlocked, -- Blocked status
    sp.cpu AS CPUUsage, -- CPU usage
    sp.login_time AS LoginTime, -- Login time
    sp.status -- Status
FROM 
    sys.sysprocesses sp
CROSS APPLY 
    sys.dm_exec_input_buffer(sp.spid, NULL) ib
WHERE 
    sp.open_tran = 1
    --AND sp.loginame = 'abcd_login'
ORDER BY 
    sp.login_time ASC;

The underlying principle of both approaches is to monitor and investigate open transactions in a SQL Server database, providing administrators with insights into the active transactions and their associated details. These techniques help in identifying and resolving issues related to long-running transactions, resource locks, or other performance concerns.

Whether using ‘DBCC OPENTRAN’ and ‘DBCC INPUTBUFFER’ or the T-SQL SELECT statement with system views and functions, the common goal remains to gain visibility into open transactions and their attributes, enabling efficient database management and troubleshooting.

It’s important to note that the sys.sysprocesses view is deprecated in newer versions of SQL Server. The recommended approach is to use the sys.dm_exec_sessions and sys.dm_exec_requests dynamic management views instead.

Here is the alternative T-SQL

SELECT 
    ses.session_id AS SessionID,
    ses.login_name AS LoginName,
    ses.host_name AS HostName,
    DB_NAME(er.database_id) AS DatabaseName,
    ib.event_info AS InputBufferValue,
    CASE WHEN ses.open_transaction_count > 0 THEN 1 ELSE 0 END AS OpenTransactions,
    CASE WHEN er.blocking_session_id IS NOT NULL THEN 1 ELSE 0 END AS IsBlocked,
    er.cpu_time AS CPUUsage,
    ses.login_time AS LoginTime,
    ses.status
FROM 
    sys.dm_exec_sessions AS ses
CROSS APPLY 
    sys.dm_exec_input_buffer(ses.session_id, NULL) ib
LEFT JOIN
    sys.dm_exec_requests AS er ON ses.session_id = er.session_id
WHERE 
    ses.open_transaction_count > 0
    --AND ses.login_name = 'abcd_login'
ORDER BY 
    ses.login_time ASC;