SQL Server TempDB version store growth: long transactions under RCSI and snapshot isolation

TempDB is filling and the version store is the consumer. You query tempdb.sys.dm_db_file_space_usage and version_store_reserved_page_count dominates the page budget, often after enabling Read Committed Snapshot Isolation (RCSI) or snapshot isolation, after turning a secondary replica into a readable one, or during an online index operation. Queries that depend on TempDB (sorts, hashes, temp tables, even row-versioned reads on secondaries) stall or fail when the volume runs out.

The mechanism is the same in every case. SQL Server writes old row versions into the version store in TempDB so readers see a consistent snapshot without taking shared locks. A background cleanup thread reclaims versions older than the oldest active transaction that could still need them. When a transaction stays open for minutes or hours, or is orphaned by a misbehaving application, cleanup cannot proceed past that transaction’s start time. Every version generated after that point accumulates until TempDB fills.

This guide covers RCSI, snapshot isolation, and AlwaysOn readable secondaries, and the fixes that work when you cannot simply kill the offender.

What this means

Under RCSI, snapshot isolation, AlwaysOn readable secondaries, and online index builds, SQL Server writes pre-image copies of modified rows into the TempDB version store. Readers consume those versions instead of acquiring shared locks on the current row.

The cleanup thread reclaims versions no longer referenced by any active transaction. The key constraint: the cleanup watermark is the oldest active transaction across the entire instance. If a single transaction stays open in any database, cleanup cannot reclaim versions generated after that transaction started. This is true even with Accelerated Database Recovery (ADR) enabled. ADR moves the persistent version store (PVS) into the user database for ADR-enabled databases, but it does not remove the cross-database cleanup constraint for the traditional version store that non-ADR databases still use.

One forgotten long-running transaction can balloon the version store until TempDB fills. Growth is linear in the modification rate, and cleanup is binary: it works or it does not.

flowchart TD
    A[Modifying transaction] --> B[Old row version written to TempDB version store]
    C[Snapshot reader] --> D[Reads pre-image version]
    B --> D
    E[Cleanup thread] -. reclaims versions older than oldest active txn .-> B
    F[Long-running or orphaned txn] -. holds back cleanup watermark .-> E
    F --> G[Version store grows unbounded]
    G --> H[TempDB fills]
    H --> I[Queries fail with 1105 or 3958]

Common causes

CauseWhat it looks likeFirst thing to check
Orphaned application transactionSession is sleeping in sys.dm_exec_sessions, no row in sys.dm_exec_requests, but present in sys.dm_tran_active_snapshot_database_transactionsJoin active snapshot DMV to sys.dm_exec_sessions; look at last_request_start_time
AlwaysOn readable secondary enabledVersion store is large on a secondary replica, no long transactions visible on primarysys.dm_hadr_database_replica_states on the secondary plus redo thread activity
Online index operationVersion store climbs during a large rebuild, drops when the build finishessys.dm_exec_requests filtered to command = 'ALTER INDEX'
ORM leaving transactions openSET IMPLICIT_TRANSACTIONS ON, or framework wrapping every batch in BEGIN TRAN without explicit commitsys.dm_tran_active_snapshot_database_transactions joined to program_name
Cross-database open transactionVersion store not clearing even though the database you care about looks quietsys.dm_tran_active_snapshot_database_transactions across all databases
ADR not actually enabledYou expected PVS to live in the user database but version_store_reserved_page_count is still high in TempDBsys.databases.is_accelerated_database_recovery_on

Quick checks

All queries below are read-only. Run the space-usage query in TempDB context.

-- Check version store share of TempDB
USE tempdb;
SELECT
    SUM(version_store_reserved_page_count) * 8 / 1024 AS version_store_mb,
    SUM(user_object_reserved_page_count) * 8 / 1024 AS user_objects_mb,
    SUM(internal_object_reserved_page_count) * 8 / 1024 AS internal_objects_mb,
    SUM(unallocated_extent_page_count) * 8 / 1024 AS free_space_mb
FROM tempdb.sys.dm_db_file_space_usage;

