Version Store, tempdb Impact, Long Transactions, and Snapshot Cleanup

Learning outcomes

A long-running ServiceHub report holds a versioned snapshot while writers continue changing rows. tempdb grows and an operator says “snapshot leaks tempdb.” That explanation is incomplete: older versions remain because active transactions can still need them, and when Accelerated Database Recovery (ADR) is enabled, the Persistent Version Store (PVS) changes where versions live. The correct investigation begins with configuration and transaction age.

1. Version cleanup is constrained by active readers

Writers create older row images when versioning mechanisms need them. Commit does not automatically make every old image disposable: an active versioned transaction might still need a prior state. Cleanup can proceed only when versions are no longer required, and cleanup itself is asynchronous.

SELECT name,is_read_committed_snapshot_on,       snapshot_isolation_state_desc,is_accelerated_database_recovery_onFROM sys.databases WHERE name=N'ServiceHubLab';GO

2. Traditional version-store pressure is measurable in tempdb

SELECT DB_NAME(database_id) AS database_name,       reserved_page_count,reserved_space_kbFROM sys.dm_tran_version_store_space_usageWHERE database_id=DB_ID(N'ServiceHubLab');GO

Microsoft documents this DMV as an efficient per-database aggregate of traditional version-store space in tempdb. It does not identify one culprit query. Sample before/during/after and correlate with active transactions and workload.

3. Create a bounded retention experiment

ALTER DATABASE ServiceHubLab SET ALLOW_SNAPSHOT_ISOLATION ON;GOUSE ServiceHubLab;SET TRANSACTION ISOLATION LEVEL SNAPSHOT;BEGIN TRANSACTION;SELECT * FROM lab07.Inventory ORDER BY part_id;-- Leave this transaction open while Session B runs bounded updates.

DECLARE @i int=0;WHILE @i<200BEGIN  UPDATE lab07.Inventory  SET on_hand=CASE WHEN part_id=10 THEN 5+(@i%2) ELSE on_hand END  WHERE part_id=10;  SET @i+=1;END;GO

Do not expect a universal kilobyte result: page allocation and current state vary. Record your local delta. The lab is intentionally bounded so it cannot generate unbounded storage pressure.

SELECT session_id,transaction_id,transaction_sequence_num,       elapsed_time_seconds,max_version_chain_traversed,average_version_chain_traversedFROM sys.dm_tran_active_snapshot_database_transactionsORDER BY elapsed_time_seconds DESC;GO

4. ADR means versions can live in the user database PVS

ADR uses a Persistent Version Store in the user database for version-based recovery and has its own cleaner. When ADR is enabled, the old blanket statement “all row versions are in tempdb” is wrong. Observe is_accelerated_database_recovery_on and, when ON, use the supported PVS DMV for the exact engine build.

SELECT name,is_accelerated_database_recovery_onFROM sys.databases WHERE name=N'ServiceHubLab';GO-- If ADR is ON, inspect the supported schema on this exact build:-- SELECT * FROM sys.dm_tran_persistent_version_store_stats-- WHERE database_id=DB_ID(N'ServiceHubLab');

This chapter does not enable ADR merely to make a screenshot. ADR is a database recovery design choice; benchmark it in a dedicated disposable database if you need direct PVS experimentation.

5. Fix the retention mechanism, not the file size symptom

An old snapshot can keep versions relevant. Ending that transaction can make them eligible for cleanup, but space may not drop immediately because cleanup is asynchronous. Repeated tempdb shrinking is therefore not a causal fix. Killing a production session can trigger rollback and application failure and should be an incident decision with owner approval.

6. Cleanup and production monitoring

IF @@TRANCOUNT>0 ROLLBACK TRANSACTION;SET TRANSACTION ISOLATION LEVEL READ COMMITTED;GO-- After all lab sessions end, restore the recorded ALLOW_SNAPSHOT_ISOLATION value.ALTER DATABASE ServiceHubLab SET ALLOW_SNAPSHOT_ISOLATION OFF;GO

For production, trend version-store/PVS space, oldest active versioned transaction, transaction duration, tempdb free space, user-database PVS growth when ADR is ON, and workload changes. One isolated DMV sample does not establish causation.

Authoritative references

• ADR — PVS and cleaner
• Version store space DMV — tempdb version usage
• Active snapshot transactions DMV — old transaction evidence
• Snapshot isolation — version lifecycle
• SQL Server 2025 builds — CU baseline