| title | ADR cleaner retry timeout (min) (server configuration option) | |
|---|---|---|
| description | Explains the SQL Server instance configuration setting for ADR cleaner retry timeout. | |
| author | MikeRayMSFT | |
| ms.author | mikeray | |
| ms.reviewer | randolphwest | |
| ms.date | 05/16/2023 | |
| ms.service | sql | |
| ms.subservice | configuration | |
| ms.topic | conceptual | |
| helpviewer_keywords |
|
[!INCLUDE SQL Server]
[!INCLUDE sssql19-starting-md], this configuration setting is required for accelerated database recovery (ADR). The cleaner is the asynchronous process that wakes up periodically and cleans page versions that aren't needed.
Occasionally the cleaner runs into issues while acquiring object level locks due to conflicts with user workload during its sweep. It tracks such pages in a separate list. ADR cleaner retry timeout (min) controls the amount of time the cleaner would spend exclusively retrying object lock acquisition and cleanup of page before abandoning the sweep. Completion of a sweep with 100 percent success is essential to keep the growth of aborted transactions in the aborted transactions map. If the separate list can't be cleaned up in the prescribed timeout, then the current sweep will be abandoned and the next sweep will start.
| Version | Default value |
|---|---|
| [!INCLUDE sssql19-md] | 120 |
| [!INCLUDE sssql22-md] and later versions | 15 |
The cleaner is single threaded in [!INCLUDE sssql19-md], and so one [!INCLUDE ssnoversion-md] instance can work on one database at a time. If the instance has more than one user database with ADR enabled, then don't increase the timeout to a large value. Doing so could delay cleanup on one database while the retry is happening on another database.
::: moniker range="= sql-server-linux-ver15 || = sql-server-ver15"
For [!INCLUDE sssql19-md] CU 12 and previous versions, this value may be set to 0. We recommend that you manually reset the value to 120, which is the designed default, using the example in this article.
The following example sets the cleaner retry timeout to the default value.
sp_configure 'show advanced options', 1;
RECONFIGURE;
GO
sp_configure 'ADR cleaner retry timeout', 120;
RECONFIGURE;
GO::: moniker-end
::: moniker range=">= sql-server-linux-ver16 || >= sql-server-ver16"
The following example sets the cleaner retry timeout to the default value.
sp_configure 'show advanced options', 1;
RECONFIGURE;
GO
sp_configure 'ADR cleaner retry timeout', 15;
RECONFIGURE;
GO::: moniker-end