Detect an active transaction

A transaction left open prevents SQL Server from reusing the part of the log it occupies, and the locks it took block other sessions. The typical case is an application that opened a transaction and never committed it, or a BEGIN TRAN run in an SSMS window and then forgotten. Read the commands below in order: the log is filling up, which database, which transaction, which session, and what it ran.

Log usage

DBCC SQLPERF (LOGSPACE);

DBCC SQLPERF (LOGSPACE) returns one row per database: the log size in MB (Log Size (MB)) and the percentage used (Log Space Used (%)). A database whose percentage keeps rising and does not drop after log backups is the candidate. The command requires the VIEW SERVER STATE permission.

Microsoft now recommends the sys.dm_db_log_space_usage view, which gives the same information, but for the current database only:

USE [PachaDataFormation];

SELECT total_log_size_in_bytes / 1048576 AS log_size_mb,
       used_log_space_in_percent
FROM sys.dm_db_log_space_usage;

Log truncation wait reasons

SELECT name, recovery_model_desc, log_reuse_wait_desc
FROM sys.databases;

log_reuse_wait_desc tells you what prevents log truncation. ACTIVE_TRANSACTION means an open transaction is holding the log. The other values point elsewhere: LOG_BACKUP is waiting for a log backup (databases in the full or bulk-logged recovery model), REPLICATION or AVAILABILITY_REPLICA for replication or a replica that is behind. The Microsoft page on the transaction log describes every value.

The column holds a single value, which Microsoft describes as the reason observed at the last checkpoint. On a database in the full recovery model, it can show LOG_BACKUP while a transaction is open. On the test instance, with a transaction open, the column stayed at LOG_BACKUP after a first log backup, and only changed to ACTIVE_TRANSACTION after the second one. Do not conclude that no transaction is open from LOG_BACKUP alone: go on to the next step.

The oldest transaction in a database

DBCC OPENTRAN (N'PachaDataFormation');

Without an argument, DBCC OPENTRAN examines the current database. It shows the oldest active transaction in the database, with the session that owns it (SPID), its name (user_transaction for an unnamed BEGIN TRAN), its LSN and its start time (Start time). When nothing is open, it answers No active open transactions.

DBCC OPENTRAN shows only one transaction, the oldest: if there are several, the others do not appear. It requires membership in the sysadmin fixed server role or the db_owner role of the database.

Sessions and their open transactions

sp_who2 is often suggested for this step. It lists every session on the instance:

EXEC sp_who2;

The procedure has no reference page in the Microsoft documentation, which lists it among the older commands, and it does not tell you whether a session has an open transaction. A session that ran BEGIN TRAN, modified rows, and then sits idle appears in sp_who2 with the status sleeping and the command AWAITING COMMAND, exactly like a harmless session. Microsoft describes this case in scenario 2 of its blocking guide.

The dynamic management views answer directly. The following query lists every open transaction of a session other than yours, oldest first, with the amount of log it has written and the last batch the session sent:

SELECT st.session_id,
       s.login_name,
       s.host_name,
       s.program_name,
       s.status,
       s.open_transaction_count,
       at.transaction_begin_time,
       DB_NAME(dt.database_id) AS database_name,
       dt.database_transaction_log_bytes_used AS log_bytes,
       ib.event_info AS last_batch
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
LEFT JOIN sys.dm_tran_database_transactions AS dt ON dt.transaction_id = st.transaction_id
OUTER APPLY sys.dm_exec_input_buffer(st.session_id, NULL) AS ib
WHERE st.session_id <> @@SPID
ORDER BY at.transaction_begin_time;

What to read:

• status at sleeping with open_transaction_count greater than 0: the session is doing nothing and keeps its transaction open, this is the forgotten transaction;
• transaction_begin_time: how long the transaction has been holding the log and its locks;
• log_bytes: the amount of log written in this database, which gives an idea of what a rollback will have to undo;
• host_name, program_name and login_name: what you need to track down the application or the person responsible.

A transaction that touches several databases, tempdb included, produces one row per database. The query requires the VIEW SERVER STATE permission, or VIEW SERVER PERFORMANCE STATE starting with SQL Server 2022.

What the session ran

For a given session, replace 51 with the session_id found in the previous step:

DECLARE @session_id smallint = 51; -- replace 51 with the session_id to examine

SELECT event_type, event_info
FROM sys.dm_exec_input_buffer(@session_id, NULL);

DBCC INPUTBUFFER (@session_id);

Both return the last batch sent by the session: its text for a SQL batch (Language Event), the procedure name for an RPC call (RPC Event). sys.dm_exec_input_buffer has existed since SQL Server 2014 SP2 and returns the full text, as nvarchar(max). DBCC INPUTBUFFER truncates the text to 4,000 characters; it is still useful on an older instance.

The last batch is not necessarily the one that opened the transaction. If the application sent BEGIN TRAN in one batch, then other statements, you only see the last one. The text mostly helps you recognize the application and the screen involved.

Before a KILL

KILL ends the session and rolls back its transaction. Before you run it, weigh the loss and the cost.

The loss: everything the transaction modified since its BEGIN TRAN is rolled back, and the application loses its connection. If it was running a batch, it receives a severity 21 error (Cannot continue the execution because the session is in the kill state.) and the remaining statements of the batch do not run, COMMIT included. If the transaction was legitimate work in progress, it will have to be restarted from the beginning.

The cost: the session does not go away immediately. SQL Server first has to undo, from the log, everything the transaction wrote, and its locks are held in the meantime. On the test instance, rolling back an UPDATE of one million rows (110 MB of log) took about two seconds, for three seconds of execution. Microsoft warns that a modification that ran for an hour can take at least an hour to roll back (Understand and resolve SQL Server blocking problems). A second KILL does not interrupt it, and restarting the instance does not help: startup recovery does the same work, with the database unavailable in the meantime.

With accelerated database recovery (ADR, SQL Server 2019 and later), rollback is instantaneous. It is off by default in SQL Server (Manage accelerated database recovery): check is_accelerated_database_recovery_on in sys.databases.

KILL does not accept a variable: type the session number as a literal, replacing 51 with the session_id you found.

KILL 51; -- replace 51 with the session_id of the transaction to roll back

To follow the rollback, use WITH STATUSONLY on the same number, which shows the percentage done and the estimated time remaining:

KILL 51 WITH STATUSONLY; -- same session_id as the KILL

Do not rerun KILL without WITH STATUSONLY to check progress: if the rollback finishes in the meantime and the number is assigned to a new connection, that is the one you kill. Once the rollback is over, WITH STATUSONLY returns error 6120 (Rollback operation for Process ID ... is not in progress.). During the rollback, sys.dm_exec_requests shows the session with the status rollback and a percent_complete that moves forward.

KILL requires the ALTER ANY CONNECTION permission, included in the sysadmin and processadmin fixed server roles. You cannot kill your own session.

Once the transaction is over, the log is not freed right away in the full recovery model: the next log backup does it. The how-to Identify and fix transaction log problems covers what comes next.