SQL Server ADR persistent version store keeps growing: find what blocks cleanup and get the space back

#Symptom

A user database with Accelerated Database Recovery (ADR) turned on keeps growing even though the row count is flat. The data file autogrows, the filegroup fills, or writes fail with error 1105 (could not allocate space). Tempdb looks fine. The space is going to the persistent version store (PVS).

ADR moves row versioning out of tempdb and into the user database. That makes rollback and crash recovery almost instant. The cost is that old row versions live in your data files until a background cleaner removes them. The cleaner can only remove a version once no transaction or snapshot could still need it. When something holds that low-water mark back, the PVS grows without limit.

#How PVS cleanup works

Three things decide whether a version can go:

Microsoft documents two scale caveats. Long-running active transactions in any ADR-enabled database on the instance can block cleanup. In SQL Server 2019 the cleaner also works on one database at a time. On a busy multi-database instance, one bad database can therefore starve the others.

#Check 1: how big is the PVS and is cleanup running?

Run this in the affected database:

SELECT  DB_NAME(pvss.database_id)                     AS database_name,
        pvss.persistent_version_store_size_kb / 1024  AS pvs_mb,
        pvss.online_index_version_store_size_kb / 1024 AS online_index_vs_mb,
        pvss.current_aborted_transaction_count,
        pvss.oldest_active_transaction_id,
        pvss.oldest_aborted_transaction_id,
        pvss.min_transaction_timestamp,
        pvss.secondary_low_water_mark,
        pvss.offrow_version_cleaner_start_time,
        pvss.offrow_version_cleaner_end_time,
        pvss.aborted_version_cleaner_start_time,
        pvss.aborted_version_cleaner_end_time,
        pvss.pvs_off_row_page_skipped_oldest_active_xdesid,
        pvss.pvs_off_row_page_skipped_min_useful_xts,
        pvss.pvs_off_row_page_skipped_oldest_snapshot
FROM    sys.dm_tran_persistent_version_store_stats AS pvss
WHERE   pvss.database_id = DB_ID();

How to read it:

• persistent_version_store_size_kb counts off-row versions only. In-row versions are not included, so the real overhead is a bit higher.
• If offrow_version_cleaner_start_time is set and offrow_version_cleaner_end_time is NULL, a cleanup pass is running right now. A pass that has been running for hours on a large PVS is normal. A pass that finishes quickly while the PVS stays the same size means something is blocking it.
• The pvs_off_row_page_skipped_* counters tell you why pages were skipped. A rising ..._oldest_active_xdesid points at an open transaction. ..._oldest_snapshot points at a snapshot reader. ..._min_useful_xts points at the low-water mark in general, often from a secondary.
• A non-zero secondary_low_water_mark that lags far behind the primary means a readable secondary is holding cleanup back.

Column names have changed between builds. If one of these errors, run SELECT * FROM sys.dm_tran_persistent_version_store_stats and check what your version returns.

#Check 2: find the oldest open transaction

SELECT TOP (10)
        st.session_id,
        dt.transaction_id,
        dt.database_transaction_begin_time,
        DATEDIFF(MINUTE, dt.database_transaction_begin_time, SYSDATETIME()) AS open_minutes,
        dt.database_transaction_log_bytes_used / 1048576 AS log_mb,
        s.login_name, s.host_name, s.program_name, s.status,
        r.command, r.wait_type
FROM    sys.dm_tran_database_transactions AS dt
JOIN    sys.dm_tran_session_transactions  AS st ON st.transaction_id = dt.transaction_id
JOIN    sys.dm_exec_sessions              AS s  ON s.session_id = st.session_id
LEFT JOIN sys.dm_exec_requests            AS r  ON r.session_id = st.session_id
WHERE   dt.database_id = DB_ID()
ORDER BY dt.database_transaction_begin_time;

Compare the transaction_id values with oldest_active_transaction_id from Check 1. A session with status = sleeping and an open transaction is the classic culprit: an application that issued BEGIN TRAN and then waited on a user or a remote call.

Run the same query in every ADR-enabled database on the instance, not just the one that is growing.

#Check 3: long snapshot readers, here and on secondaries

