A transaction has been open far too long

What this check looks for

User transactions that began more than 30 minutes ago, excluding the scan’s own session and anything that is not a user process.

The message names the session, how long the transaction has been open, and what that session is doing now, which is the part that matters most.

Why it matters

One forgotten transaction causes three separate problems at once, and each of them is usually investigated as though it were unrelated to the others.

1. It pins the transaction log. Every log record written since that transaction began has to be kept. The database’s log_reuse_wait_desc reads ACTIVE_TRANSACTION and the log grows regardless of how many log backups you take. This is the single most common cause of a log drive filling overnight.
2. It holds every lock it has taken. Anything the transaction touched stays locked until it commits or rolls back, so the blocking chain behind it grows all evening.
3. It stops the version store from cleaning up. Under read committed snapshot or snapshot isolation, tempdb keeps every row version newer than the oldest open transaction, so tempdb grows too.

The dangerous case is a sleeping session, not a running one. A transaction that is still executing is at least making progress towards finishing. A session whose status is sleeping with an open transaction has finished its work and is waiting for an application that is never going to send a commit. The usual causes are an application that opened a transaction, hit an unhandled exception and never rolled back, and a person who typed BEGIN TRAN, ran a statement, and then minimized the window.

How to confirm it yourself

SELECT s.[session_id],
       tat.[transaction_begin_time],
       DATEDIFF(MINUTE, tat.[transaction_begin_time], GETDATE()) AS [minutes_open],
       s.[status],
       s.[login_name],
       s.[host_name],
       s.[program_name],
       s.[last_request_start_time],
       s.[last_request_end_time],
       DB_NAME(r.[database_id])  AS [running_in],
       t.                  AS [last_statement]
  FROM sys.dm_tran_active_transactions AS tat WITH (NOLOCK)
 INNER JOIN sys.dm_tran_session_transactions AS tst WITH (NOLOCK)
         ON tst.[transaction_id] = tat.[transaction_id]
 INNER JOIN sys.dm_exec_sessions AS s WITH (NOLOCK)
         ON s.[session_id] = tst.[session_id]
  LEFT JOIN sys.dm_exec_requests AS r WITH (NOLOCK)
         ON r.[session_id] = s.[session_id]
 OUTER APPLY sys.dm_exec_sql_text(r.[sql_handle]) AS t
 WHERE tst.[is_user_transaction] = 1
   AND s.[is_user_process] = 1
   AND tat.[transaction_begin_time] < DATEADD(MINUTE, -30, GETDATE())
 ORDER BY tat.[transaction_begin_time];

Read the status column first. running and suspended mean work is still happening. sleeping means nothing is happening and nothing will, until somebody intervenes.

For a sleeping session, last_request_end_time tells you when it stopped doing anything, and the gap between that and now is how long it has been abandoned.

How to fix it

For the session in front of you:

• If it is sleeping and the application will not send a commit, KILL it. The rollback is the transaction being undone, and for a large transaction that takes as long as the work took in the first place. Killing it is still the right call, but do it knowing the rollback may be slow and cannot be cancelled.
• If it is running, let it finish unless it is causing an outage. Killing it triggers a rollback that may take longer than simply waiting.
• Note the program_name and host_name. That identifies the application, which is what you actually need to fix.

For the cause:

• Applications should not hold a transaction open across a user interaction or a network call. A transaction that spans a dialog box is a transaction that lasts as long as somebody’s lunch break.
• Check the client’s try and catch handling. An exception path that does not roll back is the classic source.
• Set a sensible SET XACT_ABORT ON and a command timeout in the application.
• For maintenance scripts, break large operations into batches, each in its own transaction.

How long it takes

About an hour and a half to identify the source application and get a change made. Clearing the immediate blockage takes a minute.

Related reports

Related checks

Frequently asked questions

Why 30 minutes? It is long enough that no normal transaction reaches it and short enough to catch a problem before the log drive does. A transaction that legitimately runs for hours, such as a large archive job, will be reported, and that is worth knowing about too.

The session is sleeping. Is it really doing anything? It is holding locks and pinning the log, which is doing quite a lot. Sleeping describes the connection, not the transaction.

Killing it takes forever to roll back. That is the cost of the transaction having been large. KILL <spid> WITH STATUSONLY shows the rollback percentage. It cannot be sped up or cancelled, and restarting SQL Server makes it worse because recovery does the same work at startup.

Can I stop this happening at all? Not from the server side. The fix is in the application. SET LOCK_TIMEOUT and a command timeout limit the damage but do not prevent an abandoned transaction.