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

by

Troubleshooting Guide: Error 12456 – Query Cannot Be Removed from Query Store Due to Query Hints

Why This Error Happens

This error occurs because SQL Server 2025’s Query Store maintains a dependency between a query and any query hints applied to it via sp_query_store_set_hints or through automated plan correction features. When an attempt is made to manually clean up, flush, or remove a specific query ID from the Query Store using sp_query_store_remove_query, SQL Server blocks the operation if active hints are still bound to that query ID. This is a protective mechanism to prevent orphaned hint metadata from existing without a parent query reference, ensuring the internal consistency of the query_store_query_hints system view.

Step-by-Step Resolution Guide

Step 1: Identify the Query ID and Existing Hints

  • Open SQL Server Management Studio (SSMS) 21 or Azure Data Studio connected to your SQL Server 2025 instance.
  • Run a query against the sys.query_store_query_hints catalog view to confirm the hints associated with the problematic Query ID:
  SELECT * FROM sys.query_store_query_hints WHERE query_id = <your_query_id>;
  • Note the query_hint_id and the specific hint text (e.g., OPTION (MAXDOP 4)) returned by this query.

Step 2: Clear the Specific Query Hints

  • Execute the system stored procedure sp_query_store_clear_hints to remove the hint binding specifically for the identified Query ID.
  • Use the following syntax in a New Query window targeting the correct database context:
  EXEC sp_query_store_clear_hints @query_id = <your_query_id>;
  • Verify the command completes without returning rows when you re-run the SELECT statement from Step 1.

Step 3: Retry the Query Removal

  • Once the hints are confirmed cleared, re-attempt the original removal command that triggered Error 12456.
  • Run the following command to remove the query and its associated runtime statistics:
  EXEC sp_query_store_remove_query @query_id = <your_query_id>;
  • If the command executes successfully, the Query Store garbage collection process (or sp_query_store_force_flush if you require immediate consistency) will finalize the removal of the metadata.

Step 4: Check for Automatic Tuning Interference

  • If the hint reappears immediately after clearing (specifically on Windows Server 2025 instances utilizing high-availability features), check if Automatic Tuning is enabled, as it may be re-applying a LAST_QUERY_HINT automatically.
  • Navigate to Database Properties > Automatic Tuning in SSMS, or run the T-SQL check below to disable automatic plan correction if it is interfering with manual cleanup:
  ALTER DATABASE CURRENT SET AUTOMATIC_TUNING (FORCE_LAST_GOOD_PLAN = OFF);

Alternative or Verification Steps

  • Verification: Query sys.query_store_query joined with sys.query_store_query_hints to ensure the specific query_id no longer appears in either table, confirming a clean removal.
  • Alternative Fix: If sp_query_store_clear_hints fails due to a locking conflict, restart the SQL Server service via the Services app in Windows Server 2025 (ensure no active transactions are running) to release any internal metadata locks on the Query Store schema before retrying the removal.