Note
Access to this page requires authorization. You can try signing in or changing directories.
Access to this page requires authorization. You can try changing directories.
Applies to: 
SQL Server 2019 (15.x) and later versions
Azure SQL Database
Azure SQL Managed Instance
SQL database in Microsoft Fabric
Manually starts persistent version store (PVS) cleanup process, a key element of accelerated database recovery (ADR). The cleaner removes obsolete row versions from the in-row and off-row PVS storage, and also removes uncommitted changes in PVS from aborted transactions.
It isn't typically necessary to start the PVS cleanup process manually using sys.sp_persistent_version_cleanup. However in some scenarios, you might want to initiate the PVS cleanup process manually during a known period of rest/recovery after busy OLTP activity.
For more information about ADR, see Accelerated database recovery.
Transact-SQL syntax conventions
Syntax
sp_persistent_version_cleanup
[ [ @dbname = ] N'dbname' ]
[ , [ @scanallpages = ] scanallpages ]
[ , [ @clean_option = ] clean_option ]
[ ; ]
Arguments
[ @dbname = ] N'dbname'
Optional. The name of the database to clean up. If not provided, uses the current database context. @dbname is sysname, with a default of NULL.
[ @scanallpages = ] scanallpages
Optional. @scanallpages is bit, with a default of 0. When set to 1, this option forces clean up of all database pages even if not versioned.
[ @clean_option = ] clean_option
Optional. The option to determine the type of cleanup to perform. @clean_option is int, with a default of 0. This parameter isn't commonly needed and the default value 0 is recommended.
| Value | Description |
|---|---|
0 |
Default, perform all cleanup. |
1 |
Clean up the off-row version storage without examining PVS page contents. Faster, but might skip deallocation of unused pages in PVS. This is the default behavior when @clean_option is omitted or set to 0. |
2 |
Clean up the off-row version storage by checking each PVS page contents. Slower, but always deallocates all unused pages. |
3 |
Clean up the in-row version storage only. |
4 |
Clean up row versions generated by aborted transactions only (also known as logical revert). |
Return code values
0 (success) or 1 (failure).
Result set
None.
Permissions
Requires the ALTER permission on the database.
Remarks
The sys.sp_persistent_version_cleanup stored procedure is synchronous, meaning that it doesn't complete until all version information is cleaned up from the current PVS.
In SQL Server 2019 (15.x), the PVS cleanup process only executes for one database at a time. In Azure SQL Database and Azure SQL Managed Instance, and beginning with SQL Server 2022 (16.x), the PVS cleanup process can execute in parallel against multiple databases in the same instance.
If the PVS cleanup process is already running against the desired database, this stored procedure is blocked before starting another PVS cleanup process. Active, long-running transactions in any database on the same database engine instance that have ADR enabled can also block PVS cleanup.
You can monitor the version cleaner task by looking for its process with the following sample query:
SELECT *
FROM sys.dm_exec_requests
WHERE command LIKE '%PERSISTED_VERSION_CLEANER%';
An active transaction might prevent the PVS cleanup process from starting. If this occurs, the session running the sys.sp_persistent_version_cleanup stored procedure waits with the PVS_CLEANUP_LOCK wait type. You can wait for the transaction to complete, or you can consider killing the blocker session with an active transaction, if possible.
If ADR is disabled, run sys.sp_persistent_version_cleanup to clean up previous versions still in the PVS.
Examples
To activate the PVS cleanup process manually between workloads or during maintenance windows, use the following sample script:
EXECUTE sys.sp_persistent_version_cleanup [database_name];
For example:
EXECUTE sys.sp_persistent_version_cleanup [WideWorldImporters];
Or, to assume the current database context:
USE [WideWorldImporters];
GO
EXECUTE sys.sp_persistent_version_cleanup;