SELECT  t.session_id,
        t.transaction_id,
        t.elapsed_time_seconds / 60 AS elapsed_minutes,
        s.program_name, s.host_name
FROM    sys.dm_tran_active_snapshot_database_transactions AS t
JOIN    sys.dm_exec_sessions AS s ON s.session_id = t.session_id
ORDER BY t.elapsed_time_seconds DESC;

On an Availability Group, run this on each readable secondary. A reporting query that runs for six hours on a secondary keeps versions on the primary for six hours. Snapshot isolation on secondaries is implicit, so the reader does not have to ask for it.

#Check 4: aborted transactions

SELECT * FROM sys.dm_tran_aborted_transactions;

A high current_aborted_transaction_count after a large failed batch, a killed index rebuild or a mass rollback means the logical revert cleaner has a backlog. ADR made the rollback instant, but the cleanup still has to happen.

To see whether the cleaner is working, look for its system task:

SELECT session_id, command, status, wait_type, cpu_time, total_elapsed_time
FROM   sys.dm_exec_requests
WHERE  command LIKE N'%VERSION%CLEAN%' OR command LIKE N'%PVS%';

Task names vary by version. Run without the filter and look for a background command that mentions version cleanup.

#Symptom → cause → fix

Warning

Killing a session with a large open transaction is fast under ADR, but the work is lost. Confirm with the application owner before you KILL a batch job that has been running for hours.

#Force a cleanup pass

Once the blocker is gone, you can start cleanup yourself instead of waiting for the next cycle:

EXEC sys.sp_persistent_version_cleanup N'SalesDB';

The procedure is synchronous and returns only when cleanup finishes. On a large PVS it can run for a long time and generate a lot of I/O. Run it in a quiet period from a session you can leave open. It does not help if the blocker is still there.

#Move the PVS to its own filegroup

By default the PVS lives in the PRIMARY filegroup. A separate filegroup on its own volume keeps a runaway PVS from filling the files your tables need, and makes it easy to alert on.

-- 1. Turn ADR off and wait for the PVS to empty
ALTER DATABASE SalesDB SET ACCELERATED_DATABASE_RECOVERY = OFF;

SELECT persistent_version_store_size_kb
FROM   sys.dm_tran_persistent_version_store_stats
WHERE  database_id = DB_ID(N'SalesDB');   -- repeat until 0

-- 2. Add the filegroup and file
ALTER DATABASE SalesDB ADD FILEGROUP VersionStoreFG;
ALTER DATABASE SalesDB ADD FILE
  (NAME = N'SalesDB_pvs', FILENAME = N'P:\SQLData\SalesDB_pvs.ndf',
   SIZE = 20GB, FILEGROWTH = 2GB)
  TO FILEGROUP VersionStoreFG;

-- 3. Turn ADR back on, pointing at the new filegroup
ALTER DATABASE SalesDB SET ACCELERATED_DATABASE_RECOVERY = ON
  (PERSISTENT_VERSION_STORE_FILEGROUP = VersionStoreFG);

Caution

Changing the ADR setting needs an exclusive lock on the database and waits behind active work. While ADR is off you lose fast rollback and fast recovery. Do this in a maintenance window. On an AG, run it on the primary and check that secondaries replay it.

#Prevent it next time

• Alert on PVS size as a share of the data file size. A SQL Agent job that reads sys.dm_tran_persistent_version_store_stats every 5 minutes and raises an error above a threshold is enough.
• Alert on transaction age. Any transaction open for more than 30 minutes in an ADR database should page someone. It is the main cause of PVS growth.
• Cap readers on secondaries. Use Resource Governor or query timeouts for reporting tools that connect through read-only routing.
• Size for the PVS. Leave headroom in the filegroup for your longest legitimate transaction or report window, measured from the DMV during a normal peak.
• Watch large rollbacks. After a failed bulk load or index rebuild, check current_aborted_transaction_count and plan the cleanup before the next batch run.
• Think before enabling ADR on very large databases. Older Microsoft guidance warns against databases above about 100 TB on 2019 because of the single-threaded cleaner. Check the current guidance for your version.