$ guides / microsoft-sql-server / microsoft-sql-server-tempdb-version-store-growth ▌

Operations Guides

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

Cause	What it looks like	First thing to check
Orphaned application transaction	Session is sleeping in `sys.dm_exec_sessions`, no row in `sys.dm_exec_requests`, but present in `sys.dm_tran_active_snapshot_database_transactions`	Join active snapshot DMV to `sys.dm_exec_sessions`; look at `last_request_start_time`
AlwaysOn readable secondary enabled	Version store is large on a secondary replica, no long transactions visible on primary	`sys.dm_hadr_database_replica_states` on the secondary plus redo thread activity
Online index operation	Version store climbs during a large rebuild, drops when the build finishes	`sys.dm_exec_requests` filtered to `command = 'ALTER INDEX'`
ORM leaving transactions open	`SET IMPLICIT_TRANSACTIONS ON`, or framework wrapping every batch in `BEGIN TRAN` without explicit commit	`sys.dm_tran_active_snapshot_database_transactions` joined to `program_name`
Cross-database open transaction	Version store not clearing even though the database you care about looks quiet	`sys.dm_tran_active_snapshot_database_transactions` across all databases
ADR not actually enabled	You expected PVS to live in the user database but `version_store_reserved_page_count` is still high in TempDB	`sys.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 (SQL Server 2016 SP2 and later)
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;

-- PVS size when ADR is enabled (SQL Server 2019 and later)
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

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.
Identify the database contributing versions. Use sys.dm_tran_version_store_space_usage (available since SQL Server 2016 SP2). On older versions, the per-database breakdown is harder, but the active transactions DMV still points to the source.
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.
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.
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.
Do not trust DBCC OPENTRAN alone for RCSI workloads. It may not surface the relevant transaction. The DMV is the authoritative source.
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

Signal	Why it matters	Warning sign
`version_store_reserved_page_count` (`tempdb.sys.dm_db_file_space_usage`)	Direct measure of version store size in TempDB	Sustained growth, or single reading above 50% of TempDB
`sys.dm_tran_version_store_space_usage` per database	Tells you which database is generating versions	One database dominates unexpectedly
`sys.dm_tran_active_snapshot_database_transactions` row count	Number of transactions that can pin cleanup	Sustained rows, or a row with a very old `transaction_sequence_num`
Free space on TempDB volume	Cliff-edge failure when exhausted	Trending toward zero
TempDB autogrow events (default trace)	Each growth pauses I/O to the file	Repeated events
`sys.dm_tran_persistent_version_store_stats` (SQL Server 2019+)	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 and transaction-level cleanup that separates committed versions from aborted ones. sys.dm_tran_persistent_version_store_stats, available since SQL Server 2019, reports PVS size for monitoring.

SQL Server 2025 allows ADR to be enabled on TempDB itself, giving tempdb transactions the same instantaneous rollback and aggressive log truncation benefits as user databases. Enabling or disabling requires an instance restart.

Manual PVS cleanup is available via sys.sp_persistent_version_cleanup (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.

The Netdata solution

Microsoft SQL Server monitoring with Netdata

Netdata monitors SQL Server with per-second metrics, pre-built dashboards, and ML-powered anomaly detection. Correlate wait statistics, blocking chains, transaction-log and TempDB pressure, Page Life Expectancy, memory grants, per-file I/O stalls, and AlwaysOn send/redo queues against the rest of your stack so you catch the incidents in these runbooks before they page anyone.

See SQL Server monitoring → Start monitoring free