-- Per-database contribution to version store
<!-- TODO: verify whether this DMV was introduced in SQL Server 2017 rather than 2016 SP2+ -->
SELECT
    db_name,
    reserved_space_kb / 1024 AS reserved_mb
FROM sys.dm_tran_version_store_space_usage
ORDER BY reserved_space_kb DESC;

-- Find active snapshot / RCSI transactions holding back cleanup
SELECT
    ast.session_id,
    ast.transaction_id,
    ast.transaction_sequence_num,
    ast.is_snapshot,
    s.login_name,
    s.host_name,
    s.program_name,
    s.last_request_start_time,
    s.last_request_end_time,
    t.text AS most_recent_sql
FROM sys.dm_tran_active_snapshot_database_transactions ast
JOIN sys.dm_exec_sessions s ON ast.session_id = s.session_id
LEFT JOIN sys.dm_exec_connections c ON s.session_id = c.session_id
OUTER APPLY sys.dm_exec_sql_text(c.most_recent_sql_handle) t
ORDER BY ast.transaction_sequence_num;

-- Confirm whether ADR is enabled per database (SQL Server 2019+)
SELECT name, is_accelerated_database_recovery_on
FROM sys.databases
ORDER BY name;

<!-- TODO: verify whether this DMV is available from SQL Server 2019 (when ADR shipped) rather than 2022+ -->
-- PVS size when ADR is enabled
SELECT
    DB_NAME(pvs.database_id) AS db_name,
    pvs.persistent_version_store_size_kb / 1024 AS pvs_mb
FROM sys.dm_tran_persistent_version_store_stats pvs
ORDER BY pvs.persistent_version_store_size_kb DESC;

How to diagnose it

  1. Confirm the version store is the consumer. Run the space-usage query in TempDB context. If version_store_reserved_page_count is more than half of allocated pages, version store is your problem.

  2. Identify the database contributing versions. Use sys.dm_tran_version_store_space_usage . On older versions, the per-database breakdown is harder, but the active transactions DMV still points to the source.

  3. Find the transaction pinning the cleanup watermark. Query sys.dm_tran_active_snapshot_database_transactions. The is_snapshot column distinguishes snapshot isolation transactions from RCSI read transactions and from transactions that generate versions. A long-running transaction here is your culprit or one of them.

  4. Join to sys.dm_exec_sessions to see program_name, host_name, login_name, and last_request_start_time. A session whose last_request_end_time is null or far in the past, with no row in sys.dm_exec_requests, is a sleeping session holding an open transaction. This is the classic application bug.

  5. If the active snapshot DMV is empty but the version store is still large, suspect AlwaysOn readable secondaries. On a readable secondary, every reader query is implicitly mapped to snapshot isolation, and the redo thread itself can hold a transaction that pins cleanup. Run the DMV queries on the secondary, not the primary.

  6. Do not trust DBCC OPENTRAN alone for RCSI workloads. It may not surface the relevant transaction. The DMV is the authoritative source.

  7. If you recently enabled ADR, verify it is actually on for the database. ADR is per database and only moves version storage to the user database for that database. Other databases still use the TempDB version store.

Metrics and signals to monitor

SignalWhy it mattersWarning sign
version_store_reserved_page_count (tempdb.sys.dm_db_file_space_usage)Direct measure of version store size in TempDBSustained growth, or single reading above 50% of TempDB
sys.dm_tran_version_store_space_usage per databaseTells you which database is generating versionsOne database dominates unexpectedly
sys.dm_tran_active_snapshot_database_transactions row countNumber of transactions that can pin cleanupSustained rows, or a row with a very old transaction_sequence_num
Free space on TempDB volumeCliff-edge failure when exhaustedTrending toward zero
TempDB autogrow events (default trace)Each growth pauses I/O to the fileRepeated events

| sys.dm_tran_persistent_version_store_stats | PVS size for ADR-enabled databases | Growing without bound | | AG secondary redo queue and redo rate | Source of version store pressure on readable secondaries | Redo queue growing |

Fixes

Identify and end the offending transaction

If sys.dm_tran_active_snapshot_database_transactions shows a single transaction with an old transaction_sequence_num and the session is sleeping (no row in sys.dm_exec_requests), the fix is KILL <session_id>.

