Open Transactions

Overview of Open Transactions Report

The Open Transactions report in Database Health Monitor provides visibility into transactions that have been started but not yet committed or rolled back. Long-running open transactions can lead to blocking, excessive use of tempdb version store, and prevent log truncation, so identifying them quickly is important.

Features

  • Real-Time Transaction Tracking: See currently open transactions across all sessions.
  • Transaction Age: Displays the elapsed time each transaction has been open in an easy-to-read HH:MM:SS format.
  • Session Details: Includes SPID, login name, host name, and application program name.
  • Query Text: Shows the SQL batch or statement associated with the open transaction for troubleshooting.

Understanding the Open Transactions Report

The report displays one row per open transaction with the following columns:

  • SPID – The session ID that owns the transaction.
  • Login Name – The SQL Server login that initiated the transaction.
  • Host Name – The client machine name connected to SQL Server.
  • Program Name – The application name associated with the session.
  • Transaction Start Time – The exact timestamp when the transaction began.
  • Open Time – How long the transaction has been open, shown in HH:MM:SS.
  • Request Status – Current state of the request (e.g., running, suspended, sleeping).
  • Running Command – The T-SQL command being executed, if applicable.
  • Query – The SQL statement or batch that started or is associated with the transaction.

Why Monitor Open Transactions

Monitoring open transactions is critical for both performance and reliability:

  • Blocking and Concurrency Issues: A single long-running transaction can hold locks on rows, pages, or tables, blocking other queries and slowing down the system.
  • Transaction Log Growth: Transactions that remain open prevent log truncation. This can cause the transaction log to grow indefinitely, leading to disk space pressure and slower recovery times.
  • Tempdb Version Store Usage (with RCSI or Snapshot Isolation): When Read Committed Snapshot Isolation (RCSI) or Snapshot Isolation is enabled, open transactions can prevent older row versions from being cleaned up. This leads to version store bloat in tempdb, which consumes additional disk space and can degrade performance.
  • Recovery Impact: The longer a transaction remains open, the more work SQL Server must perform during crash recovery or failover, increasing downtime.
  • Application Health: Open transactions can be a sign of application coding problems (e.g., transactions started without proper commit/rollback handling).

What to Look For

  • Transactions open for longer than a few minutes may need investigation.
  • Look for transactions from applications or users that should normally commit quickly, as they can indicate application issues.
  • Very old open transactions can cause problems with blocking, version store growth, or transaction log backups.

Getting Help from Steve and the Stedman Solutions Team

We are ready to help. The team at Stedman Solutions are here to help with your SQL Server needs. Get help today by contacting Stedman Solutions through the free 30-minute consultation form.