Open Transactions
Overview
The Open Transactions report answers what is holding a transaction open, for how long, and what is it costing – drawn as a runway. One lane per transaction, anchored at now on the right and running back to the moment the transaction began.
Age is the entire finding on this page. An open transaction is not interesting because it exists; it is interesting because of how long it has existed and what it is holding while it does. Drawn as length, the offender is the longest bar on the page before anybody reads a word.
Three things hang off that spine:
- Colour is what the transaction is doing right now – running, idle in transaction, rolling back, blocked.
- The rail under each bar is transaction log written and not yet committed.
- The dashed line is the log truncation waterline: the point past which the log cannot be reused.
What this replaced
The report used to be a plain grid: ten columns of text, no chart, sorted by start time, with one right-click item. Four things were wrong with it beyond having no picture.
- Nothing said whether a transaction mattered. Age was a
varchar(8)in the seventh column, next to a start time saying the same thing again. - The log damage was not on the page at all. The old query joined
sys.dm_tran_database_transactionstwice and read no byte column from it, whendatabase_transaction_log_bytes_usedis the answer to why a log will not shrink. - Blocking was invisible. A transaction with nobody behind it is a housekeeping note. One with four sessions queued behind it is an outage in progress. Same row.
- Idle in transaction was not called out. A blank Request Status meant the session was asleep holding locks, which is the classic case and the thing most people open this page to find.
Where to find it
| Route | How |
|---|---|
| Server tree | Right-click the server → Instance Level Reports → Open Transactions |
| Server Overview page | Click the Open Transactions report link |
| Report arrows | Sits between Missing Indexes and Orphan Users |
The page title reads Open Transactions for <server name>.
Requirements
-
SQL Server 2008 or newer. On 2005 it says so and stops:
This report needs SQL Server 2008 or newer. It reads the transaction log columns on sys.dm_tran_database_transactions and log_reuse_wait_desc from sys.databases.
-
VIEW SERVER STATEon the instance. -
The query timeout is 60 seconds.
The states
| State | Meaning |
|---|---|
| Running | The session has a request in flight. |
| Idle in transaction | No request at all. Asleep, holding locks and log. |
| Rolling back | ROLLBACK in progress, with the percentage where SQL Server reports one. |
| Blocked | Waiting behind another session. |
The states are ordered worst last, and a transaction takes the worst that applies. Blocked is drawn as a hollow bar with a coloured outline rather than a fill, because the rarest and worst state should not rest on hue alone – that survives colour blind mode and a bad monitor.
System transactions are drawn outlined too, and are hidden by default. A chip in the header always says how many are hidden, so hiding them is visibly a choice.
Reading the chart
The axis is linear
A bar twice as long is twice as old. A logarithmic axis would give a twelve second transaction and a four hour one comparable length, which reads more comfortably and is a lie about the one thing this page exists to measure.
What linear costs is that young transactions collapse toward the right edge. No bar is ever drawn narrower than three pixels, so a transaction that exists is always visible as something. Axis ticks are picked from a ladder of round intervals – five minutes, an hour, six hours – so they land on numbers a person recognises rather than on 47m 12s.
The log rail
Under each bar is a thinner rail in the transaction log colour: bytes that transaction has written and not committed, on its own linear scale, right-anchored so the two encodings read as one footprint. The axis caption says what full length is worth.
When one transaction owns most of the log, that rail is the whole picture: a single wide rail under a single wide bar, and near-invisible rails under everything else.
The waterline
The dashed vertical line is drawn from log_reuse_wait_desc, not from the oldest transaction. Those are not the same thing, and the difference is the point.
- When a log is pinned by an open transaction, the line falls at that transaction and reads <database> log truncation waterline. Killing it frees log space.
- When a log is pinned by something else – a missing log backup, replication, an availability group secondary – the line pins to the left edge and says so: <database> log pinned by LOG_BACKUP · not an open transaction, nothing on this page is holding it. Killing the oldest transaction would free nothing, and a line drawn at that transaction would have said it would.
- When nothing is pinned, no line is drawn.
When more than one log is constrained, the biggest one gets the line and the footer says how many others there are.
The header
The tiles carry the counts, with a coloured rule that is the severity:
| Tile | What it says |
|---|---|
| Oldest open | The oldest transaction, its SPID and when it started. Amber past 5 minutes, red past an hour. |
| Open now | How many, across how many databases. |
| Log held | Total log written and not yet committed. |
| Idle in tran | How many are holding locks and running nothing. |
| Blocking | How many sessions are waiting behind these transactions. |
The chip strip carries the constrained logs first, because those are the chips somebody is looking for, then N rolling back, N distributed, N read-only, N span databases and N system transactions hidden.
The notice band names the one transaction worth acting on, with the numbers that make the case:
SPID 71 has been idle inside a transaction for 3h 42m. It holds 1.2 GB of log and 41,900 locks. 4 sessions are waiting behind it. BillingDB cannot truncate its log until it commits or is killed.
The transaction it names is chosen deliberately: first one that is provably pinning a log, because that is the only case where killing something frees disk; then whichever is blocking the most sessions, because that is where killing something frees people; then the oldest idle transaction. A transaction that is merely old and busy is never named – it is doing work.
The Kill button
The notice band carries a Kill button on the end of it. The band is where the case for killing something is made, and an action whose entire justification is on screen should not be two right-clicks away.
It confirms first, and the confirmation is where the cost goes – see About Kill Session below.
The three shapes
| Shape | Question it answers |
|---|---|
| Age | Which transaction. One lane each, on a shared linear age axis. |
| Log pressure | Which log is in trouble, and whether a transaction is the reason. |
| By database | Which application. The Age lanes grouped under database headings. |
All three come from one query, so switching is instant and never requeries.
Log pressure
One bar per database. Full width is the log file, the pale segment is what cannot be reused yet, and the solid segment is what these open transactions wrote.
The line underneath is what turns the picture into a decision:
log_reuse_wait = ACTIVE_TRANSACTION · oldest transaction 3h 42m, SPID 71 · killing it is what frees this log
log_reuse_wait = LOG_BACKUP · last log backup 41m ago · not an open transaction, so nothing on this page frees it
Databases with no open transaction and no constraint are left off. A page of healthy logs is not what anybody came here to read.
By database
The same lanes, grouped under a heading per database carrying the database name, its log_reuse_wait_desc chip and how much log it is holding. Groups are ordered by their oldest transaction, so the database holding the oldest transaction is the first heading on the page rather than the alphabetically luckiest one.
Chart interactions
| Gesture | Result |
|---|---|
| Hover a lane | Transaction tooltip – begin time, age, state, log written and reserved, log records, lock count, wait type, who it blocks |
| Click a lane | Selects that transaction in the grid |
| Double-click a lane | Opens the query text |
| Right-click a lane | The transaction menu, plus Copy Chart to Clipboard |
| Click a database heading or bar | Filters the grid to that database; click again to clear |
| Right-click a database | The database menu |
| Click Kill in the notice band | Confirms, then runs KILL |
| Right-click empty space | Copy Chart to Clipboard only |
The hit target for a lane is the whole row, not the bar, because a three pixel bar on a young transaction would otherwise be unclickable – and the young ones are exactly the rows somebody scrubs across looking for the old one.
Reading the grid
| Column | What it is |
|---|---|
| SPID | The session id holding the transaction. |
| Age | How long the transaction has been open. Amber past 5 minutes, red past an hour. |
| State | running, idle in transaction, rolling back N%, or blocked by N. |
| Database | Every user database the transaction has touched. |
| Log Written | database_transaction_log_bytes_used, formatted. Blank when nothing has been written. |
| Locks | How many locks the session holds. This is the number that decides whether killing it is cheap. |
| Blocking | How many sessions are waiting behind it. |
| Login and Program | Login, application name and client host. |
| Started | transaction_begin_time. |
| Last Command or Query | What it is running, or the last batch it sent before it went quiet. |
The numeric columns keep zero-padded raw values behind the displayed text so the header sort is a real sort – a list view sorts as text, and would read 3h 42m as junk.
Program names are normalised the same way the Sessions and Connections pages do them, so all three name the same application the same way.
The toolbar
| Group | Buttons |
|---|---|
| Shape | Age · Log pressure · By database |
System transactions (toggle) · Refresh · Pause |
A countdown sits to the right. The page refreshes itself every 30 seconds by default, on its own private timer with a visible countdown rather than through the application’s shared navigation timer – which would push every firing into the go-back history and the popular-reports tracking.
Thirty seconds rather than the sixty Sessions uses, because open transactions turn over faster than connections do.
Auto-refresh is suspended while a confirmation dialog or a right-click menu is open, so the page cannot reload out from under a question you are being asked. Pause is your own, and it persists across navigating away and back.
Right-click actions
On a transaction:
| Item | What it does |
|---|---|
| Kill Session N | (action) Confirms, then runs KILL. |
| Show the Query for Session N | The batch behind the transaction, in the Query Advisor. |
| Show the Execution Plan for Session N | Opens the Plan Analyzer. On an idle transaction the item is worded Show the Plan for the Batch that Opened Session N’s Transaction, because that is what it will actually open. |
| Open the Blocking Tree | Only when the transaction is blocked or blocking. |
| Show only <database> in the grid | Filters the grid. |
| Copy the Query to Clipboard | Just the text. |
| Copy Transaction Detail Script to Clipboard | A read-only script for investigating it in SSMS: per-database log usage, lock breakdown, log_reuse_wait_desc, and DBCC INPUTBUFFER. |
| Copy the KILL Script to Clipboard | One commented KILL. |
On a database:
| Item | What it does |
|---|---|
| Show only <database> in the grid | Filters the grid. |
| Copy the KILL Script for these N Transactions to Clipboard | One KILL per transaction, commented with who each belongs to and what undoing it costs. |
| Copy Database Name to Clipboard | Just the name. |
About the execution plan
Which plan you get depends on what the transaction is doing, and the report says which of the three it found rather than presenting them as the same thing.
| Situation | What you get |
|---|---|
| Running, SQL Server 2016 SP1 or newer with lightweight profiling on | The live plan from sys.dm_exec_query_statistics_xml, carrying actual row counts for the operators that have already finished. |
| Running, otherwise | The cached plan for the request, from sys.dm_exec_requests. Estimates only. |
| Idle in transaction | The plan of the last batch the session ran before it went quiet – which is the batch that opened the transaction, and the one worth looking at. |
On most reports that last row is the fallback. Here it is the ordinary case: the transaction everybody opens this page to find is idle, so it has no row in sys.dm_exec_requests at all and there is no plan in flight to fetch.
When nothing comes back, the message says which reason it is:
There is no execution plan available for session 71.
This transaction is idle – the session is running nothing, so there is no plan in flight, and the batch that opened the transaction 3h 42m ago is no longer in the plan cache.
Show the Query for Session 71 still shows the SQL itself where the text is available.
That is a normal outcome for a transaction that has been open for hours, not a fault. The plan cache is not a history.
The plan is also handed through to the Query Advisor, so the plan analysis button inside that dialog is live rather than hidden when you double-click a lane. Nothing is fetched until you ask for it – the refresh never pulls plans.
About Kill Session
This is the one genuinely destructive action on the page, and it is reachable in two places: the Kill button in the notice band, and the right-click menu.
Both confirm first, and the confirmation puts the cost in front of you before you say yes:
Are you sure you want to kill session 71?
svc_billing on APP03 .NET application BillingDB
The transaction has been open for 3h 42m and will be rolled back.
It has written 1.2 GB of transaction log. The rollback has to undo all of it, and on a large transaction that can take as long as the transaction took to run.
4 sessions are waiting behind it and will be released.
A transaction that is already rolling back cannot be killed again. The page says so rather than running a second KILL:
Session 92 is already rolling back. Killing it again does not make the rollback finish any sooner, and there is nothing left to undo that SQL Server is not already undoing.
KILL runs on a connection of its own so a permissions or state failure reaches you as a message box rather than disappearing into a log.
The bulk KILL script is deliberately clipboard-only, with a header telling you to read it before running it. Killing every transaction in a database is not a one-click action.
How to read the report
- Read the tiles. Oldest open and Blocking are the two that decide whether this is urgent.
- Read the notice band if there is one. It has already picked the transaction worth acting on and said why.
- Look for the waterline. If it is drawn in red, a transaction is pinning a log and killing it frees disk. If it is drawn grey at the left edge, the log problem is real but nothing on this page will fix it.
- Look at the colour mix. Amber bars are transactions holding locks and doing nothing. That is the classic leak.
- Switch to Log pressure when a drive is filling. It says which log and why in one line.
- Switch to By database to find the application responsible.
- Double-click anything interesting for the query.
Common patterns
One long amber bar and a wall of short ones. An application opened a transaction and went away without committing. Everything else is normal traffic. This is what the page is for.
A long bar with a long log rail under it. A large batch still running, or one that has run and not committed. Either way the rollback will be expensive – check the Log Written column before killing it.
A hollow bar behind a long amber one. Blocking, with the head of the chain visible in the same picture. Open the Blocking Tree for the full chain.
Log pressure shows a huge log and log_reuse_wait = LOG_BACKUP. Nothing on this page is the cause. The database is in full recovery and the log backups are not running. Go to Backup Status.
A bar that never gets shorter across refreshes and is rolling back. A rollback in progress. It cannot be hurried, and killing it again does nothing. The percentage in the State column is SQL Server’s own estimate.
No open transactions, but a log that still will not truncate. The empty page says which database and what its log_reuse_wait_desc is, which is exactly the case where the old grid showed a blank page and no explanation.
Where the data comes from
Live, every refresh. Nothing is stored.
| Source | What it gives |
|---|---|
sys.dm_tran_active_transactions |
Begin time, transaction type and state |
sys.dm_tran_database_transactions |
Log bytes used and reserved, log record count, per database |
sys.dm_tran_session_transactions |
Which session owns the transaction |
sys.dm_exec_sessions |
Login, host, program, whether it is a user process |
sys.dm_exec_requests |
Status, command, wait type, blocking session, rollback percentage |
sys.dm_tran_locks |
Lock count per session |
sys.dm_exec_sql_text |
The running batch, or the last one sent |
sys.dm_exec_query_statistics_xml |
The live plan, on demand only, 2016 SP1 and newer |
sys.dm_exec_query_plan |
The cached plan and the last batch’s plan, on demand only |
sys.databases |
log_reuse_wait_desc and recovery model |
sys.dm_os_performance_counters |
Log file size and used size, per database |
msdb.dbo.backupset |
Last log backup, and only for databases whose log_reuse_wait_desc is LOG_BACKUP |
tempdb is excluded throughout. Every transaction on a busy instance touches it, and it tells you nothing about the transaction.
Settings
| Setting | Default | Values |
|---|---|---|
InstanceOpenTransactionsShape |
Age |
Age, Log, Database |
InstanceOpenTransactionsShowSystem |
off | on / off |
InstanceOpenTransactionsRefreshInterval |
30 |
seconds, minimum 10 |
InstanceOpenTransactionsAutoRefreshPaused |
off | on / off |
Messages you may see
Nothing open, and every log healthy:
No user transactions are open on this instance right now. Every user database can truncate its log. The page checks again every 30 seconds.
Nothing open, but a log still pinned:
No user transactions are open on this instance right now. BillingDB still cannot truncate its log – log_reuse_wait is LOG_BACKUP, which is not an open transaction.
Timed out:
The open transactions lookup did not finish in time. Try the Refresh button, or check the status of the SQL Server.
No query text:
There is no query text for session 71. The transaction is open but the batch that started it has aged out of the plan cache.
Related reports
| Report | Why you would go there |
|---|---|
| Blocking Tree | The full chain when a transaction is blocking. |
| Blocking Queries | What the blocked sessions were trying to run. |
| Sessions | Everything connected, not just what is in a transaction. |
| What is Active | What the instance is executing right now. |
| Blocking by Hour by Day | Whether this is a pattern or a one-off. |
| Deadlock History | Where blocking has already turned into deadlocks. |
Frequently asked questions
Why is the axis linear when everything else in the product uses a log scale? Because on this page length is the measurement. A log axis would make a twelve second transaction look comparable to a four hour one. Nothing narrower than three pixels is drawn, so nothing disappears.
Why does the waterline sometimes not sit on the oldest transaction? Because the waterline is read from log_reuse_wait_desc, which is what is actually stopping the log from truncating. That is often an open transaction and often not. When it is not, the line says which constraint it is and that nothing on this page will free it.
Why is a transaction that is both blocked and holding log drawn as blocked? Because that is the one somebody has to do something about. States are ordered worst last.
What does idle in transaction mean exactly? The session has an open transaction and no row in sys.dm_exec_requests at all. It is running nothing and holding everything. On the old grid this showed as an empty Request Status cell.
Why is the log rail sometimes invisible? Because that transaction has written no log. A read-only transaction never will.
Killing it did not free any log space. Why? Check log_reuse_wait_desc in the Log pressure shape. If it was not ACTIVE_TRANSACTION, the transaction was never what was holding the log.
Why are system transactions hidden? They are what SQL Server runs for itself and they crowd out the answer. The System transactions button shows them, drawn outlined rather than filled, and a chip in the header always says how many are hidden.
Why does the plan menu item change wording on some rows? Because what it opens changes. On a running transaction it is the plan of what the session is executing. On an idle one it is the plan of the batch that opened the transaction, possibly hours ago, and calling that “the execution plan” invites the reading that it is what the session is doing now – when the session is doing nothing at all.
Why is there no plan for my three hour old transaction? Because the batch that opened it finished running long ago and has since been evicted from the plan cache. The transaction is still open; the plan is not still cached. The query text often survives when the plan does not, so Show the Query may still have something.
Can I kill several transactions at once? Only via Copy the KILL Script for these N Transactions to Clipboard, which you then read and run yourself.