How to fix Error 12142 on Windows Server 2025 / Windows 11

by

Troubleshooting Guide: Error 12142 – Automatic Index Compaction Requires Accelerated Database Recovery

Why This Error Happens

This error occurs because Automatic Index Compaction is a feature in SQL Server 2025 that depends entirely on the Persistent Version Store (PVS) architecture provided by Accelerated Database Recovery (ADR). Without ADR enabled on the target database, SQL Server has no mechanism to track row versioning data required to safely compact indexes in the background. This is a database-level configuration gap rather than a corruption or permissions issue, and it typically happens when a database was migrated or restored from an earlier SQL Server version where ADR was off by default.

Step-by-Step Resolution Guide

Step 1: Verify Current ADR Status

  • Open SQL Server Management Studio (SSMS) or Azure Data Studio and connect to your SQL Server 2025 instance.
  • Run the following query to check the current state:
SELECT name, is_accelerated_database_recovery_on 
FROM sys.databases 
WHERE name = 'YourDatabaseName';
  • A result of 0 confirms ADR is disabled and is the direct cause of Error 12142.

Step 2: Enable Accelerated Database Recovery

  • Ensure no long-running transactions are active on the target database, as enabling ADR requires a brief exclusive lock.
  • Execute the following T-SQL command, replacing the placeholder with your actual database name:
ALTER DATABASE [YourDatabaseName] SET ACCELERATED_DATABASE_RECOVERY = ON;
  • Wait for the command to complete; this creates the Persistent Version Store (PVS) in the user database automatically.

Step 3: Enable Automatic Index Compaction

  • Once ADR is confirmed active, enable the Index Compaction feature using the database-scoped configuration:
ALTER DATABASE SCOPED CONFIGURATION SET AUTOMATIC_INDEX_COMPACTION = ON;
  • If using Windows Server 2025 Core installations, ensure you are running this via the sqlcmd utility if a GUI is unavailable:
sqlcmd -S localhost -Q "ALTER DATABASE SCOPED CONFIGURATION SET AUTOMATIC_INDEX_COMPACTION = ON;"

Step 4: Restart the SQL Server Service (If Required)

  • Navigate to Services.msc on your Windows Server 2025/Windows 11 machine.
  • Locate SQL Server (MSSQLSERVER) or your specific named instance.
  • Right-click and select Restart to ensure the Database Engine fully reinitializes the PVS cleaner background thread required for compaction.

Alternative or Verification Steps

  • Verification: Re-run the sys.databases query from Step 1; the is_accelerated_database_recovery_on column should now return 1, and the Error 12142 should no longer appear in the SQL Server Error Log (viewable via SSMS > Management > SQL Server Logs).
  • Alternative: If ADR cannot be enabled due to active replication or mirroring configurations, you must temporarily pause the Log Shipping or Always On Availability Group synchronization before retrying the ALTER DATABASE command in Step 2.