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

by

Troubleshooting Guide: SQL Server Error 12451 — Query Store Configuration Failure

Why This Error Happens

Error 12451 occurs when SQL Server attempts to update a Query Store configuration option (such as OPERATION_MODE, MAX_STORAGE_SIZE_MB, or QUERY_CAPTURE_MODE) but fails to persist the change to the database’s internal metadata tables. This is typically caused by the Query Store catalog being in a corrupted or inconsistent state, insufficient disk space in the database files to write updated configuration records, or the database being in a read-only, restricted, or emergency state that blocks the ALTER DATABASE SET operation. In rarer cases, this stems from an in-progress Query Store internal cleanup or compaction task holding a conflicting lock on the configuration tables.

Step-by-Step Resolution Guide

Step 1: Verify Database State and Permissions

  • Run SELECT name, state_desc, is_read_only FROM sys.databases WHERE name = 'YourDatabaseName'; to confirm the database is ONLINE and not READ_ONLY.
  • Ensure the executing login has ALTER permission on the database or is a member of the db_owner role.
  • If the database is read-only, run ALTER DATABASE [YourDatabaseName] SET READ_WRITE; before retrying the Query Store command.

Step 2: Check Available Disk Space and File Growth Settings

  • Confirm the drive hosting the data (.mdf) and log (.ldf) files has sufficient free space (at least 500 MB recommended as a buffer).
  • Use EXEC sp_spaceused; within the target database to check current allocation versus used space.
  • If autogrowth is disabled or capped, adjust file growth settings via SQL Server Management Studio (SSMS) → Database Properties → Files, or run:
  ALTER DATABASE [YourDatabaseName] MODIFY FILE (NAME = N'YourLogicalFileName', MAXSIZE = UNLIMITED);

Step 3: Reset or Repair the Query Store Catalog

  • If the Query Store metadata appears corrupted, disable and clear it:
  ALTER DATABASE [YourDatabaseName] SET QUERY_STORE = OFF;
  • Wait 10–15 seconds, then re-enable it:
  ALTER DATABASE [YourDatabaseName] SET QUERY_STORE = ON (OPERATION_MODE = READ_WRITE);
  • If the error persists, run ALTER DATABASE [YourDatabaseName] SET QUERY_STORE CLEAR; after setting operation mode to READ_WRITE to purge existing data and metadata.

Step 4: Restart the SQL Server Service

  • On Windows Server 2025 / Windows 11, open Services.msc, locate SQL Server (MSSQLSERVER) or your named instance, and restart it.
  • Alternatively, use PowerShell as Administrator:
  Restart-Service -Name 'MSSQLSERVER' -Force
  • This clears any stale internal locks or cached Query Store state held in memory.

Step 5: Review SQL Server Error Logs for Underlying I/O Errors

  • Open the SQL Server Error Log via SSMS → Management → SQL Server Logs, or check the log file directly at:
    C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG
  • Look for accompanying I/O or storage engine errors (e.g., Error 823, 824, or 825) that may indicate underlying disk corruption requiring DBCC CHECKDB.

Alternative or Verification Steps

  • After applying the fix, confirm success by running SELECT actual_state_desc, desired_state_desc FROM sys.database_query_store_options; — both should reflect the intended state (e.g., READ_WRITE).
  • If the error continues, run DBCC CHECKDB('YourDatabaseName') WITH NO_INFOMSGS, ALL_ERRORMSGS; to rule out deeper database corruption requiring a restore from backup.