WARNING: KILL triggers rollback, which can take as long as the transaction ran. Coordinate with the application owner before killing production sessions.

If the transaction is on a readable secondary replica and is being held by the redo thread itself, address what is blocking redo. Usually that is a long-running read query on the secondary that took a conflicting schema stability lock. You may need to kill the reader, not the redo thread.

Address the application bug

The deeper fix is to make the application commit or roll back promptly. Common patterns that cause orphaned transactions:

  • ORMs that wrap every operation in BEGIN TRAN and rely on connection close to roll back. Under connection pooling, the connection does not close, so the transaction stays open.
  • Code paths that throw between BEGIN TRAN and COMMIT without an exception handler that rolls back.
  • SET IMPLICIT_TRANSACTIONS ON at the session level combined with a query that does not explicitly commit.
  • Monitoring agents or ETL jobs that hold a transaction across a long sleep or wait.

AlwaysOn readable secondary specifics

When a secondary is configured as readable, SQL Server silently maps all read queries to snapshot isolation. This generates versions in the secondary’s TempDB even if RCSI is not enabled on the primary. The redo thread itself holds a transaction that pins version cleanup while it processes. Practical mitigations:

  • Cap read workload concurrency on the secondary.
  • Watch the redo queue. If redo cannot keep up, version store pressure compounds.
  • Be aware of the 14-byte row overhead on the primary database when readable secondaries are configured. Disk-based tables gain a version pointer that can cause page splits on hot tables.

ADR and PVS

SQL Server 2019 introduced Accelerated Database Recovery, which stores versions in a persistent version store (PVS) inside the user database instead of TempDB. ADR does not eliminate the cross-database cleanup constraint for the traditional version store. Databases without ADR still use TempDB. ADR is per-database, not instance-wide.

SQL Server 2022 added multi-threaded PVS cleanup, transaction-level cleanup that decouples committed versions from aborted ones, and sys.dm_tran_persistent_version_store_stats for monitoring PVS size.

SQL Server 2025 allows ADR to be enabled on TempDB itself, splitting the version store into a traditional store for non-ADR user databases and a PVS for TempDB transactions. Enabling or disabling requires an instance restart.

Manual PVS cleanup is available via sys.sp_persistent_version_cleanup in SQL Server 2019 and later for ADR-enabled databases during maintenance windows.

Add TempDB space as emergency relief

Adding a TempDB data file on a different volume buys time but does not fix the underlying cause. Do this only when TempDB is about to fill and the culprit cannot be addressed immediately. Pre-grow TempDB correctly afterward.

Prevention

  • Alert on version store share of TempDB. Alert when version_store_reserved_page_count exceeds roughly 50% of TempDB size sustained. The 50% threshold is a widely-used heuristic. Calibrate to your TempDB size and modification rate.
  • Capture active snapshot transactions periodically. Sample sys.dm_tran_active_snapshot_database_transactions every 30 to 60 seconds and alert on any transaction older than a threshold your workload defines.
  • Audit application transaction duration before enabling RCSI. RCSI does not cause long transactions, but it makes their cost visible in TempDB.
  • Plan TempDB on readable secondaries separately. Secondary replica TempDB pressure is independent of primary capacity planning.
  • Monitor PVS size when ADR is enabled. Use sys.dm_tran_persistent_version_store_stats and verify cleanup is keeping up.
  • Treat long transactions under RCSI like long transactions preventing log truncation. Both are single points of failure for shared cleanup machinery.

How Netdata helps

  • Per-second collection of TempDB space utilization, broken into user objects, internal objects, and version store, lets you see the version store climbing before it fills the volume.
  • Correlating version store growth with active transactions and wait statistics shortens the path from “TempDB is growing” to “this session is the culprit”.
  • On readable secondaries, combining version store metrics with AG redo queue size and redo rate shows whether redo pressure is the source.
  • Anomaly detection on version_store_reserved_page_count flags growth that deviates from baseline before crossing an absolute threshold.
  • Tracking TempDB autogrow events alongside version store size distinguishes a sudden growth event from a gradual accumulation.

Netdata’s Microsoft SQL Server monitoring with Netdata brings these signals together with per-second metrics and ML anomaly detection.