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

by

Troubleshooting Guide: Error 12139 – Optimized Halloween Protection Conflict with Accelerated Database Recovery

Why This Error Happens

This error occurs because Accelerated Database Recovery (ADR) and Optimized Halloween Protection (OHP) are mutually exclusive features in SQL Server 2025, and OHP is currently active on the database. OHP relies on specific internal row-versioning and query processing optimizations that conflict with the persisted version store architecture used by ADR. SQL Server blocks the configuration change to prevent inconsistent transaction log recovery behavior until one of the two features is explicitly disabled.

Step-by-Step Resolution Guide

Step 1: Verify Current Database Configuration

  • Open SQL Server Management Studio (SSMS) or Azure Data Studio connected to your SQL Server 2025 instance.
  • Run the following query to check current ADR and OHP status:
SELECT name, is_accelerated_database_recovery_on 
FROM sys.databases 
WHERE name = 'YourDatabaseName';
  • Check Optimized Halloween Protection status via Database Scoped Configurations:
SELECT name, value, value_for_secondary 
FROM sys.database_scoped_configurations 
WHERE name = 'OPTIMIZED_HALLOWEEN_PROTECTION';

Step 2: Disable Optimized Halloween Protection

  • Execute the following T-SQL command to turn off OHP at the database scope:
ALTER DATABASE SCOPED CONFIGURATION SET OPTIMIZED_HALLOWEEN_PROTECTION = OFF;
  • Ensure no active transactions are running against the target database before applying this change.
  • If running on a Windows Server 2025 failover cluster, apply this change on the primary replica only.

Step 3: Enable Accelerated Database Recovery

  • Once OHP is confirmed disabled, set the database to single-user mode temporarily to avoid conflicts:
ALTER DATABASE YourDatabaseName SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
  • Enable ADR with the following command:
ALTER DATABASE YourDatabaseName SET ACCELERATED_DATABASE_RECOVERY = ON;
  • Return the database to multi-user mode:
ALTER DATABASE YourDatabaseName SET MULTI_USER;

Step 4: Restart SQL Server Service (If Required)

  • Open Services.msc on Windows Server 2025 / Windows 11.
  • Locate “SQL Server (MSSQLSERVER)” or your named instance.
  • Right-click and select Restart to ensure configuration changes are fully applied at the engine level.

Alternative or Verification Steps

  • Re-run the verification query from Step 1 to confirm is_accelerated_database_recovery_on returns 1 and OHP returns 0 or OFF.
  • If the error persists, check for active persistent version store (PVS) cleanup tasks using sys.dm_tran_persistent_version_store_stats and wait for cleanup to complete before retrying Step 2.