Troubleshooting Guide: SQL Server Error 12510 – Table Clone Feature Not Supported

Why This Error Happens

Error 12510 occurs when the DBCC CLONEDATABASE command encounters a table or database feature that the table clone engine does not know how to replicate into the schema-only clone. This typically happens with newer or specialized features—such as certain memory-optimized table configurations, specific columnstore index variants, or newly introduced SQL Server 2025 features—that have not been fully integrated into the cloning subsystem. It can also surface when the source database is in a mixed-compatibility state after an in-place upgrade, leaving metadata that the clone operation cannot interpret correctly.

Step-by-Step Resolution Guide

Step 1: Identify the Unsupported Feature and Object

• Run the DBCC CLONEDATABASE command again and capture the full error message, including the %.*ls placeholder text, which names the specific feature.
• Query sys.tables, sys.indexes, and sys.columns on the source database to locate objects using features like In-Memory OLTP, Stretch Database, or specific Columnstore configurations.
• Cross-reference the feature name against the Microsoft Docs “Limitations of DBCC CLONEDATABASE” article for SQL Server 2025 to confirm it is a known exclusion.

Step 2: Remove or Isolate the Unsupported Object Before Cloning

• Script out the unsupported table or index definition using SSMS (Generate Scripts wizard) for later reference.
• Temporarily drop or disable the specific feature (e.g., disable a memory-optimized filegroup reference or an unsupported index type) on a copy of the database, not the production source.
• Re-run DBCC CLONEDATABASE ('SourceDB', 'ClonedDB'); against the modified copy to confirm the clone completes successfully without that object.

Step 3: Update SQL Server to the Latest Cumulative Update

• Open SQL Server Configuration Manager and verify the current build version under SQL Server Services.
• Download and install the latest Cumulative Update (CU) for SQL Server 2025 from the Microsoft Update Catalog, as clone engine compatibility fixes are frequently patched in CUs.
• After patching, restart the MSSQLSERVER service via services.msc or run net stop MSSQLSERVER && net start MSSQLSERVER from an elevated Command Prompt.

Step 4: Rebuild Affected Indexes or Convert Unsupported Table Types

• For Columnstore or In-Memory OLTP conflicts, convert the table to a standard rowstore/disk-based table using ALTER TABLE syntax if business requirements allow.
• Rebuild any fragmented or legacy-format indexes using ALTER INDEX ALL ON [TableName] REBUILD; to ensure metadata aligns with SQL Server 2025 clone requirements.
• Validate database compatibility level using ALTER DATABASE [SourceDB] SET COMPATIBILITY_LEVEL = 170; to ensure it matches the SQL Server 2025 engine level.

Alternative or Verification Steps

• Verify the fix by executing DBCC CLONEDATABASE ('SourceDB', 'VerifyClone') WITH VERIFY_CLONEDB; and confirm it completes with a success message and no Error 12510.
• If the unsupported feature cannot be removed (e.g., it is business-critical), use a traditional backup-and-restore or Export Data-tier Application (BACPAC) method instead of DBCC CLONEDATABASE to capture the schema and statistics for troubleshooting purposes.

Troubleshooting Guide: Error 12507 – Unsupported Index Option in CREATE TABLE AS SELECT (CTAS)

Why This Error Happens

Error 12507 occurs when a CREATE TABLE AS SELECT (CTAS) statement includes an index option—such as a specific index type, fill factor, or compression setting—that is not compatible with the distribution model or storage engine being targeted. This is common in distributed SQL environments (like Analytics Platform System or Azure Synapse-style syntax now present in SQL Server 2025) where CTAS has stricter limitations than a standard CREATE TABLE/CREATE INDEX combination. The root cause is almost always a syntax mismatch between the WITH clause options and what CTAS natively supports in this version.

Step-by-Step Resolution Guide

Step 1: Review the CTAS Syntax and Index Clause

• Open the failing query in SQL Server Management Studio (SSMS) or Azure Data Studio.
• Check the WITH clause for unsupported options such as CLUSTERED COLUMNSTORE INDEX combined with incompatible distribution types, or invalid FILLFACTOR values.
• Confirm which index types are valid for CTAS by running: SELECT * FROM sys.dm_exec_valid_index_options; (new DMV introduced in SQL Server 2025).

Step 2: Separate Index Creation from Table Creation

• Remove the unsupported index option directly from the CTAS statement.
• Example corrected pattern:

  CREATE TABLE dbo.NewTable
  WITH (DISTRIBUTION = ROUND_ROBIN, HEAP)
  AS SELECT * FROM dbo.SourceTable;

• Run a separate CREATE INDEX statement afterward to apply the specific index type:

  CREATE CLUSTERED COLUMNSTORE INDEX IX_NewTable ON dbo.NewTable;

Step 3: Update Database Compatibility Level

• Navigate to SSMS → Right-click the database → Properties → Options.
• Ensure the Compatibility Level is set to 170 (SQL Server 2025) to access the latest CTAS index support.
• Alternatively, run:

  ALTER DATABASE [YourDB] SET COMPATIBILITY_LEVEL = 170;

Step 4: Apply the Latest Cumulative Update (CU)

• Go to Start > Settings > Apps > Installed Apps on Windows Server 2025/Windows 11 and verify the installed SQL Server build.
• Download and install the latest Cumulative Update from the Microsoft Update Catalog, as early 2025 builds had a known bug affecting WITH (DATA_COMPRESSION = …) inside CTAS.
• Restart the SQL Server service via services.msc after the update completes.

Alternative or Verification Steps

• Re-run the original CTAS query after removing the index clause to confirm table creation succeeds, then verify the separately created index using SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID('dbo.NewTable');.
• If the error persists, use CREATE TABLE with standard syntax first, then populate it using INSERT INTO ... SELECT instead of CTAS, bypassing the index-option restriction entirely.

Troubleshooting Guide: SQL Server Error 12508 – Cross-Database Table Clone Not Supported

Why This Error Happens

Error 12508 occurs when the CREATE TABLE ... AS CLONE OF statement references a source table located in a different database than the destination table. SQL Server’s table clone feature is architecturally restricted to operate within the boundaries of a single database context due to how it replicates internal statistics and schema metadata. This limitation is enforced regardless of whether both databases reside on the same instance, same server, or have matching compatibility levels.

Step-by-Step Resolution Guide

Step 1: Confirm the Source and Destination Database Context

• Check the fully qualified names used in your CREATE TABLE statement.
• Look for a pattern like SourceDB.dbo.TableName while running the query from DestinationDB.
• Run SELECT DB_NAME(); in your active query session to confirm which database context you are currently in.

Step 2: Rewrite the Query to Operate Within a Single Database

• Switch your session context to the source database using USE SourceDB; before executing the clone statement.
• Ensure both the destination table and source table reference live in that same USE context.
• Example corrected syntax:

  USE SourceDB;
  CREATE TABLE dbo.NewTable AS CLONE OF dbo.OriginalTable;

Step 3: Use an Alternate Migration Method for Cross-Database Scenarios

• If the clone must land in a different database, use SELECT INTO combined with sp_estimate_data_compression_savings to replicate structure manually.
• Alternatively, export statistics separately using DBCC SHOW_STATISTICS and import them into the new table after a standard cross-database INSERT INTO ... SELECT.
• Validate collation settings between SourceDB and DestinationDB since cross-database copies often fail silently on collation mismatches.

Step 4: Restart the Query Session and Clear Cached Plans

• Run DBCC FREEPROCCACHE; to eliminate any cached execution plan that may reference the invalid cross-database clone syntax.
• Re-run the corrected query from Step 2 to confirm Error 12508 no longer appears.

Alternative or Verification Steps

• Verify the fix by querying sys.tables in the destination database to confirm the new table exists with the expected schema and row count matches the source.
• If cross-database cloning is a recurring business requirement, consider consolidating both tables into the same database or using an ETL tool (such as SSIS or Azure Data Factory) to replicate schema and statistics instead of relying on the native CLONE OF syntax.

Troubleshooting Guide: Error 12506 – Unsupported Distribution Option in CREATE TABLE

Why This Error Happens

Error 12506 occurs when a CREATE TABLE statement specifies a DISTRIBUTION clause (such as HASH, ROUND_ROBIN, or REPLICATE) that is not valid for the table type or edition of SQL Server 2025 being used. This typically happens when distribution syntax intended for Azure Synapse-style or distributed tables is used against a standard on-premises SQL Server instance that does not support that distribution model. It can also occur if the database compatibility level or edition (Standard vs. Enterprise) does not include support for the specified distribution architecture.

Step-by-Step Resolution Guide

Step 1: Verify Edition and Feature Support

• Confirm whether your installed SQL Server 2025 edition supports distributed/sharded tables (check via SELECT SERVERPROPERTY('Edition')).
• Review the official SQL Server 2025 documentation to confirm DISTRIBUTION clauses are only valid in specific deployment models (e.g., Big Data Clusters or Hyperscale-compatible configurations).
• If the feature is unsupported in your edition, remove the DISTRIBUTION clause from your query entirely.

Step 2: Correct the CREATE TABLE Syntax

• Open the query in SQL Server Management Studio (SSMS) or Azure Data Studio.
• Remove or replace the invalid distribution option, for example change:

  CREATE TABLE dbo.SalesData (...) WITH (DISTRIBUTION = HASH(CustomerID));

to a standard definition:

  CREATE TABLE dbo.SalesData (...);

• If distribution is required, ensure you are targeting a supported distributed database context (not a standard relational database).

Step 3: Check Database Compatibility Level

• Run the following to check current compatibility level:

  SELECT name, compatibility_level FROM sys.databases WHERE name = 'YourDatabase';

• If it is set below the level required for distribution features, update it:

  ALTER DATABASE YourDatabase SET COMPATIBILITY_LEVEL = 170;

• Re-run the CREATE TABLE statement after updating.

Step 4: Validate Server Configuration (If Using Clustered/Distributed Deployment)

• On Windows Server 2025, confirm the SQL Server instance is correctly configured as part of a Big Data Cluster or distributed topology, if applicable.
• Check service configuration under:
  Control Panel > Administrative Tools > Services > SQL Server (MSSQLSERVER)
• Ensure the instance type matches the deployment required for distributed table support.

Alternative or Verification Steps

• Verify the fix by successfully executing the CREATE TABLE statement without errors and confirming the table appears under Object Explorer > Databases > Tables in SSMS.
• If distribution functionality is genuinely required, consider migrating the workload to Azure SQL Hyperscale or Azure Synapse Analytics, which natively support DISTRIBUTION clauses.

Troubleshooting Guide: SQL Server Error 12504 — Distribution Column Missing in INSERT SELECT

Why This Error Happens

This error occurs because the target table in your INSERT...SELECT statement is a hash distributed table, and SQL Server’s distributed query engine requires the distribution column to be explicitly present in the result set of the SELECT statement to correctly route rows to the appropriate distribution. Without this column, the engine cannot determine which node or shard should physically store each row, so it blocks the operation to prevent data inconsistency. This typically happens when the SELECT clause uses SELECT * on a differently structured source table, uses column aliases that don’t match, or simply omits the distribution key column during a manual column list definition.

Step-by-Step Resolution Guide

Step 1: Identify the Distribution Column of the Target Table

• Run the following query to find the exact distribution column name referenced in the error message:

SELECT distribution_policy_desc, distribution_column_name 
FROM sys.tables WHERE object_id = OBJECT_ID('YourTargetTable');

• Note the exact column name (case-sensitive) and data type returned.

Step 2: Modify the INSERT Statement to Explicitly Include the Column

• Rewrite your query to explicitly list columns in both the INSERT and SELECT clauses rather than relying on SELECT *.
• Ensure the distribution column exists in the source table or source query result set.

INSERT INTO TargetTable (DistCol, Col2, Col3)
SELECT DistCol, Col2, Col3 FROM SourceTable;

Step 3: Verify Column Alias Mapping

• If the distribution column in the source table has a different name, use an explicit AS alias in the SELECT statement to map it correctly.
• Check for implicit type conversions; ensure the data type of the source column matches the distribution column’s defined type (e.g., INT vs BIGINT) to avoid silent casting issues.

Step 4: Check for Views or CTEs Stripping the Column

• If your SELECT source is a View or Common Table Expression (CTE), verify that the underlying definition does not filter out or rename the distribution column.
• Open the view definition via SQL Server Management Studio (SSMS) and confirm the column is passed through in the SELECT list of the view itself.

Alternative or Verification Steps

• Verification: Re-run the INSERT...SELECT statement after modification. If successful, query sys.dm_exec_query_stats or check the Query Store in SSMS to confirm the execution plan shows a direct move rather than a data redistribution error.
• Alternative: If modifying the query is not feasible due to application constraints, consider using a staging table with a matching distribution column and schema, then perform the INSERT...SELECT from the staging table into the target table.

Troubleshooting Guide: Error 12505 – Missing DISTRIBUTION Option in CREATE TABLE AS SELECT (CTAS)

Why This Error Happens

This error occurs because SQL Server 2025 requires that distributed table architecture be explicitly declared when using CTAS syntax, rather than inferring it from context or defaults. It typically surfaces when a query is migrated from a non-distributed environment (like standard on-prem SQL Server or an older version) where the DISTRIBUTION clause was optional or unsupported. The engine throws Error 12505 to prevent ambiguous data placement across nodes, as it cannot guess whether you intend a HASH, ROUND_ROBIN, or REPLICATE distribution model.

Step-by-Step Resolution Guide

Step 1: Identify the Offending Query

• Locate the specific CREATE TABLE ... AS SELECT statement referenced in the SQL Server error log or the client-side error message.
• Check the stack trace in SQL Server Management Studio (SSMS) or Azure Data Studio to pinpoint the exact line number.
• Note the source table structure to determine the best distribution key.

Step 2: Determine the Correct Distribution Strategy

• Decide between HASH, ROUND_ROBIN, or REPLICATE based on the table size and join patterns.
• For large fact tables, use HASH with a high-cardinality column (e.g., CustomerID) to minimize data movement.
• For small dimension tables, use REPLICATE to copy the data across all compute nodes.

Step 3: Modify the CTAS Syntax

• Update the query to explicitly include the WITH (DISTRIBUTION = ...) clause.
• Example syntax to insert into your script:

  CREATE TABLE dbo.SalesData
  WITH (DISTRIBUTION = HASH(CustomerID))
  AS SELECT * FROM dbo.Staging_Sales;

• Ensure the clause is placed immediately after the table name and before the AS SELECT statement.

Step 4: Validate Server Configuration Defaults

• Open SQL Server Configuration Manager and check if the instance is running in “Distributed Compatibility Mode.”
• Run DBCC TRACEON (12505, -1) temporarily if you need to bypass strict validation for legacy migration scripts (not recommended for production).
• Restart the SQL Server service via services.msc on Windows Server 2025 for configuration changes to take effect.

Step 5: Re-deploy the Table Script

• Drop the failed temporary object if it was partially created: DROP TABLE IF EXISTS dbo.SalesData;
• Re-run the corrected script with the explicit distribution clause applied.

Alternative or Verification Steps

• Verification: Run SELECT * FROM sys.pdw_table_distribution_properties WHERE object_id = OBJECT_ID('dbo.SalesData'); to confirm the distribution method was successfully applied to the new table.
• Alternative: If you do not know the optimal distribution key, use WITH (DISTRIBUTION = ROUND_ROBIN) as a safe default to get the query running immediately, then optimize the key later based on query performance metrics.

Why This Error Happens

Error 12502 occurs because CTAS and CETAS statements in SQL Server 2025 do not support explicit column definitions or data type declarations within the CREATE TABLE clause itself; instead, data types must be inferred directly from the SELECT statement’s source columns or explicit CAST/CONVERT functions. This typically happens when a user migrates legacy T-SQL scripts (which defined schemas manually) to the newer CTAS/CETAS syntax without refactoring the column list. Additionally, this error can trigger if the underlying PolyBase or Data Virtualization engine cannot resolve an ambiguous data type from an external source (like a CSV or Parquet file) because no implicit conversion path exists.

Step-by-Step Resolution Guide

Step 1: Remove Explicit Column Definitions

• Open the T-SQL script causing the failure in SQL Server Management Studio (SSMS) or Azure Data Studio.
• Locate the CREATE TABLE [dbo].[NewTable] (Column1 INT, Column2 VARCHAR(50)) syntax immediately following the table name.
• Delete the parenthetical column list entirely, as CTAS does not allow you to pre-define the schema; the schema is derived automatically from the query results.

Step 2: Apply Data Types via the SELECT Statement

• Since you cannot define types in the header, modify the body of the SELECT statement to enforce the correct schema.
• Use explicit casting functions for every column where the source type is ambiguous or incorrect:
• SELECT CAST(OrderID AS INT), CONVERT(DECIMAL(10,2), Price) FROM Staging.Orders
• Ensure every column in the SELECT list has an explicit alias if you are using an expression or function (e.g., CAST(Price AS MONEY) AS Price).

Step 3: Verify External File Format Compatibility (CETAS Specific)

• If the error occurs specifically during CREATE EXTERNAL TABLE AS SELECT, check the WITH clause for the DATA_SOURCE and FILE_FORMAT.
• Navigate to Object Explorer > Databases > External Resources > File Formats to confirm the Parquet or CSV format matches the data being read.
• Run DBCC TRACEON(3604) to output detailed schema inference logs to the console, helping identify which specific column is triggering the ambiguous type error.

Step 4: Update SQL Server Compatibility Level

• Ensure the database is running under a compatibility level that fully supports the enhanced CTAS/CETAS engine introduced in SQL Server 2025.
• Execute the following command to check and set the level:
• ALTER DATABASE [YourDB] SET COMPATIBILITY_LEVEL = 170;
• Restart the SQL Server service via the Services app (services.msc) on Windows Server 2025 if the change does not apply immediately.

Alternative or Verification Steps

• Verification: Run sp_help 'NewTableName' or query INFORMATION_SCHEMA.COLUMNS immediately after the CTAS execution to confirm the inferred data types match your business requirements (e.g., ensuring an INT wasn’t accidentally inferred as BIGINT).
• Alternative: If type inference continues to fail due to a messy external data source, create the table manually using a standard CREATE TABLE statement with explicit types first, and then use a standard INSERT INTO ... SELECT statement to populate the data, bypassing the CTAS schema-inference engine entirely.

Troubleshooting Guide: Error 12503 – “Specifying Schema Elements in CREATE SCHEMA Statement Is Not Supported”

Why This Error Happens

This error occurs because the CREATE SCHEMA statement in SQL Server does not allow combining schema-scoped object creation (tables, views, grants) with certain unsupported syntax structures or incompatible compatibility-level settings. It is often triggered when a script written for a different SQL dialect (such as using multiple CREATE TABLE/CREATE VIEW statements inside a single CREATE SCHEMA block) is executed against a database still running under an older compatibility level that doesn’t parse the nested elements correctly. Additionally, this can happen if the database engine instance has a corrupted or outdated system catalog view that fails to validate the nested schema elements properly.

Step-by-Step Resolution Guide

Step 1: Verify Database Compatibility Level

• Open SQL Server Management Studio (SSMS) and connect to your Windows Server 2025 instance.
• Run the following query to check the current compatibility level:

  SELECT name, compatibility_level FROM sys.databases WHERE name = 'YourDatabaseName';

• Ensure the compatibility level is set to 160 or higher (required for SQL Server 2025 schema features):

  ALTER DATABASE YourDatabaseName SET COMPATIBILITY_LEVEL = 160;

Step 2: Correct the CREATE SCHEMA Syntax

• Remove any unsupported nested DML/DDL combinations from your script.
• Ensure only CREATE TABLE, CREATE VIEW, GRANT, DENY, or REVOKE statements are included, and that they strictly follow this structure:

  CREATE SCHEMA Sales
      CREATE TABLE Orders (OrderID INT PRIMARY KEY)
      GRANT SELECT ON SCHEMA::Sales TO PublicRole;

• Do not place a semicolon between the nested elements inside the CREATE SCHEMA block; only one terminating semicolon is allowed at the end of the entire statement.

Step 3: Validate Permissions and Server Configuration

• Confirm the executing login has CREATE SCHEMA and ALTER permissions on the target database:

  SELECT * FROM fn_my_permissions(NULL, 'DATABASE');

• On Windows Server 2025, check that the SQL Server service account has not been restricted by Group Policy or Windows Defender Application Control (WDAC) rules that block T-SQL parser extensions.
• Restart the SQL Server service via services.msc or Restart-Service MSSQLSERVER in PowerShell if permission changes were applied.

Step 4: Update SQL Server 2025 Cumulative Updates

• Navigate to Windows Update or the Microsoft Update Catalog to confirm the latest Cumulative Update (CU) for SQL Server 2025 is installed.
• Run SELECT @@VERSION; in SSMS to confirm the current build number matches the latest released patch.
• Apply any pending CU via the SQL Server Installation Center if the build is outdated, as early 2025 builds contained a known parser bug related to Error 12503.

Alternative or Verification Steps

• Re-run the corrected CREATE SCHEMA script in a test database to confirm the schema and nested objects are created without triggering Error 12503.
• If the error persists, split the script into two separate steps: first execute CREATE SCHEMA SchemaName; alone, then run individual CREATE TABLE/GRANT statements referencing SchemaName afterward.

Troubleshooting Guide: Error 12501 – Column Mismatch in CREATE TABLE/CREATE EXTERNAL TABLE and SELECT Query

Why This Error Happens

Error 12501 occurs when the number of columns defined in a CREATE TABLE or CREATE EXTERNAL TABLE statement does not match the number of columns returned by the associated SELECT query or underlying data source schema. This typically happens due to manual edits to table definitions, schema drift in the source data (such as an added or removed column in a CSV, Parquet file, or external data source), or an incorrectly written SELECT statement using SELECT * against a data source whose structure changed after the table definition was created. SQL Server 2025 validates column counts strictly during query compilation (specifically in PolyBase/external table scenarios and CTAS operations), so even a single extra or missing column triggers this failure.

Step-by-Step Resolution Guide

Step 1: Verify the Column Count in Both Statements

• Open the failing script in SQL Server Management Studio (SSMS) or Azure Data Studio on your Windows Server 2025/Windows 11 machine.
• Manually count the columns listed in the CREATE TABLE or CREATE EXTERNAL TABLE column definition list.
• Run the SELECT statement (or a SELECT TOP 1 *) independently to inspect the actual columns returned, using the Results Grid to count them.
• Check for trailing commas, duplicate column names, or commented-out column lines that could throw off the count.

Step 2: Inspect the External Data Source Schema (for CREATE EXTERNAL TABLE)

• If using CREATE EXTERNAL TABLE, navigate to the source file location (e.g., Azure Blob Storage, ADLS Gen2, or local file share referenced via EXTERNAL DATA SOURCE).
• Open the source file (CSV, Parquet, or ORC) and confirm the actual number of fields/columns match your WITH (LOCATION = ..., DATA_SOURCE = ...) definition.
• For CSV sources, check the FIELD_TERMINATOR and STRING_DELIMITER settings in your EXTERNAL FILE FORMAT object — malformed delimiters can cause SQL Server to misread the column count.
• Run sp_helpexternaltable 'dbo.YourExternalTableName'; to view the current cataloged schema definition.

Step 3: Align Column Definitions with the Query/Source

• Edit the CREATE TABLE or CREATE EXTERNAL TABLE statement so the column list exactly matches the number and order of columns in the SELECT query or source file.
• If using SELECT *, replace it with an explicit column list (e.g., SELECT Col1, Col2, Col3 FROM SourceTable) to prevent future drift from silently breaking the statement.
• Drop and recreate the table if it was partially created:

  DROP EXTERNAL TABLE dbo.YourExternalTableName;

• Re-run the corrected CREATE EXTERNAL TABLE or CREATE TABLE ... AS SELECT (CTAS) statement.

Step 4: Clear Cached Metadata and Restart Services (if issue persists)

• On Windows Server 2025, open Services.msc and restart the SQL Server (MSSQLSERVER) service, along with SQL Server PolyBase Engine and SQL Server PolyBase Data Movement services if external tables are involved.
• Clear any cached query plans related to the table using:

  DBCC FREEPROCCACHE;

• Re-attempt the CREATE TABLE/CREATE EXTERNAL TABLE statement.

Alternative or Verification Steps

• Confirm the fix by running SELECT * FROM dbo.YourNewTable; and verifying the result set column count matches your original source data without triggering Error 12501.
• As an alternative, use sys.columns to programmatically compare counts: SELECT COUNT(*) FROM sys.columns WHERE object_id = OBJECT_ID('dbo.YourTableName'); and cross-check against the source query’s column count before executing the full script again.

Troubleshooting Guide: SQL Server Error 12470 – Removing Plan Feedback is Disabled

Why This Error Happens

Error 12470 occurs when SQL Server’s Intelligent Query Processing (IQP) feature, specifically Parameter Sensitive Plan (PSP) or Plan Feedback, attempts to remove or invalidate a cached feedback entry while the database-level configuration disables this capability. This typically happens because the PLAN_FEEDBACK database-scoped configuration is explicitly set to OFF, or the compatibility level of the database does not support automatic plan correction. In some cases, Query Store itself may be in a read-only state or disabled, preventing the feedback mechanism from writing or removing entries as required.

Step-by-Step Resolution Guide

Step 1: Verify Query Store Status

• Open SQL Server Management Studio (SSMS) and connect to the target instance on Windows Server 2025 or Windows 11.
• Run the following query to check Query Store state:

  SELECT actual_state_desc, readonly_reason 
  FROM sys.database_query_store_options;

• If actual_state_desc returns READ_ONLY, proceed to re-enable write access in Step 2.

Step 2: Enable Query Store and Plan Feedback

• Execute the following T-SQL to ensure Query Store is active and writable:

  ALTER DATABASE [YourDatabaseName] SET QUERY_STORE = ON;
  ALTER DATABASE [YourDatabaseName] SET QUERY_STORE (OPERATION_MODE = READ_WRITE);

• Explicitly enable Plan Feedback at the database scope:

  ALTER DATABASE SCOPED CONFIGURATION SET PLAN_FEEDBACK = ON;

Step 3: Check Database Compatibility Level

• Confirm the database is running on compatibility level 160 or higher (required for advanced feedback features in SQL Server 2025).
• Run this command to check and update if necessary:

  ALTER DATABASE [YourDatabaseName] SET COMPATIBILITY_LEVEL = 160;

Step 4: Clear Corrupted Feedback Cache

• If the error persists, the internal feedback cache may be corrupted.
• Flush the Query Store and plan cache to force regeneration of feedback entries:

  ALTER DATABASE [YourDatabaseName] SET QUERY_STORE CLEAR;
  DBCC FREEPROCCACHE;

Step 5: Restart SQL Server Service

• Open the Services console (services.msc) on Windows Server 2025 / Windows 11.
• Locate SQL Server (MSSQLSERVER) or the named instance, right-click, and select Restart to apply the configuration changes fully.

Alternative or Verification Steps

• To verify the fix, re-run a query that previously triggered feedback invalidation and check sys.dm_exec_query_store_plan_feedback to confirm entries are being created and removed without throwing Error 12470.
• If the issue persists after configuration changes, consider dropping and recreating the Query Store catalog via ALTER DATABASE SET QUERY_STORE = OFF; followed by ON; to rebuild the underlying metadata tables from scratch.

Troubleshooting Guide: Error 12500 — SELECT INTO Not Allowed in CREATE TABLE / CREATE EXTERNAL TABLE

Why This Error Happens

Error 12500 occurs because SQL Server 2025 restricts the use of SELECT INTO syntax when targeting CREATE TABLE or CREATE EXTERNAL TABLE statements, particularly in PolyBase or external data virtualization scenarios. This typically happens when a query attempts to materialize results directly into a new external table definition, which is not a supported pattern in the T-SQL engine. It can also surface when database compatibility level settings or PolyBase configuration flags are misaligned with the syntax being used.

Step-by-Step Resolution Guide

Step 1: Identify the Offending Query Syntax

• Review the query that triggered the error and locate the SELECT ... INTO clause.
• Confirm whether the target is a CREATE EXTERNAL TABLE statement, since this combination is explicitly blocked.
• Check SQL Server logs at C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG for the exact statement context.

Step 2: Rewrite the Query Using Supported Syntax

• Replace SELECT INTO with a standard two-step process: first run CREATE TABLE (or CREATE EXTERNAL TABLE) explicitly defining columns and data types, then use INSERT INTO ... SELECT to populate it.
• For external tables, ensure the external data source and file format are already defined using CREATE EXTERNAL DATA SOURCE and CREATE EXTERNAL FILE FORMAT before creating the table.
• Example corrected pattern:

  CREATE EXTERNAL TABLE dbo.MyExternalTable (Col1 INT, Col2 VARCHAR(50))
  WITH (LOCATION='/data/', DATA_SOURCE=MyDataSource, FILE_FORMAT=MyFileFormat);

  INSERT INTO dbo.MyExternalTable
  SELECT Col1, Col2 FROM dbo.SourceTable;

Step 3: Verify Database Compatibility Level

• Open SQL Server Management Studio (SSMS) and run:

  SELECT compatibility_level FROM sys.databases WHERE name = 'YourDatabaseName';

• Ensure the compatibility level matches SQL Server 2025 standards (170) using:

  ALTER DATABASE YourDatabaseName SET COMPATIBILITY_LEVEL = 170;

• Restart the SQL Server service via services.msc on Windows Server 2025 or Windows 11 if changes do not take effect immediately.

Step 4: Check PolyBase Configuration (If Using External Tables)

• Confirm PolyBase is enabled by running:

  EXEC sp_configure 'polybase enabled', 1;
  RECONFIGURE;

• Restart the “SQL Server PolyBase Engine” service from the Services console (services.msc).
• Validate connectivity to the external data source using a test SELECT query before attempting table creation.

Alternative or Verification Steps

• Run the corrected CREATE TABLE / INSERT INTO sequence in a test database first to confirm no Error 12500 is raised, then apply it to production.
• If the error persists, use SSMS’s Query Execution Plan (Ctrl+L) to confirm the parser is not reintroducing an implicit SELECT INTO pattern through linked server or synonym references.

Troubleshooting Guide: SQL Server Error 12468 – “Removing Feedback for Feature_ID is Not Supported”

Why This Error Happens

This error occurs when an internal or external process attempts to invoke the sp_db_decrement_feature_usage system procedure (or the underlying Query Store/Database Engine feedback API) on a feature_id that is explicitly flagged as non-reversible in SQL Server 2025’s telemetry schema. Unlike standard feedback features where usage counters can be incremented and decremented freely, certain features—typically those tied to compliance, licensing, or Intelligent Query Processing (IQP) locks—are designed as “write-once” flags that can only be incremented, never removed, to maintain an accurate historical audit trail. This is frequently triggered by third-party monitoring tools, outdated maintenance scripts, or manual DBA scripts attempting to “reset” usage statistics for a feature that Microsoft has deprecated the removal capability for in this specific build.

Step-by-Step Resolution Guide

Step 1: Identify the Offending Script or Process

• Check the SQL Server Error Log (C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG) to identify the exact feature_id integer referenced in the error.
• Run the following query to cross-reference the ID against the system catalog:

SELECT * FROM sys.dm_db_feature_usage WHERE feature_id = <ID_FROM_LOG>;

• Determine if the call is coming from a SQL Agent Job, an ORM framework (like Entity Framework), or a manual sqlcmd execution.

Step 2: Update Deprecated Maintenance Scripts

• If you are using custom PowerShell or T-SQL scripts to “reset” feature flags, remove any explicit calls to sp_db_decrement_feature_usage.
• Navigate to your script repository (often located in D:\Scripts\SQLMaintenance\) and comment out or delete the DECREMENT logic block.
• Replace the logic with a SELECT check to simply monitor the status rather than attempting to alter it.

Step 3: Apply the Latest Cumulative Update (CU)

• Open PowerShell as Administrator and check your current build version:

SELECT @@VERSION;

• If the build number is lower than the latest GA release, download the latest Cumulative Update for SQL Server 2025 from the Microsoft Download Center.
• Install the update, ensuring the SQL Server service account has write permissions to the installation directory (C:\Program Files\Microsoft SQL Server\) to prevent installation rollbacks.

Step 4: Restart the SQL Server Service

• Open Services.msc on your Windows Server 2025 instance.
• Locate SQL Server (MSSQLSERVER), right-click, and select Restart to clear the cached execution plan that may be holding the erroneous feedback request in memory.
• Verify the service account has the necessary Log on as a service rights under Local Security Policy if the restart fails to initialize correctly.

Alternative or Verification Steps

• Verification: Re-run the triggering script or job. If successful, query sys.dm_db_feature_usage again to confirm the feature_id is still present (as expected) and that no error is thrown in the Activity Monitor.
• Alternative: If the error persists due to a corrupted Query Store, consider clearing the Query Store cache via ALTER DATABASE [YourDB] SET QUERY_STORE CLEAR; as a last resort, as this forces a reset of the feedback metadata tables entirely.

Troubleshooting Guide: SQL Server Error 12469 – “Removing Feedback for a Specific Plan is Not Supported”

Why This Error Happens

This error occurs when an application or administrator attempts to call sys.sp_query_store_remove_feedback or a similar Query Store feedback-removal procedure against a plan that does not support individual feedback removal, such as a plan associated with Automatic Plan Correction or a forced plan baseline created internally by the engine. In SQL Server 2025, certain Intelligent Query Processing (IQP) feedback mechanisms—like Cardinality Estimation Feedback or Memory Grant Feedback tied to a persisted plan—are managed as a unit at the query level rather than the individual plan level, so targeting a single plan_id for deletion is rejected by the engine. This can also surface if the Query Store is in a read-only state or if the feedback entry has already been consumed/invalidated by a background cleanup task, leaving a stale reference that the removal API refuses to process.

Step-by-Step Resolution Guide

Step 1: Verify the Query Store State and Permissions

• Confirm the database is not in READ_ONLY Query Store mode by running: SELECT actual_state_desc FROM sys.database_query_store_options;
• Ensure the executing login has db_owner or the explicit ALTER permission on the database, as insufficient rights can produce misleading behavior alongside Error 12469.
• Check Windows Server 2025 Event Viewer under Applications and Services Logs > MSSQLSERVER for correlating I/O or permission denial entries.

Step 2: Remove Feedback at the Query Level Instead of the Plan Level

• Since Error 12469 specifically blocks plan-level removal for unsupported feedback types, use the query-level cleanup procedure instead:
• Run: EXEC sys.sp_query_store_remove_query @query_id = <query_id>;
• Identify the correct query_id via: SELECT query_id, plan_id FROM sys.query_store_plan WHERE plan_id = <your_plan_id>;
• This clears all associated feedback entries tied to that query, bypassing the restriction on isolated plan feedback deletion.

Step 3: Clear Stale Feedback via Query Store Cleanup

• If the feedback entry is orphaned due to a background cleanup race condition, force a manual cleanup pass:
• Run: EXEC sys.sp_query_store_flush_db;
• Follow this with: ALTER DATABASE SCOPED CONFIGURATION SET AUTOMATIC_TUNING (FORCE_LAST_GOOD_PLAN = ON); temporarily disabled and re-enabled to reset the internal feedback tracking state.
• Restart the SQL Server (MSSQLSERVER) service via services.msc on Windows Server 2025 if the lock persists after cleanup.

Step 4: Disable and Re-enable the Specific Feedback Feature

• Navigate to Database Scoped Configurations and toggle the specific IQP feedback feature causing the conflict:
• ALTER DATABASE SCOPED CONFIGURATION SET MEMORY_GRANT_FEEDBACK_PERSISTENCE = OFF;
• Wait for active sessions to clear, then re-enable: ALTER DATABASE SCOPED CONFIGURATION SET MEMORY_GRANT_FEEDBACK_PERSISTENCE = ON;
• This forces the engine to regenerate feedback metadata cleanly, resolving orphaned plan associations.

Alternative or Verification Steps

• Verify the fix by querying sys.query_store_plan_feedback for the affected plan_id—it should return no rows if the feedback was successfully cleared at the query level.
• If the error persists, as a last resort, run EXEC sys.sp_query_store_clear_database; during a maintenance window to fully reset Query Store data, then allow the workload to repopulate plan feedback naturally.

Troubleshooting Guide: SQL Server Error 12467 – “The feature_id %d is invalid”

Why This Error Happens

Error 12467 typically occurs when SQL Server’s internal feature metadata tables (such as sys.dm_server_feature_events or related feature-tracking catalog views) encounter a reference to a feature_id that doesn’t exist in the current build’s feature registry. This usually stems from a corrupted system catalog, an incomplete or interrupted SQL Server 2025 update/patch installation, or a mismatch between the SQL Server engine version and an extended feature pack (such as PolyBase, Machine Learning Services, or Microsoft Fabric connectors) that registered an invalid ID during setup. In rare cases, this is triggered by third-party monitoring tools or extensions querying feature-tracking DMVs with outdated or malformed feature_id values.

Step-by-Step Resolution Guide

Step 1: Verify SQL Server Build and Patch Level

• Open SQL Server Management Studio (SSMS) and run SELECT @@VERSION; to confirm the exact build number.
• Cross-reference this build against the latest Cumulative Update (CU) for SQL Server 2025 on the Microsoft Update Catalog.
• If the instance is mid-patch or shows a mismatched build between the engine and shared components, proceed to reapply the latest CU fully.

Step 2: Repair the SQL Server Installation

• Launch SQL Server Installation Center from the Start Menu on Windows Server 2025 / Windows 11.
• Navigate to Maintenance > Repair, select the affected instance, and allow the repair wizard to complete.
• Alternatively, run from an elevated Command Prompt:

  Setup.exe /ACTION=Repair /INSTANCENAME=MSSQLSERVER /QUIET

• Restart the SQL Server (MSSQLSERVER) service afterward via services.msc.

Step 3: Rebuild or Refresh Feature Metadata Catalog

• Run DBCC CHECKDB('master') WITH NO_INFOMSGS, ALL_ERRORMSGS; to rule out master database corruption.
• If corruption is found in system catalog views, restore the master database from a known good backup taken before the error began.
• If no backup is available, consider rebuilding system databases using:

  Setup.exe /ACTION=RebuildDatabase /INSTANCENAME=MSSQLSERVER

Step 4: Check for Conflicting Feature Pack Installations

• Open Programs and Features in Windows 11 / Windows Server 2025 Control Panel.
• Look for partially installed components like PolyBase, Machine Learning Services, or Fabric Mirroring add-ons.
• Uninstall any feature showing “Repair needed” status, then reinstall it cleanly via the SQL Server Installation Center.

Step 5: Review SQL Server Error Log for Correlating Events

• Navigate to C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG.
• Search for entries immediately preceding Error 12467 to identify which feature module triggered the invalid ID.
• Use this context to target the specific feature component for reinstallation.

Alternative or Verification Steps

• After applying fixes, run SELECT * FROM sys.dm_server_feature_events; to confirm the query executes without throwing Error 12467.
• If the error persists, open an elevated PowerShell session and run Get-WinEvent -LogName Application | Where-Object {$_.Id -eq 12467} to capture additional context, then escalate to Microsoft Support with the full ERRORLOG and setup bootstrap logs from %ProgramFiles%\Microsoft SQL Server\170\Setup Bootstrap\Log.

Troubleshooting Guide: Error 12465 in Microsoft SQL Server 2025

Why This Error Happens

This error occurs because SQL Server 2025’s Query Store maintains a hierarchical dependency between parent queries and their associated query variants, a feature used to track parameterization-sensitive plans. When an attempt is made to clean up or remove a parent query via sp_query_store_remove_query or an automated cleanup policy, the engine blocks the deletion because one or more child query variants still hold a foreign-key-style reference to that parent query ID. This is a referential integrity safeguard designed to prevent orphaned query variant entries from remaining in the query_store_query_variant internal catalog views.

Step-by-Step Resolution Guide

Step 1: Identify the Parent Query and Its Variants

• Run a diagnostic query against sys.query_store_query_variant joined with sys.query_store_query to find all query_variant_query_id values linked to the parent query_id mentioned in the error message.
• Example T-SQL:

  SELECT parent_query_id, query_variant_query_id 
  FROM sys.query_store_query_variant 
  WHERE parent_query_id = <your_query_id>;

• Note down every query_variant_query_id returned, as these must be cleared first.

Step 2: Remove the Associated Query Variants

• Execute sp_query_store_remove_query individually for each query_variant_query_id identified in Step 1.
• Example command: EXEC sp_query_store_remove_query @query_id = <variant_id>;
• Do this for every child variant before attempting to touch the parent record again.

Step 3: Remove the Parent Query

• Once all child variants are confirmed removed (re-run the Step 1 query to ensure zero rows return), execute the removal command on the original parent query ID.
• Example command: EXEC sp_query_store_remove_query @query_id = <parent_query_id>;
• If this was part of an automated cleanup script (SQL Agent Job), ensure the script logic loops through variants before parents to avoid future recurrences.

Step 4: Check for Concurrent Plan Generation

• Verify that the query isn’t actively generating new variants due to ongoing workload execution on Windows Server 2025, as a new variant plan could be cached milliseconds after you clear the old ones.
• Temporarily pause the specific workload or session generating the variants if the removal continues to fail intermittently.

Alternative or Verification Steps

• Verification: Query sys.query_store_query using the original query_id; if the command succeeds and returns no rows, the removal was successful and the Query Store metadata is clean.
• Alternative: If manual removal of individual variants is too time-consuming due to a high volume of variants, consider using ALTER DATABASE SCOPED CONFIGURATION SET QUERY_OPTIMIZER_HOTFIXES = OFF temporarily or clearing the entire Query Store via ALTER DATABASE [YourDB] SET QUERY_STORE CLEAR;—note that this is a destructive action that wipes all historical query performance data for the database.

Troubleshooting Guide: SQL Server Error 12466 – Query Plan Referenced by Query Variant

Why This Error Happens

This error occurs because SQL Server 2025 introduces query variants in Query Store, a feature that groups parameterized queries under a single “dispatcher” query plan. When a specific query plan (identified by plan_id) acts as a dispatcher for one or more variants, Query Store enforces a referential integrity rule that prevents the deletion of that plan while dependencies exist. Attempting to force a cleanup, run sp_query_store_remove_plan, or manually purge the plan cache without first unlinking the child variants triggers this block to prevent orphaned metadata in the plan_persist_query_variant system view.

Step-by-Step Resolution Guide

Step 1: Identify the Dependent Query Variants

• Before attempting removal, query the internal Query Store views to find which variants are linked to the dispatcher plan causing the error.
• Run the following T-SQL command in SQL Server Management Studio (SSMS) connected to the affected database:

  SELECT * FROM sys.query_store_plan 
  WHERE plan_id = <Your_Plan_ID_Here>;

  SELECT * FROM sys.plan_persist_query_variant 
  WHERE dispatcher_plan_id = <Your_Plan_ID_Here>;

• Note down the query_variant_query_id values returned, as these must be addressed first.

Step 2: Remove the Child Variants Prior to the Dispatcher

• You cannot delete the “parent” dispatcher plan while children exist; you must clear the variants first.
• Execute the removal procedure specifically on the variant IDs gathered in Step 1:

  EXEC sp_query_store_remove_query @query_id = <Variant_Query_ID>;

• Repeat this command for every variant ID associated with the dispatcher plan to fully sever the dependency links.

Step 3: Clear the Dispatcher Plan

• Once all associated variants have been successfully removed from the Query Store, the reference lock on the dispatcher plan is released.
• Re-run the original removal command to clear the dispatcher plan itself:

  EXEC sp_query_store_remove_plan @plan_id = <Your_Plan_ID_Here>;

• If the error persists, check for replication latency between the Query Store cache and the on-disk catalog views by restarting the SQL Server Database Engine service via the Services console (services.msc) on your Windows Server 2025 host.

Step 4: Force a Query Store Flush (If Needed)

• In high-transaction environments, the internal background task may not have flushed the removal instantly, causing the error to reappear on retry.
• Force an immediate write-to-disk operation to ensure the catalog views are updated before Step 3 is re-attempted:

  EXEC sp_query_store_flush_db;

Alternative or Verification Steps

• Verification: Confirm the fix by re-querying sys.query_store_plan for the specific plan_id. If the SELECT statement returns zero rows, the dispatcher and its variants have been successfully purged.
• Alternative (Nuclear Option): If specific ID removal continues to fail due to a corrupted internal state, navigate to Database Properties > Query Store in SSMS and select Purge Query Data. Note: This clears all Query Store history for the database, so use this only if targeted removal is impossible.

Troubleshooting Guide: SQL Server Error 12463 – “Role ID Should Be Between (Including) 1 and 4”

Why This Error Happens

This error is thrown by SQL Server’s security subsystem when a server role or availability group role assignment references a role_id value that falls outside the valid internal range (1–4) used by system catalogs such as sys.server_principals or sys.availability_replicas. It typically occurs due to corrupted metadata in system tables, an incomplete upgrade/migration from a previous SQL Server version, or manual/scripted modification of role-related system views using unsupported T-SQL. In clustered or Availability Group (AG) configurations on Windows Server 2025, this can also surface when replica role synchronization becomes desynced after a failover.

Step-by-Step Resolution Guide

Step 1: Verify the Scope of the Corruption

• Open SQL Server Management Studio (SSMS) and connect to the affected instance.
• Run DBCC CHECKCATALOG('master') to confirm whether system catalog corruption is present.
• Check the SQL Server Error Log (C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG) for the exact object or query that triggered Error 12463.

Step 2: Inspect and Correct Role Assignments

• Query SELECT * FROM sys.server_principals WHERE type = 'R' to inspect current server roles.
• If using Availability Groups, run SELECT replica_id, role FROM sys.dm_hadr_availability_replica_states to check for an invalid role value outside 1–4.
• If a custom script or third-party tool recently modified role mappings, roll back that change using your last known-good database backup or AG configuration script.

Step 3: Restart the Affected Services

• Open Services.msc on Windows Server 2025 / Windows 11.
• Restart SQL Server (MSSQLSERVER) and SQL Server Agent services to clear any cached, invalid role-state data held in memory.
• If running in a Failover Cluster Instance (FCI), use Failover Cluster Manager to move the role to another node and back to force a clean re-initialization.

Step 4: Rebuild or Restore System Databases (If Corruption Persists)

• If DBCC CHECKCATALOG reports consistency errors, rebuild the master database using:
  Setup.exe /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=MSSQLSERVER /SQLSYSADMINACCOUNTS=<AdminAccount> /SAPWD=<Password>
• Alternatively, restore master and msdb from a verified backup taken before the error began occurring.

Alternative or Verification Steps

• After applying fixes, re-run SELECT * FROM sys.server_principals WHERE type = 'R' and confirm all role_id values fall strictly between 1 and 4.
• If the error persists only in an Availability Group context, remove and re-add the affected replica to the AG using ALTER AVAILABILITY GROUP [AGName] REMOVE REPLICA followed by ADD REPLICA to force a clean role re-registration.

Troubleshooting Guide: SQL Server Error 12464 – Query Plan Cannot Be Forced for Replica Group

Why This Error Happens

This error occurs when Query Store attempts to force a query plan (plan_id) onto a specific Availability Group replica group (replica_group_id), but the plan is either missing from that replica’s local Query Store cache, has been marked unforceable due to schema drift, or the secondary replica has not yet synchronized the plan metadata from the primary. It is frequently triggered in per-replica Query Store configurations (new in SQL Server 2022+/2025) where plan forcing policies differ between primary and secondary nodes. Corrupted Query Store internal tables (plan_persistence catalog views) or an outdated replica topology cache can also prevent the forcing operation from locating the correct plan reference.

Step-by-Step Resolution Guide

Step 1: Verify Query Store Replica Configuration

• Run SELECT * FROM sys.database_query_store_options; on both primary and secondary replicas to confirm actual_state_desc is READ_WRITE or READ_ONLY as expected per node role.
• Check that QUERY_STORE = ON (OPERATION_MODE = READ_WRITE) is explicitly set per-replica using:

  ALTER DATABASE [YourDB] SET QUERY_STORE (OPERATION_MODE = READ_WRITE);

• Confirm the replica group ID referenced in the error matches an active, healthy AG replica via sys.dm_hadr_availability_replica_states.

Step 2: Validate the Plan Exists in the Target Replica’s Cache

• Query sys.query_store_plan on the specific secondary replica (not just the primary) to confirm the plan_id is physically present.
• If the plan is missing, force a Query Store sync by running:

  EXEC sys.sp_query_store_flush_db;

• Check is_forced_plan_failed and force_failure_count columns in sys.query_store_plan to identify if SQL Server already attempted and rejected forcing.

Step 3: Clear Corrupted Query Store Cache

• If plan metadata is inconsistent, clear and rebuild the Query Store cache:

  ALTER DATABASE [YourDB] SET QUERY_STORE CLEAR;

• Restart the Query Store background collector by toggling operation mode off and back on.
• Re-run sp_query_store_force_plan (plan_id, query_id) after the cache rebuild completes.

Step 4: Check for Schema Drift or Plan Incompatibility

• Inspect sys.query_store_plan.plan_forcing_type_desc to confirm the plan type is still compatible (e.g., not invalidated by an index rebuild or statistics update).
• If the underlying table schema changed since the plan was captured, unforce the old plan and allow SQL Server to recompile:

  EXEC sys.sp_query_store_unforce_plan @query_id, @plan_id;

Step 5: Update SQL Server Cumulative Updates

• Confirm you are running the latest Cumulative Update (CU) for SQL Server 2025, as early builds had known Query Store/AG replica synchronization bugs.
• Check build version with SELECT @@VERSION; and compare against the latest CU release notes from Microsoft.

Alternative or Verification Steps

• After applying fixes, re-run the original forcing command and confirm success via SELECT is_forced_plan FROM sys.query_store_plan WHERE plan_id = <your_plan_id>; — it should return 1.
• If forcing still fails, as a workaround, disable per-replica plan forcing and instead force the plan only at the primary replica level, allowing natural AG replication to propagate the forced plan state to secondaries.

Troubleshooting Guide: SQL Server Error 12462 – Malformed Feedback Data for Query ID

Why This Error Happens

Error 12462 is typically raised by SQL Server’s Query Store or Intelligent Query Processing (IQP) feedback mechanism when the internal feedback data associated with a specific Query ID becomes corrupted or fails schema validation during serialization/deserialization. This often occurs after an abrupt server shutdown, a failed Query Store flush to disk, or when feedback data was generated by a different, incompatible build of SQL Server 2025 (e.g., after a cumulative update that changed the internal feedback format). It can also surface when the msdb or user database’s Query Store internal tables have inconsistent or truncated rows due to disk-level corruption.

Step-by-Step Resolution Guide

Step 1: Identify the Affected Database and Query ID

• Check the SQL Server Error Log or Event Viewer (Windows Logs > Application) for the exact Query ID and database name tied to Error 12462.
• Run the following query to cross-reference the query in Query Store:

  SELECT * FROM sys.query_store_query WHERE query_id = <QueryID>;

• Note whether the error is isolated to one query or recurring across multiple queries.

Step 2: Clear the Corrupted Feedback Data

• Disable automatic plan feedback temporarily to stop further corruption:

  ALTER DATABASE SCOPED CONFIGURATION SET QUERY_FEEDBACK = OFF;

• Clear the Query Store feedback cache for the affected database:

  ALTER DATABASE [YourDatabaseName] SET QUERY_STORE CLEAR;

• If the issue is isolated to one plan, force-remove it specifically:

  EXEC sp_query_store_remove_query @query_id = <QueryID>;

Step 3: Validate Database and Query Store Integrity

• Run a full consistency check on the affected database:

  DBCC CHECKDB ('YourDatabaseName') WITH NO_INFOMSGS, ALL_ERRORMSGS;

• Confirm Query Store is in a healthy, read-write state:

  SELECT actual_state_desc FROM sys.database_query_store_options;

• If actual_state_desc shows ERROR or READ_ONLY, reset it using:

  ALTER DATABASE [YourDatabaseName] SET QUERY_STORE = ON (OPERATION_MODE = READ_WRITE);

Step 4: Apply Latest Cumulative Update

• Navigate to Windows Update or the Microsoft Update Catalog and confirm SQL Server 2025 is patched to the latest Cumulative Update (CU), as feedback serialization bugs are commonly addressed in CUs.
• Verify current build version:

  SELECT @@VERSION;

• Restart the SQL Server service after patching via services.msc or:

  Restart-Service -Name "MSSQLSERVER"

Step 5: Re-enable Feedback Mechanism

• Once the database passes integrity checks and the CU is applied, re-enable query feedback:

  ALTER DATABASE SCOPED CONFIGURATION SET QUERY_FEEDBACK = ON;

• Monitor the SQL Server Error Log for 24–48 hours to confirm Error 12462 does not recur.

Alternative or Verification Steps

• Verify the fix by querying sys.query_store_plan_feedback and sys.query_store_query for the previously affected Query ID to confirm clean, non-null feedback rows are being generated.
• If the error persists after clearing Query Store, as a last resort, disable and re-enable Query Store entirely (ALTER DATABASE [YourDatabaseName] SET QUERY_STORE = OFF; followed by = ON), which forces a full rebuild of the internal feedback and plan cache structures.

Troubleshooting Guide: Error 12460 — Invalid Value for @disableoptimizedplan_forcing

Why This Error Happens

This error occurs when a stored procedure call—typically sp_query_store_set_hints, sp_query_store_force_plan, or a related Query Store configuration procedure—receives a parameter value for @disable_optimized_plan_forcing that falls outside the accepted boolean range of 0 or 1. This is most commonly caused by a manual scripting mistake, a legacy automation/maintenance script that hasn’t been updated for SQL Server 2025’s Query Store parameter validation, or a NULL/truncated value being passed due to a variable type mismatch (e.g., using an INT variable that was never properly initialized). In some cases, this also surfaces when third-party monitoring tools or ORM-generated maintenance scripts pass default values that are incompatible with the stricter parameter validation introduced in SQL Server 2025.

Step-by-Step Resolution Guide

Step 1: Identify the Source of the Invalid Call

• Check SQL Server Error Logs (via SSMS > Management > SQL Server Logs) or the Windows Event Viewer under Applications and Services Logs > Microsoft > Windows > SQLSERVER for the exact query or batch that triggered Error 12460.
• Run the following to capture recent executions if Extended Events are enabled:

SELECT * FROM sys.fn_xe_file_target_read_file('system_health*.xel', NULL, NULL, NULL)
WHERE object_name = 'error_reported' AND event_data LIKE '%12460%';

• Note whether the call originates from a maintenance plan, a SQL Agent Job, or an application connection string.

Step 2: Correct the Parameter Value in the Script

• Open the offending script, stored procedure, or SQL Agent Job step (SSMS > SQL Server Agent > Jobs > right-click > Properties > Steps).
• Ensure the parameter is explicitly cast as a BIT or passed as a literal 0 or 1, not a string, NULL, or out-of-range integer.

EXEC sys.sp_query_store_set_hints 
    @query_id = 101, 
    @disable_optimized_plan_forcing = 1; -- must be 0 or 1 only

• If the value comes from a variable, add a validation check before execution:

IF @DisablePlanForcing NOT IN (0,1)
    SET @DisablePlanForcing = 0;

Step 3: Update Outdated Maintenance Scripts or Tools

• If using third-party monitoring software (e.g., Redgate, SolarWinds, Idera), check vendor release notes for SQL Server 2025 compatibility updates, as older builds may pass deprecated or malformed parameters.
• Update the tool to the latest version or apply the vendor-provided hotfix that addresses SQL Server 2025 Query Store API changes.
• On Windows Server 2025/Windows 11, verify the SQL Server 2025 Cumulative Update level matches the minimum build required by the third-party tool via:

SELECT @@VERSION;

Step 4: Rebuild or Reset the Query Store Configuration (If Corrupted)

• If the error persists even with correct parameters, the Query Store catalog metadata may be inconsistent. Clear and reset Query Store for the affected database:

ALTER DATABASE [YourDatabaseName] SET QUERY_STORE CLEAR;

• Re-enable Query Store afterward with explicit, valid settings:

ALTER DATABASE [YourDatabaseName] 
SET QUERY_STORE = ON 
(OPERATION_MODE = READ_WRITE);

Alternative or Verification Steps

• Verify the fix by re-running the original failing procedure call with the corrected 0 or 1 value and confirming no Error 12460 appears in sys.messages or the SQL Error Log.
• As an alternative, if the issue stems from an automated job, temporarily disable the specific SQL Agent Job step and manually execute the corrected T-SQL in SSMS to confirm successful execution before re-enabling automation.

Troubleshooting Guide: SQL Server Error 12461 — Automatic Query Parameterization Notification

Why This Error Happens

This message is an informational event (Error 12461) rather than a critical failure; it fires when SQL Server’s Query Store detects that a query was converted into a parameterized plan via SIMPLE or FORCED parameterization instead of using the literal values submitted by the application. This typically occurs because the database’s PARAMETERIZATION option is set to FORCED, causing the optimizer to template literal constants into parameters to promote plan reuse. While not inherently an error, it can lead to unexpected plan caching behavior, parameter sniffing issues, or inconsistent query performance if the workload wasn’t designed with forced parameterization in mind.

Step-by-Step Resolution Guide

Step 1: Confirm the Database Parameterization Setting

• Open SQL Server Management Studio (SSMS) and connect to your Windows Server 2025 instance.
• Run the following query to check the current mode:

  SELECT name, is_parameterization_forced 
  FROM sys.databases 
  WHERE name = 'YourDatabaseName';

• A value of 1 confirms FORCED parameterization is active and likely the source of the 12461 messages.

Step 2: Evaluate Query Store Capture Policy

• Navigate to Object Explorer > Databases > YourDatabase > Properties > Query Store.
• Check the “Query Store Capture Mode” — if set to ALL, every auto-parameterized query gets logged, increasing noise in the error log.
• Change the capture mode to AUTO to reduce excessive logging of low-value ad-hoc queries:

  ALTER DATABASE YourDatabaseName 
  SET QUERY_STORE (QUERY_CAPTURE_MODE = AUTO);

Step 3: Toggle Parameterization Mode (If Unintended)

• If FORCED parameterization was enabled unintentionally, revert it to SIMPLE mode:

  ALTER DATABASE YourDatabaseName 
  SET PARAMETERIZATION SIMPLE;

• If FORCED parameterization is required for your workload (e.g., high-volume OLTP with ad-hoc queries), instead suppress the logging using a Plan Guide or Query Store hint rather than disabling the feature entirely.

Step 4: Apply Targeted Exclusions via Query Store Hints

• For specific problematic queries, use the QUERY_STORE_HINT feature introduced in recent SQL Server versions to override parameterization behavior for a single query ID without changing the whole database:

  EXEC sys.sp_query_store_set_hints 
  @query_id = <query_id>, 
  @query_hints = N'OPTION(DISABLE_PARAMETER_SNIFFING)';

• Replace <query_id> with the ID reported in the Windows Event Viewer or SQL Server Error Log message.

Step 5: Restart the Service and Monitor the Error Log

• In Windows Server 2025, open Services.msc, locate SQL Server (MSSQLSERVER), and restart it to apply configuration changes cleanly.
• Monitor ERRORLOG located at C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ for recurrence of Error 12461.

Alternative or Verification Steps

• Verify the fix by querying sys.query_store_query and checking the is_forced_parameterized column to ensure new queries are no longer being auto-parameterized unexpectedly.
• If the error persists despite configuration changes, use Extended Events (xEvents) with the sql_statement_recompile or query_store_plan_forcing_failed events to trace exactly which stored procedures or ad-hoc batches are triggering the behavior.

Troubleshooting Guide: Error 12459 — Cannot Load Optimization Replay Script (ORS) from Query Store

Why This Error Happens

Error 12459 typically occurs when the Query Store’s internal metadata catalog views required to generate the Optimization Replay Script have become corrupted or are inaccessible due to insufficient permissions on the ossgtore-related system tables. This can also happen if the Query Store is in a READ_ONLY or ERROR state because it hit its configured size cap, preventing SQL Server 2025’s new ORS engine from serializing the replay script. Additionally, a mismatch between the database compatibility level and the ORS feature requirements (compatibility level 170 or higher) can trigger this failure.

Step-by-Step Resolution Guide

Step 1: Verify Query Store Operational State

• Open SQL Server Management Studio (SSMS) and connect to the target instance.
• Run the following query to check the current state:

  SELECT actual_state_desc, readonly_reason 
  FROM sys.database_query_store_options;

• If actual_state_desc returns READ_ONLY, proceed to Step 2 to clear space.

Step 2: Clear Query Store Space and Reset State

• Execute a cleanup on the stale query data to free up allocated space:

  ALTER DATABASE [YourDBName] SET QUERY_STORE CLEAR;

• Increase the MAX_STORAGE_SIZE_MB parameter if the store is hitting its cap frequently:

  ALTER DATABASE [YourDBName] SET QUERY_STORE (MAX_STORAGE_SIZE_MB = 2000);

Step 3: Validate Database Compatibility Level

• Confirm the database is running on compatibility level 170 (SQL Server 2025) to support the ORS schema.
• Check the current level:

  SELECT compatibility_level FROM sys.databases WHERE name = 'YourDBName';

• Update it if necessary:

  ALTER DATABASE [YourDBName] SET COMPATIBILITY_LEVEL = 170;

Step 4: Check and Repair File System Permissions

• Navigate to the SQL Server data directory, typically located at C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\DATA\.
• Ensure the SQL Server Service Account (e.g., NT Service\MSSQLSERVER) has Read/Write permissions on the .mdf and .ldf files associated with the target database.
• Restart the SQL Server service via services.msc to apply permission changes.

Step 5: Rebuild the Query Store Catalog

• If the catalog remains corrupted after clearing, disable and re-enable the Query Store entirely to force a schema rebuild:

  ALTER DATABASE [YourDBName] SET QUERY_STORE = OFF;
  ALTER DATABASE [YourDBName] SET QUERY_STORE = ON;

• Attempt to generate the ORS script again via the “Replay” context menu in SSMS.

Alternative or Verification Steps

• Verification: Run sys.dm_db_query_store_internal_state to confirm the desired_state_desc matches READ_WRITE before attempting to export the ORS file again.
• Alternative: If the error persists, extract the historical query data via the sp_query_store_consistency_check stored procedure to manually identify and remove corrupted plan entries that are blocking the serialization process.

Troubleshooting Guide: Error 12457 – Cannot Apply Hints to Forced Plan Query

Why This Error Happens

This error occurs because a query has an existing forced plan applied through Query Store (sp_query_store_force_plan), and SQL Server’s Query Store hint feature (sp_query_store_set_hints) does not permit hints to be layered on top of a manually forced plan. The engine treats forced plans and forced hints as mutually exclusive mechanisms since both attempt to influence the optimizer’s plan selection, and allowing both simultaneously could create conflicting or ambiguous execution directives. This typically happens when a DBA or automated tuning process (such as Automatic Plan Correction) previously forced a specific plan for the query_id and a separate process later attempts to apply query hints to the same query.

Step-by-Step Resolution Guide

Step 1: Identify the Query and Confirm Forced Plan Status

• Run the following query to check the current state of the query in Query Store:
• SELECT query_id, plan_id, is_forced_plan FROM sys.query_store_plan WHERE query_id = <your_query_id>;
• Confirm that is_forced_plan returns 1, verifying this is the root cause of Error 12457.

Step 2: Unforce the Existing Plan

• Before hints can be applied, remove the existing forced plan using sp_query_store_unforce_plan.
• Execute: EXEC sp_query_store_unforce_plan @query_id = <your_query_id>, @plan_id = <your_plan_id>;
• Ensure you have db_owner or appropriate ALTER permissions on the database, as insufficient rights will cause this command to silently fail or throw a permissions error.

Step 3: Apply the Desired Query Hint

• Once the plan is unforced, apply the intended hint using sp_query_store_set_hints.
• Example: EXEC sp_query_store_set_hints @query_id = <your_query_id>, @query_hints = N'OPTION (MAXDOP 4)';
• Verify the hint syntax is compatible with SQL Server 2025’s supported hint list, as deprecated or malformed hint strings will trigger a separate error.

Step 4: Re-Force the Plan if Required (Optional)

• If your workload still requires plan stability in addition to the hint, re-force the plan only after hints are successfully applied.
• Execute: EXEC sp_query_store_force_plan @query_id = <your_query_id>, @plan_id = <your_plan_id>;
• Note that SQL Server 2025 will apply the hint first during compilation, then enforce the plan shape afterward, so test performance impact in a staging environment.

Alternative or Verification Steps

• Verify the fix by querying sys.query_store_query_hints to confirm the hint is now listed against the query_id, and re-run the original workload to ensure Error 12457 no longer appears in the SQL Server Error Log or Extended Events session.
• If the error persists, check whether Automatic Tuning (ALTER DATABASE SCOPED CONFIGURATION SET AUTOMATIC_TUNING = FORCE_LAST_GOOD_PLAN = ON) is re-forcing the plan automatically after you remove it; disable this setting temporarily to isolate the conflict.

Troubleshooting Guide: SQL Server Error 12458 – Query Store Cannot Force Plan Due to Query Hints

Why This Error Happens

This error occurs because Query Store identifies that the specified query_id has one or more USE HINT or OPTION-level Query Store hints already applied to it via sys.sp_query_store_set_hints. SQL Server’s query optimizer does not allow Query Store to force a specific execution plan on a query while manual query hints are simultaneously active, as this creates a conflict between two competing plan-shaping mechanisms. The engine prioritizes explicit hints over forced plans to avoid unpredictable or contradictory optimizer behavior.

Step-by-Step Resolution Guide

Step 1: Identify Existing Query Store Hints

• Run the following query against your target database to check for existing hints:
• SELECT * FROM sys.query_store_query_hints WHERE query_id = <your_query_id>;
• Note the query_hint_id and the hint text returned for the affected query.

Step 2: Remove the Conflicting Query Hint

• Execute the system stored procedure to clear hints for the specific query:
• EXEC sys.sp_query_store_clear_hints @query_id = <your_query_id>;
• Confirm no rows are returned when re-running the SELECT query from Step 1.

Step 3: Attempt to Force the Plan Again

• Retry forcing the desired plan using Query Store once hints are cleared:
• EXEC sys.sp_query_store_force_plan @query_id = <your_query_id>, @plan_id = <your_plan_id>;
• Verify the command completes without raising Error 12458.

Step 4: Evaluate Hint vs. Forced Plan Strategy

• Decide whether you actually need the hint or the forced plan, as SQL Server 2025 does not allow both simultaneously on the same query_id.
• If the hint is required for correctness (e.g., OPTION (RECOMPILE) or MAXDOP), consider re-applying the hint instead of forcing a plan via sys.sp_query_store_set_hints.

Step 5: Validate Query Store Configuration

• Open SQL Server Management Studio (SSMS) and navigate to Database Properties > Query Store.
• Ensure “Query Store Capture Mode” and “Operation Mode” are set to READ_WRITE to allow plan forcing operations to persist correctly.

Alternative or Verification Steps

• Run SELECT * FROM sys.query_store_plan WHERE query_id = <your_query_id> AND is_forced_plan = 1; to confirm the plan is now successfully forced.
• If forcing still fails, use sys.sp_query_store_set_hints instead of plan forcing to achieve similar performance stability without triggering a conflict.

Troubleshooting Guide: Error 12455 – Query Hint Not Supported in Query Store

Why This Error Happens

This error occurs because Query Store maintains a restricted list of query hints that it can persist and reapply during plan forcing operations, and the specified hint is not included in this supported list. This typically happens when using newer or specialized hints (such as certain USE HINT options, trace flag-based hints, or deprecated syntax) that Query Store’s forcing mechanism cannot validate or replay reliably. The issue can also surface after an in-place upgrade to SQL Server 2025 if legacy query hints were created under an older compatibility level that has since changed its supported hint surface area.

Step-by-Step Resolution Guide

Step 1: Identify the Unsupported Hint Causing the Error

• Capture the full error message text, as the %.*ls placeholder will be replaced with the actual hint name (e.g., FORCE ORDER, ENABLE_PARALLEL_PLAN_PREFERENCE, etc.).
• Run the following query to locate the offending query/plan in Query Store:

SELECT qsq.query_id, qsqt.query_sql_text, qsp.query_hint_text
FROM sys.query_store_query qsq
JOIN sys.query_store_query_text qsqt ON qsq.query_text_id = qsqt.query_text_id
JOIN sys.query_store_plan qsp ON qsq.query_id = qsp.query_id
WHERE qsp.query_hint_text IS NOT NULL;

• Note the query_id and plan_id values returned for later use.

Step 2: Remove or Replace the Unsupported Hint

• Open the stored procedure, view, or inline query that contains the problematic OPTION (USE HINT(...)) or OPTION (...) clause.
• Replace the unsupported hint with a supported equivalent. Check Microsoft’s current supported hints list under sys.database_query_store_options documentation for SQL Server 2025.
• If the hint was auto-generated by Automatic Tuning, disable automatic plan correction temporarily:

ALTER DATABASE SCOPED CONFIGURATION SET AUTOMATIC_TUNING (FORCE_LAST_GOOD_PLAN = OFF);

Step 3: Clear the Invalid Plan Forcing Entry from Query Store

• Unforce the plan associated with the unsupported hint using the query_id from Step 1:

EXEC sp_query_store_unforce_plan @query_id = <query_id>, @plan_id = <plan_id>;

• If the hint was injected via a plan guide rather than Query Store directly, drop the plan guide:

EXEC sp_control_plan_guide N'DROP', N'<plan_guide_name>';

• Restart the SQL Server service via services.msc on Windows Server 2025 / Windows 11 only if the error persists in the error log after the above changes.

Step 4: Validate Compatibility Level Settings

• Check the current database compatibility level, since hint support varies by level:

SELECT name, compatibility_level FROM sys.databases WHERE name = '<YourDatabaseName>';

• If the database was upgraded from an older version, ensure compatibility level is explicitly set to 170 (or the correct SQL Server 2025 level) to align hint parsing behavior:

ALTER DATABASE <YourDatabaseName> SET COMPATIBILITY_LEVEL = 170;

Alternative or Verification Steps

• Re-execute the original query or stored procedure and confirm Error 12455 no longer appears in SQL Server Error Log or Management Studio output.
• As an alternative, if the hint is business-critical and unsupported by Query Store, apply it directly in the T-SQL query text using OPTION() syntax instead of relying on Query Store plan forcing, which bypasses the validation restriction entirely.

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.

Troubleshooting Guide: SQL Server Error 12454 — Query Store Configuration Change

Why This Error Happens

Error 12454 is actually an informational message, not a true failure condition; it is logged whenever SQL Server successfully changes a Query Store option (such as OPERATION_MODE, MAX_STORAGE_SIZE_MB, or QUERY_CAPTURE_MODE) for a specific database. However, this message can flood the SQL Server Error Log and cause confusion if Query Store is rapidly toggling states, which typically happens due to automated maintenance scripts, Query Store entering “READ_ONLY” mode from disk space exhaustion, or internal cleanup tasks modifying settings. Excessive logging of this event is most often a symptom of the underlying Query Store storage filling up or becoming corrupted, forcing SQL Server to repeatedly adjust the capture mode.

Step-by-Step Resolution Guide

Step 1: Verify Current Query Store Status and Disk Usage

• Open SQL Server Management Studio (SSMS) and run the following query to check the current state:

  SELECT actual_state_desc, readonly_reason, current_storage_size_mb, max_storage_size_mb 
  FROM sys.database_query_store_options;

• If readonly_reason returns a non-zero value (e.g., 65536), this confirms the Query Store disk quota has been exceeded, triggering the repeated Error 12454 messages.
• Check the physical disk space on the volume hosting your database files (e.g., C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\DATA) to ensure the drive isn’t full.

Step 2: Clear or Resize the Query Store

• If the store is in READ_ONLY mode due to size limits, clear the stale data to force it back to READ_WRITE:

  ALTER DATABASE [YourDatabaseName] SET QUERY_STORE CLEAR;

• Alternatively, increase the maximum storage allocation to prevent immediate re-triggering of the read-only state:

  ALTER DATABASE [YourDatabaseName] SET QUERY_STORE (MAX_STORAGE_SIZE_MB = 2048);

• If using Windows Server 2025 storage spaces, verify the underlying virtual disk tier hasn’t throttled I/O, which can delay Query Store flush operations and cause state flapping.

Step 3: Adjust Capture Policy to Reduce Log Noise

• If the error repeats due to high-frequency query churn (common in Windows 11 development environments running local SQL instances), switch the capture mode to AUTO to reduce overhead:

  ALTER DATABASE [YourDatabaseName] SET QUERY_STORE (QUERY_CAPTURE_MODE = AUTO);

• Restart the SQL Server (MSSQLSERVER) service via services.msc or net stop MSSQLSERVER && net start MSSQLSERVER to ensure the new configuration is fully re-initialized in memory.

Alternative or Verification Steps

• Verify the fix by re-running the sys.database_query_store_options query; actual_state_desc should now show READ_WRITE instead of READ_ONLY.
• If the error persists despite available disk space, run DBCC CHECKDB to rule out corruption in the underlying Query Store catalog views, as corrupted internal tables can force SQL Server into a continuous reset loop.

Troubleshooting Guide: Error 12452 in Microsoft SQL Server 2025

Why This Error Happens

Error 12452 occurs when a configuration option—commonly related to extended events sessions, server configuration settings, or sp_configure parameters—receives a value that falls outside the acceptable minimum and maximum range defined by SQL Server. This typically happens due to manual edits to configuration scripts, outdated deployment templates carried over from older SQL Server versions, or automated provisioning tools (such as PowerShell DSC or ARM templates) passing incorrect integer values. In SQL Server 2025 running on Windows Server 2025 or Windows 11, this is frequently triggered by compatibility mismatches when legacy T-SQL scripts reference option boundaries that have changed in the new release.

Step-by-Step Resolution Guide

Step 1: Identify the Offending Option and Value

• Open the SQL Server Error Log via SSMS (Object Explorer > Management > SQL Server Logs) or check C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG.
• Locate the exact line referencing Error 12452 to identify the %S_MSG option name and the invalid %d value passed.
• Cross-reference the failing script or application (e.g., a deployment pipeline, Extended Events DDL, or sp_configure call).

Step 2: Verify the Valid Range for the Option

• Run the following query to check valid boundaries for server-level configuration options:

SELECT name, minimum, maximum, value, value_in_use 
FROM sys.configurations 
WHERE name = 'option_name_here';

• For Extended Events–related errors, query sys.dm_xe_object_columns to confirm the type_name and acceptable range for the specific event session parameter.
• Compare the documented min/max in SQL Server 2025 Books Online, as ranges may differ from SQL Server 2019/2022.

Step 3: Correct the Value in the Script or Configuration

• Edit the T-SQL script, PowerShell DSC resource, or ARM/Bicep template to pass a value within the valid range.
• If using sp_configure, run:

EXEC sp_configure 'option_name_here', <valid_value>;
RECONFIGURE WITH OVERRIDE;

• If the error originates from an Extended Events session, drop and recreate the session with corrected parameter values using CREATE EVENT SESSION.

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

• Open Services.msc on Windows Server 2025 / Windows 11.
• Locate SQL Server (MSSQLSERVER), right-click, and select Restart.
• Confirm no dependent services (SQL Server Agent, Reporting Services) fail to restart due to the same configuration issue.

Alternative or Verification Steps

• Re-run the original script or operation that triggered Error 12452 and confirm it completes without error messages in SSMS or the ERRORLOG file.
• If the issue persists, use sp_configure with 'show advanced options', 1 to reveal hidden configuration boundaries, or revert to a known-good backup of the configuration script from source control.

Troubleshooting Guide: SQL Server Error 12453 – Invalid Value Provided for Option

Why This Error Happens

Error 12453 occurs when a configuration option—commonly associated with memory settings, ALTER SERVER CONFIGURATION, or Service Broker/session-level parameters—is assigned a value outside of the valid range accepted by SQL Server 2025. This typically happens due to a manual misconfiguration in a T-SQL script, a legacy configuration value carried over from an older SQL Server version that is no longer supported, or an automated deployment script passing an incorrect parameter (such as a negative number or a value exceeding the maximum thread or memory threshold). In some cases, corrupted or outdated SSMS scripts generated before an upgrade to SQL Server 2025 may reference deprecated option boundaries.

Step-by-Step Resolution Guide

Step 1: Identify the Exact Option and Value in Error

• Check the full error message text in SQL Server Error Logs (Management > SQL Server Logs in SSMS) or via xp_readerrorlog.
• Note the specific %S_MSG placeholders filled in the actual error—these identify which configuration option (e.g., max worker threads, cost threshold for parallelism, query governor cost limit) and what min/max boundary was violated.
• Cross-reference the setting against Microsoft’s official documentation for SQL Server 2025, as valid ranges may have changed from SQL Server 2022.

Step 2: Correct the Configuration Value via T-SQL or SSMS

• Open SQL Server Management Studio (SSMS) and connect to the instance using an account with sysadmin or serveradmin role.
• Run sp_configure to view current and allowed values:

  EXEC sp_configure 'show advanced options', 1;
  RECONFIGURE;
  EXEC sp_configure;

• Locate the problematic option in the output and note the minimum and maximum columns.
• Reset the value within the valid range using:

  EXEC sp_configure 'option_name', correct_value;
  RECONFIGURE WITH OVERRIDE;

Step 3: Audit Deployment Scripts and Automation Pipelines

• Review any DevOps pipelines, PowerShell DSC configurations, or .sql deployment scripts that set server-level or database-level options programmatically.
• Search for hardcoded values that may no longer be valid in SQL Server 2025 (e.g., thread counts, timeout values, or memory allocations in MB that exceed new server limits).
• Update the script source (stored in Azure DevOps, GitHub, or local file paths such as C:\Scripts\SQLConfig\) to reflect the corrected, validated values.

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

• Some configuration changes require a service restart to take effect, particularly those not marked as “dynamic” in sp_configure output.
• Open Services.msc on Windows Server 2025 / Windows 11, locate SQL Server (MSSQLSERVER) or the named instance, and select Restart.
• Alternatively, use PowerShell:

  Restart-Service -Name 'MSSQLSERVER' -Force

Alternative or Verification Steps

• Re-run the original command or script that triggered Error 12453 and confirm it completes without raising the exception.
• If the issue persists, use EXEC sp_configure 'option_name' to double-check the applied value matches the intended configuration, and review the SQL Server Error Log for any residual warnings tied to Event ID 12453.

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.

Troubleshooting Guide: Error 12449 – Plan Forcing Not Supported for Resumable Index Builds

Why This Error Happens

This error occurs because SQL Server’s Query Store attempted to force a query plan (via sp_query_store_force_plan or Automatic Plan Correction) on a query that is internally associated with a resumable index build operation. Resumable index rebuilds (ALTER INDEX ... RESUME) generate execution plans that are inherently dynamic and checkpoint-dependent, meaning the SQL Server engine cannot guarantee a single forced plan shape across pause/resume cycles. This is a hard architectural limitation in SQL Server 2025, not a configuration error, so the fix involves changing workflow rather than correcting a broken setting.

Step-by-Step Resolution Guide

Step 1: Identify the Conflicting Query ID

• Run the following against Query Store to locate the offending query tied to the index operation:
• SELECT qsq.query_id, qsq.object_id, qsqt.query_sql_text FROM sys.query_store_query qsq JOIN sys.query_store_query_text qsqt ON qsq.query_text_id = qsqt.query_text_id WHERE qsqt.query_sql_text LIKE '%ALTER INDEX%RESUME%';
• Confirm the query_id returned matches the one referenced in the SQL Server error log or Extended Events trace for Error 12449.

Step 2: Remove the Forced Plan Policy from the Resumable Operation

• Execute EXEC sp_query_store_unforce_plan @query_id = <id>, @plan_id = <id>; to clear the invalid forcing attempt.
• If Automatic Tuning (ALTER DATABASE SET AUTOMATIC_TUNING (FORCE_LAST_GOOD_PLAN = ON)) is enabled, temporarily disable it for the duration of the index maintenance window using ALTER DATABASE [YourDB] SET AUTOMATIC_TUNING (FORCE_LAST_GOOD_PLAN = OFF);.
• Verify no SQL Agent Job or maintenance plan is scripted to re-apply plan forcing immediately after the index operation starts.

Step 3: Restructure the Index Maintenance Window

• Separate the resumable index build command into its own isolated batch, away from any query hints or plan guides.
• Ensure the ALTER INDEX REBUILD WITH (ONLINE = ON, RESUMABLE = ON, MAX_DURATION = <minutes>) statement does not inherit a USE PLAN or OPTION (FORCE_PLAN) hint from a wrapping stored procedure.
• Check sys.index_resumable_operations on Windows Server 2025 instances to ensure no orphaned “Paused” states exist from a previous failed session before re-running the build.

Step 4: Re-enable Query Store Settings Safely

• Once the resumable build reaches a COMPLETED state, re-enable FORCE_LAST_GOOD_PLAN or manually force plans on the post-build stable version of the query if needed.
• Use ALTER DATABASE [YourDB] SET AUTOMATIC_TUNING (FORCE_LAST_GOOD_PLAN = ON); to restore standard behavior.

Alternative or Verification Steps

• To verify the fix, re-run the index rebuild script and monitor the SQL Server Error Log (via EXEC xp_readerrorlog 0, 1, N'12449';) to confirm the event no longer triggers during the resumable operation.
• As an alternative, if plan stability is critical, use a Plan Guide (sp_create_plan_guide) targeting the underlying table statistics rather than attempting to force a plan directly on the transient resumable index query.

Troubleshooting Guide: Error 12450 — “Could Not Allocate Memory for DW Query or Showplan”

Why This Error Happens

This error typically occurs when SQL Server’s internal memory broker is unable to reserve sufficient contiguous memory to generate a query execution plan (Showplan) or process a DataWarehouse (DW) query, often due to memory pressure from other concurrent workloads. It is commonly triggered when the max server memory setting is configured too restrictively relative to the complexity of the query plan being compiled, or when the system is experiencing external memory pressure from the OS or other applications on Windows Server 2025. In some cases, this can also stem from a corrupted plan cache or excessive query plan complexity (e.g., queries with thousands of joins or heavily nested subqueries) that exceeds the memory grant available to the resource governor.

Step-by-Step Resolution Guide

Step 1: Verify and Adjust SQL Server Memory Configuration

• Open SQL Server Management Studio (SSMS) and connect to the instance.
• Run EXEC sp_configure 'show advanced options', 1; RECONFIGURE; followed by EXEC sp_configure 'max server memory'; to check current limits.
• Ensure max server memory is not set too low for your workload; a common starting point is Total RAM minus 4GB for OS overhead on dedicated servers.
• Update if necessary: EXEC sp_configure 'max server memory', <value_in_MB>; RECONFIGURE;

Step 2: Clear the Plan Cache and Buffer Pool

• Execute DBCC FREEPROCCACHE; to clear the corrupted or bloated execution plan cache that may be causing the allocation failure.
• Follow this with DBCC DROPCLEANBUFFERS; on a non-production environment to reset the buffer pool (use with caution in production due to performance impact).
• Restart the SQL Server service via services.msc or run net stop MSSQLSERVER && net start MSSQLSERVER in an elevated Command Prompt if the issue persists.

Step 3: Simplify or Optimize the Problematic Query

• Identify the specific DW query causing the error using Extended Events or the Query Store (SELECT * FROM sys.query_store_query_text WHERE query_text_id = <ID>;).
• Break down overly complex queries (e.g., those with excessive UNIONs or nested CTEs) into smaller, temp-table-driven batches to reduce the memory required for plan generation.
• Check for missing statistics using sp_updatestats on the relevant database to ensure the optimizer isn’t generating an inefficient, memory-heavy plan.

Step 4: Check Windows Server Resource Allocation

• Open Task Manager or Resource Monitor on Windows Server 2025/Windows 11 to check if another process (e.g., antivirus, backup software) is consuming high memory alongside sqlservr.exe.
• Navigate to Control Panel > System > Advanced System Settings > Performance Settings > Advanced to verify the “Adjust for best performance of” setting is configured for Background Services rather than Programs.
• Ensure the “Lock Pages in Memory” policy is correctly assigned to the SQL Server service account via secpol.msc > Local Policies > User Rights Assignment.

Alternative or Verification Steps

• Verification: Re-run the original DW query or generate an Estimated Execution Plan in SSMS; if successful, check the SQL Server Error Log (EXEC sp_readerrorlog;) to confirm Error 12450 no longer appears.
• Alternative: If the error persists despite available memory, enable Trace Flag 8048 (if applicable to specific memory spinlock contention scenarios) or consult the sys.dm_os_memory_clerks DMV to pinpoint which specific memory clerk (e.g., MEMORYCLERK_SQLOPTIMIZER) is being starved.

Troubleshooting Guide: Query Store Stored Procedure Unavailable on Readable Secondary Replica (Error 12446)

Why This Error Happens

Error 12446 occurs because Query Store stored procedures (such as sys.sp_query_store_*) are designed to modify Query Store data, which requires write access to the database. Readable secondary replicas in an Always On Availability Group are read-only by design, so any procedure that attempts to insert, update, or flush Query Store data against the secondary database will be rejected. This is not a bug but an architectural limitation tied to how SQL Server 2025 enforces read-only states on secondary replicas.

Step-by-Step Resolution Guide

Step 1: Confirm the Replica Role and Read-Only State

• Run SELECT role_desc FROM sys.dm_hadr_availability_replica_states WHERE is_local = 1; on the instance reporting the error.
• Verify the output shows SECONDARY and that the database is in READ_ONLY mode via SELECT name, is_read_only FROM sys.databases WHERE name = 'YourDBName';.
• Confirm Error 12446 is logged specifically when calling procedures like sys.sp_query_store_flush_db or sys.sp_query_store_force_plan.

Step 2: Redirect Write Operations to the Primary Replica

• Identify the current primary replica using SELECT primary_replica FROM sys.dm_hadr_availability_group_states;.
• Modify your application connection string or script to explicitly target the primary replica for any Query Store maintenance stored procedures.
• Use Application Intent=ReadWrite in the connection string instead of ReadOnly when write operations to Query Store are required.

Step 3: Validate Query Store Configuration on the Primary

• On the primary replica, open SQL Server Management Studio (SSMS) and navigate to Database Properties > Query Store.
• Ensure “Operation Mode (Requested)” is set to Read Write and not Read Only.
• Run ALTER DATABASE [YourDBName] SET QUERY_STORE = ON (OPERATION_MODE = READ_WRITE); on the primary to confirm proper configuration.

Step 4: Separate Read and Write Workloads in Application Logic

• Update your application or monitoring scripts to query Query Store views (e.g., sys.query_store_runtime_stats) on the secondary for reporting purposes only.
• Route all maintenance procedures (cleanup, forcing plans, flushing) exclusively through the primary replica.
• Document this split clearly in your deployment runbooks to prevent future misconfiguration.

Step 5: Restart Query Store Monitoring Services if Needed

• If Query Store data appears stale on the secondary after redirecting writes, restart the SQL Server service on the secondary replica via Services.msc or Restart-Service MSSQLSERVER in PowerShell.
• Re-run the Always On Availability Group dashboard in SSMS to confirm synchronization health returns to SYNCHRONIZED.

Alternative or Verification Steps

• Verify the fix by executing a Query Store maintenance procedure against the primary replica and confirming it completes without Error 12446, then check that the updated data replicates to the secondary within the expected sync latency.
• As an alternative, if direct write access cannot be redirected, disable Query Store write-dependent automation on secondary-reading applications entirely and rely on scheduled jobs on the primary to handle plan forcing and cleanup tasks.

Troubleshooting Guide: SQL Server Error 12447 — Plan Forcing Not Supported for Query Type

Why This Error Happens

This error occurs because SQL Server’s Query Store attempted to force a specific execution plan (via sp_query_store_force_plan or Automatic Plan Correction) on a query whose statement type does not support plan forcing. Certain T-SQL constructs — such as queries referencing system views, bulk operations, recursive CTEs beyond supported patterns, natively compiled modules, or statements using specific hints incompatible with the plan guide engine — are explicitly excluded from forced plan behavior. This is a design limitation of the Query Store feature rather than a corruption or permissions issue, meaning the planid/queryid pair you’re targeting is structurally ineligible for forcing regardless of server configuration.

Step-by-Step Resolution Guide

Step 1: Identify the Query and Statement Type Causing the Error

• Run the following to inspect the query text and context:

  SELECT qsq.query_id, qsqt.query_sql_text, qsp.plan_id
  FROM sys.query_store_query qsq
  JOIN sys.query_store_query_text qsqt ON qsq.query_text_id = qsqt.query_text_id
  JOIN sys.query_store_plan qsp ON qsq.query_id = qsp.query_id
  WHERE qsq.query_id = <query_id>;

• Check for disqualifying constructs: MARS-enabled sessions, natively compiled modules (In-Memory OLTP), queries with RECOMPILE hints, or statements involving sp_executesql with dynamic SQL using unsupported patterns.

Step 2: Remove or Avoid the Failed Forcing Attempt

• If forcing was triggered manually, clear the force request:

  EXEC sp_query_store_unforce_plan @query_id = <query_id>, @plan_id = <plan_id>;

• If Automatic Plan Correction (AUTOMATIC_TUNING) triggered this, disable automatic forcing temporarily:

  ALTER DATABASE SCOPED CONFIGURATION SET FORCE_LAST_GOOD_PLAN = OFF;

Step 3: Rewrite the Query to Support Plan Forcing

• Refactor the query to avoid unsupported elements — for example, replace natively compiled stored procedure calls with standard T-SQL wrappers.
• Remove conflicting query hints (e.g., OPTION (RECOMPILE)) that inherently prevent a static plan from being cached and forced.
• Re-test by generating a new plan and attempting sp_query_store_force_plan on the updated query_id.

Step 4: Use Plan Guides as an Alternative (If Query Store Forcing Is Not Viable)

• For statement types ineligible under Query Store, use the legacy sp_create_plan_guide procedure instead, which supports a broader range of T-SQL constructs:

  EXEC sp_create_plan_guide
      @name = N'PlanGuide_WorkaroundQuery',
      @stmt = N'<exact_query_text>',
      @type = N'SQL',
      @module_or_batch = NULL,
      @params = NULL,
      @hints = N'OPTION (OPTIMIZE FOR UNKNOWN)';

Alternative or Verification Steps

• Verify resolution by querying sys.query_store_plan and confirming is_forced_plan = 1 without triggering Error 12447 in the SQL Server Error Log or Event Viewer (Event ID 12447 under Application logs).
• If plan forcing remains unsupported for this specific query shape, consider using Query Store Hints (sp_query_store_set_hints) introduced in SQL Server 2022+/2025 as a more flexible alternative to full plan forcing for edge-case statement types.

Troubleshooting Guide: Error 12445 — Query Store maxstoragesize_mb Limit Exceeded

Why This Error Happens

This error occurs because SQL Server 2025 enforces a hard upper limit on the MAX_STORAGE_SIZE_MB parameter for Query Store, which is tied to the edition of SQL Server or the available disk space on the volume hosting the database files. Attempting to set a value higher than this calculated or edition-imposed ceiling causes the engine to reject the ALTER DATABASE command outright. This is typically seen when migrating configuration scripts from an Enterprise edition environment to a Standard or Web edition instance, or when the underlying disk volume lacks sufficient free space to accommodate the requested allocation.

Step-by-Step Resolution Guide

Step 1: Verify the Current Edition Limitations

• Open SSMS (SQL Server Management Studio) connected to your Windows Server 2025 instance.
• Run SELECT SERVERPROPERTY('Edition'); to confirm if you are running Standard, Web, or Enterprise edition.
• Note that Standard Edition often caps Query Store storage significantly lower than Enterprise Edition.

Step 2: Check Available Disk Space on the Data Volume

• Navigate to the physical drive location where your .mdf and .ldf files reside (e.g., D:\SQLData\).
• Right-click the drive in Windows Explorer and select Properties to check free space.
• Ensure the drive has enough contiguous free space to support the value you are trying to set; SQL Server will not allow a max_storage_size_mb setting that exceeds the physical disk capacity.

Step 3: Adjust the T-SQL Command to a Valid Value

• Instead of guessing, query the actual limit returned in the error message (the %lu value in the Event Log).
• Modify your script to use a compliant value:

  ALTER DATABASE [YourDatabaseName] 
  SET QUERY_STORE (MAX_STORAGE_SIZE_MB = 1024);

• Avoid hardcoding values from older server instances; always pull the limit dynamically if scripting this for multiple servers.

Step 4: Clear Existing Query Store Data (If Corrupted)

• If the limit error persists even with low values, the internal Query Store catalog may be corrupted.
• Run the following to force a clean state before resizing:

  ALTER DATABASE [YourDatabaseName] SET QUERY_STORE CLEAR;

• Restart the SQL Server service via services.msc on your Windows Server 2025 machine to clear any cached memory allocations related to the Query Store.

Alternative or Verification Steps

• To verify the fix, run SELECT * FROM sys.database_query_store_options; and confirm the max_storage_size_mb column reflects your newly configured value without throwing Error 12445.
• If the error persists despite available disk space, consider moving the database to a volume with a larger capacity or upgrading your SQL Server edition to lift the hard-coded ceiling.

Troubleshooting Guide: SQL Server Error 12443 – Query Store Cannot Set Default Settings

Why This Error Happens

Error 12443 typically occurs when SQL Server 2025 attempts to initialize or reset Query Store to its default configuration but is blocked by insufficient permissions on the database or system catalog views. This can also result from a corrupted Query Store internal state (e.g., damaged mssqlsystemresource references or orphaned Query Store metadata) following an improper upgrade or abrupt shutdown. In some cases, the issue stems from the database being in a restricted state (READ_ONLY, EMERGENCY, or in the middle of an unfinished auto-upgrade) that prevents Query Store from writing default configuration values.

Step-by-Step Resolution Guide

Step 1: Verify Database State and Permissions

• Confirm the target database is ONLINE and not in READ_ONLY, RESTORING, or EMERGENCY mode by running:
  SELECT name, state_desc FROM sys.databases WHERE name = 'YourDBName';
• Ensure the executing login has ALTER DATABASE or db_owner permissions.
• If the database is read-only, temporarily set it to read-write:
  ALTER DATABASE [YourDBName] SET READ_WRITE;

Step 2: Disable and Re-enable Query Store

• Run the following to fully clear the existing (possibly corrupted) Query Store state:
  ALTER DATABASE [YourDBName] SET QUERY_STORE = OFF;
• Wait a few seconds, then re-enable it with explicit defaults instead of relying on automatic default application:
  ALTER DATABASE [YourDBName] SET QUERY_STORE = ON (OPERATION_MODE = READ_WRITE, DATA_FLUSH_INTERVAL_SECONDS = 900, MAX_STORAGE_SIZE_MB = 1000);
• This bypasses the internal “set defaults” routine that is triggering Error 12443.

Step 3: Clear Corrupted Query Store Internal Tables

• If Step 2 fails, manually clear Query Store data via the built-in stored procedure:
  EXEC sys.sp_query_store_consistency_check;
• If inconsistencies are reported, purge the Query Store catalog:
  ALTER DATABASE [YourDBName] SET QUERY_STORE CLEAR;
• Restart the SQL Server (MSSQLSERVER) service via services.msc on Windows Server 2025 / Windows 11 to flush cached metadata.

Step 4: Validate Resource Database Integrity

• Run DBCC CHECKDB('YourDBName') WITH NO_INFOMSGS, ALL_ERRORMSGS; to rule out underlying corruption.
• If corruption is found in system catalog views related to Query Store, restore the database from the last known good backup rather than attempting repair.

Alternative or Verification Steps

• Verify the fix by querying sys.database_query_store_options — the actual_state_desc column should show READ_WRITE with no error flags.
• If the error persists after all steps, create a new database, enable Query Store on it successfully, then migrate objects/data via sp_copy_database or export/import rather than repairing the original corrupted instance.

Troubleshooting Guide: SQL Server Error 12444 – Plan Forcing Not Supported for Natively Compiled Plans

Why This Error Happens

This error occurs because Query Store’s plan forcing feature relies on the standard SQL Server optimizer and execution engine, which natively compiled plans bypass entirely since they are compiled directly into machine code at creation time via the native compiler for memory-optimized tables. Natively compiled stored procedures and inline table-valued functions do not generate alternate plan shapes that can be swapped at runtime, so there is no mechanism for Query Store to inject a forced plan. This typically surfaces when an automatic tuning recommendation, an Automatic Plan Correction policy, or a manual sp_query_store_force_plan call attempts to apply a forced plan to a query that originates from a natively compiled module.

Step-by-Step Resolution Guide

Step 1: Identify the Offending Query and Module

• Run the following against the affected database to find the queryid and planid referenced in the error:

SELECT qsq.query_id, qsp.plan_id, OBJECT_NAME(qsq.object_id) AS module_name
FROM sys.query_store_query qsq
JOIN sys.query_store_plan qsp ON qsq.query_id = qsp.query_id
WHERE qsq.query_id = <query_id_from_error>;

• Confirm the module_name corresponds to a natively compiled procedure by checking sys.sql_modules for uses_native_compilation = 1.

Step 2: Disable Automatic Plan Correction for This Query

• If Automatic Tuning (Automatic Plan Correction) is enabled at the database level, exclude natively compiled queries from consideration:

ALTER DATABASE CURRENT SET AUTOMATIC_TUNING (FORCE_LAST_GOOD_PLAN = OFF);

• Alternatively, if you only want to stop forcing on this specific query, manually unforce it:

EXEC sys.sp_query_store_unforce_plan @query_id = <query_id>, @plan_id = <plan_id>;

• Verify no scheduled Agent jobs or third-party tuning tools are re-triggering a forced-plan recommendation on this query_id.

Step 3: Remove or Correct Any Manual Forcing Attempts

• Search deployment scripts, maintenance plans, or DBA runbooks for any sp_query_store_force_plan calls targeting natively compiled modules and remove them.
• If the intent was performance stabilization, instead optimize the natively compiled procedure directly by reviewing its NATIVE_COMPILATION definition and indexing strategy on the underlying memory-optimized tables, since plan shape here is fixed at compile time and cannot be altered via Query Store.

Step 4: Validate Query Store Configuration

• Open SQL Server Management Studio (SSMS) on Windows Server 2025 / Windows 11, right-click the database → Properties → Query Store page.
• Ensure “Query Store Capture Mode” is not inadvertently flagging natively compiled queries for forcing; set Capture Mode to “Auto” if custom filters are too aggressive.
• Restart the SQL Server service only if configuration changes require a full reload: Restart-Service MSSQLSERVER in an elevated PowerShell session.

Alternative or Verification Steps

• Re-run the workload or wait for the next automatic tuning evaluation cycle, then query sys.query_store_plan to confirm is_forced_plan = 0 for the affected query_id with no recurrence of Error 12444 in the SQL Server error log.
• If forcing is truly required for performance consistency, consider rewriting the logic as a standard (interpreted) stored procedure instead of a natively compiled one, since plan forcing is only supported for conventional T-SQL query plans.

Troubleshooting Guide: SQL Server Error 12441 – Query Store Is Initializing

Why This Error Happens

Error 12441 is a purely informational message generated by the SQL Server Query Store feature during the startup phase of a database. It occurs when the Query Store plan cache and runtime statistics structures are being loaded into memory, typically right after a database comes online following a restart, failover, or database attach/restore operation. This message does not indicate corruption, misconfiguration, or a permissions issue—it simply signals that the asynchronous initialization process is underway and that query data capture will resume once it completes.

Step-by-Step Resolution Guide

Step 1: Confirm the Message Is Informational Only

• Check the SQL Server Error Log (via SSMS: Management > SQL Server Logs, or EXEC xp_readerrorlog 0, 1, N'12441') to confirm the entry is logged with severity level 0 or 10 (informational), not a warning or error severity (16+).
• Note the timestamp—this should correlate with a recent SQL Server service restart, database failover (Always On), or database attach/restore operation.

Step 2: Monitor Query Store Initialization Status

• Run the following query to check the current Query Store operational state:

  SELECT actual_state, actual_state_desc, readonly_reason 
  FROM sys.database_query_store_options;

• An actual_state_desc of READ_WRITE confirms initialization has completed successfully; a temporary READ_ONLY state during startup is expected behavior.
• Allow a few minutes for large databases, as initialization time scales with the size of the Query Store internal tables (plan_persist_query, plan_persist_plan, etc.).

Step 3: Validate Query Store Configuration and Disk I/O Health

• Confirm the Query Store MAX_STORAGE_SIZE_MB has not been exceeded by running:

  SELECT current_storage_size_mb, max_storage_size_mb 
  FROM sys.database_query_store_options;

• Check Windows Server 2025 Event Viewer (Application log, source “MSSQLSERVER”) for any concurrent disk latency or I/O subsystem warnings (Event ID 833) that could slow down the initialization read from the underlying data files (.mdf/.ldf) located in your configured SQLDATA path.
• If storage is near capacity, increase MAX_STORAGE_SIZE_MB via T-SQL:

  ALTER DATABASE [YourDB] SET QUERY_STORE (MAX_STORAGE_SIZE_MB = 2048);

Step 4: Suppress Future Log Noise (Optional)

• If this informational message is cluttering your logs and you wish to reduce verbosity, adjust the Query Store capture mode from ALL to AUTO to reduce overhead during re-initialization:

  ALTER DATABASE [YourDB] SET QUERY_STORE (QUERY_CAPTURE_MODE = AUTO);

• Restart the SQL Server service via services.msc or Restart-Service MSSQLSERVER in PowerShell (run as Administrator) only if you need to force a clean re-initialization cycle for testing purposes.

Alternative or Verification Steps

• Verify the fix by re-querying sys.database_query_store_options and confirming actual_state_desc returns READ_WRITE with no further 12441 entries appearing in the SQL Server Error Log after the initialization window passes.
• If Query Store remains stuck in an initializing or read-only state for an extended period (beyond 10-15 minutes on a reasonably sized database), run ALTER DATABASE [YourDB] SET QUERY_STORE = OFF; followed by ALTER DATABASE [YourDB] SET QUERY_STORE = ON; to force a clean reset of the feature.

Troubleshooting Guide: SQL Server Error 12442 – Query Store Flush Failure

Why This Error Happens

Error 12442 occurs when SQL Server 2025 attempts to asynchronously flush Query Store data from memory to the underlying disk storage but encounters an internal error during this write operation. This is most commonly caused by insufficient disk space on the volume hosting the database files, corrupted Query Store internal tables within the database, or restrictive file-system permissions on the data file path that prevent the SQL Server service account from completing the write. In some cases, this also surfaces when the database is in a read-only or suspect state at the moment the flush is triggered.

Step-by-Step Resolution Guide

Step 1: Verify Disk Space and File System Health

• Check the drive hosting your .mdf and .ldf files (commonly C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\DATA) for available free space.
• Run Get-PSDrive in PowerShell on Windows Server 2025 to confirm the volume isn’t near capacity.
• Run CHKDSK D: /F /R (replace D: with your data drive letter) to rule out underlying disk corruption.

Step 2: Validate Query Store Internal State

• Connect via SSMS and run SELECT * FROM sys.database_query_store_options; to confirm the actual_state is not stuck in an error state.
• If the state shows ERROR_IO, execute ALTER DATABASE [YourDB] SET QUERY_STORE = OFF; followed by ALTER DATABASE [YourDB] SET QUERY_STORE = ON; to reset the internal flush mechanism.
• Run DBCC CHECKDB ('YourDB') WITH NO_INFOMSGS, ALL_ERRORMSGS; to confirm there is no underlying page corruption in the Query Store catalog views.

Step 3: Confirm Service Account Permissions

• Open Services.msc on Windows Server 2025 and verify the account running “SQL Server (MSSQLSERVER)”.
• Right-click the DATA folder path, go to Properties > Security, and ensure the SQL Server service account has Full Control (Read, Write, Modify).
• Restart the SQL Server service via Restart-Service MSSQLSERVER -Force after adjusting permissions.

Step 4: Clear and Rebuild Query Store Data (If Corruption Persists)

• If errors continue after state reset, purge the corrupted data using ALTER DATABASE [YourDB] SET QUERY_STORE CLEAR;.
• This forces SQL Server to rebuild the internal Query Store schema from scratch on the next write cycle.

Alternative or Verification Steps

• To verify the fix, re-run SELECT * FROM sys.database_query_store_options; and confirm actual_state_desc returns READ_WRITE with no readonly_reason flags set.
• If the error persists after these steps, check the Windows Event Viewer under Applications and Services Logs > MSSQLSERVER for underlying Error: 823 or 824 messages, which indicate hardware-level I/O subsystem failures requiring a storage hardware diagnostic.

Why This Error Happens

Error 12440 is an informational/state-change message that SQL Server 2025 writes to the error log whenever the query_store database option is toggled (e.g., OFF to ON, or READ_WRITE to READ_ONLY). It typically surfaces as a troubleshooting concern when it appears unexpectedly, repeats in a loop, or is immediately followed by a failure—usually because the Query Store internal tables are corrupted, the database is in a read-only or restricted state, or the service account lacks sufficient permissions on the system database files to persist the new Query Store state. In most cases, the underlying trigger is disk I/O pressure, a full data/log volume, or an inconsistent Query Store metadata catalog left over from an improper shutdown.

Step-by-Step Resolution Guide

Step 1: Confirm the Current Query Store State

• Open SQL Server Management Studio (SSMS) or Azure Data Studio connected to the instance.
• Run: SELECT actual_state_desc, readonly_reason FROM sys.database_query_store_options;
• Check the SQL Server Error Log (Management > SQL Server Logs in SSMS, or C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG) for repeated Error 12440 entries and any accompanying error codes (e.g., 3906, 1101, or 9002).

Step 2: Check Disk Space and File Permissions

• Verify the drive hosting the database’s data and log files (commonly D:\SQLData\ or E:\SQLLog\ on Windows Server 2025) has at least 20% free space.
• Confirm the SQL Server service account (visible in SQL Server Configuration Manager) has Full Control NTFS permissions on the database file directory.
• If the volume is full, free space or expand the VHDX/disk, then retry: ALTER DATABASE [YourDB] SET QUERY_STORE = ON;

Step 3: Clear or Rebuild a Corrupted Query Store Catalog

• If the option still fails to apply, force Query Store off and clear its internal data:
  ALTER DATABASE [YourDB] SET QUERY_STORE = OFF;
ALTER DATABASE [YourDB] SET QUERY_STORE CLEAR;
• Re-enable it with explicit sizing to avoid quota-related failures:
  ALTER DATABASE [YourDB] SET QUERY_STORE = ON (OPERATION_MODE = READ_WRITE, MAX_STORAGE_SIZE_MB = 1024);

Step 4: Validate Database State and Consistency

• Ensure the database isn’t in RESTRICTED_USER, EMERGENCY, or READ_ONLY mode:
  SELECT state_desc, is_read_only FROM sys.databases WHERE name = 'YourDB';
• Run DBCC CHECKDB('YourDB') WITH NO_INFOMSGS, ALL_ERRORMSGS; to rule out page-level corruption affecting the query_store internal tables.

Step 5: Restart the SQL Server Service (If Needed)

• Use SQL Server Configuration Manager or PowerShell: Restart-Service -Name 'MSSQLSERVER' to clear any cached metadata locks tied to the Query Store feature.
• Re-run the ALTER DATABASE statement after the service comes back online.

Alternative or Verification Steps

• Confirm success by querying sys.database_query_store_options again and verifying actual_state_desc reflects the desired state (READ_WRITE) with no readonly_reason codes.
• If the issue persists, as a fallback, detach and reattach the database (after a verified backup) to force SQL Server to rebuild internal metadata structures, then reapply the Query Store settings.

Troubleshooting Guide: Error 12438 – Query Store Cannot Be Enabled on System Database

Why This Error Happens

This error occurs because SQL Server 2025 intentionally blocks Query Store from being enabled on system databases such as master, model, msdb, and tempdb. Query Store is designed exclusively for user databases that track query execution history and performance regression data, so the engine rejects ALTER DATABASE SET QUERY_STORE commands targeting these protected databases. This is typically triggered by a maintenance script, third-party monitoring tool, or automation task that loops through all databases (including system ones) without filtering them out.

Step-by-Step Resolution Guide

Step 1: Identify the Source of the Command

• Check SQL Server Agent job steps, PowerShell/dbatools scripts, or third-party monitoring tools (e.g., SolarWinds DPA, Redgate, Idera) that may be iterating through sys.databases.
• Run the following query to confirm which database triggered the error based on recent session history:

  SELECT name, database_id, is_query_store_on 
  FROM sys.databases 
  WHERE database_id <= 4;

Step 2: Filter Out System Databases in Your Script or Tool

• Modify any automation script to exclude system databases explicitly using database_id > 4 or a NOT IN ('master','model','msdb','tempdb') clause.
• Example corrected T-SQL snippet:

  SELECT name FROM sys.databases 
  WHERE database_id > 4 AND state = 0;

• If using dbatools, ensure Set-DbaDbQueryStoreOption includes -ExcludeDatabase 'master','model','msdb','tempdb'.

Step 3: Disable Query Store Enforcement Attempts via Policy-Based Management (If Applicable)

• Navigate to SQL Server Management Studio (SSMS) > Management > Policy Management.
• Review any existing Policies under Database facet that may be forcing Query Store settings across all databases.
• Edit the policy’s Condition to add a filter: @Name NOT IN ('master','model','msdb','tempdb').

Step 4: Restart the SQL Server Agent Service (If Job-Based)

• Open Services.msc on Windows Server 2025 / Windows 11.
• Locate SQL Server Agent (MSSQLSERVER) or the named instance equivalent.
• Right-click and select Restart to clear any cached job execution context after script correction.

Alternative or Verification Steps

• Verify the fix by manually running ALTER DATABASE [YourUserDB] SET QUERY_STORE = ON; on a non-system database to confirm Query Store functions correctly elsewhere.
• If the error persists in automated jobs, enable SQL Server Profiler or Extended Events to trace the exact ALTER DATABASE statement being executed and its originating login/application name.

Troubleshooting Guide: SQL Server Error 12439 — Query Store Configuration Change

Why This Error Happens

Error 12439 is actually an informational message, not a true failure condition; it is logged whenever the Query Store engine successfully applies a configuration change (such as OPERATION_MODE, MAX_STORAGE_SIZE_MB, or QUERY_CAPTURE_MODE) to a specific database. However, this message is often mistaken for an error when it appears unexpectedly during automated maintenance scripts, Availability Group failovers, or when Query Store silently flips to READ_ONLY mode due to the storage limit being reached. The underlying root cause of concern is typically disk space exhaustion in the Query Store internal tables or a permissions mismatch preventing the ALTER DATABASE SET command from persisting correctly to the metadata catalog.

Step-by-Step Resolution Guide

Step 1: Verify the Query Store Storage Status

• Open SSMS and connect to the SQL Server 2025 instance.
• Run the following query to check current space usage: SELECT actual_state_desc, current_storage_size_mb, max_storage_size_mb FROM sys.database_query_store_options;
• If actual_state_desc returns READ_ONLY, this confirms the error is triggered by the 10% “optimize for” threshold being hit.

Step 2: Clear Stale Query Store Data

• Execute a cleanup procedure to force the engine to purge aged runtime statistics: ALTER DATABASE [YourDB] SET QUERY_STORE CLEAR;
• Alternatively, purge only the query text cache using: EXEC sp_query_store_flush_db;
• Restart the SQL Server Agent service via services.msc on Windows Server 2025 to clear any orphaned background threads handling the write-back task.

Step 3: Validate Database Permissions

• Ensure the service account running the SQL Server (MSSQLSERVER) instance has db_owner or specifically the ALTER DATABASE permission on the target database.
• Check the SQL Server error log located at C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG for concurrent permission-denied entries (Error 229 or 300) that might be blocking the metadata write.

Step 4: Resize the Query Store Capacity

• If disk space is the limiting factor, increase the MAX_STORAGE_SIZE_MB parameter to prevent immediate recurrence: ALTER DATABASE [YourDB] SET QUERY_STORE (MAX_STORAGE_SIZE_MB = 2000);
• Verify the underlying disk volume (e.g., D:\SQLData) has sufficient free space using File Explorer or Get-PSDrive D in PowerShell.

Alternative or Verification Steps

• Verification: Re-run SELECT * FROM sys.database_query_store_options; and confirm actual_state_desc now shows READ_WRITE instead of READ_ONLY.
• Alternative: If the error persists across reboots, disable and re-initialize Query Store entirely using ALTER DATABASE [YourDB] SET QUERY_STORE = OFF; followed by ALTER DATABASE [YourDB] SET QUERY_STORE = ON; to rebuild the internal storage schema from scratch.

Why This Error Happens

Error 12436 occurs when SQL Server’s Query Store attempts to associate a query with a Resource Governor workload group, but the classification engine cannot resolve the global default Resource Group at the time of capture. This typically happens when Resource Governor is in a mid-reconfiguration state (ALTER RESOURCE GOVERNOR RECONFIGURE was run but not completed cleanly), or when the internal default pool/group metadata has become inconsistent due to a failed upgrade or corrupted system catalog views. It can also surface when Query Store is enabled on a database before Resource Governor classification has fully initialized after a service restart.

Step-by-Step Resolution Guide

Step 1: Verify Resource Governor State

• Run SELECT * FROM sys.dm_resource_governor_configuration; to confirm whether Resource Governor is enabled and whether is_reconfiguration_pending is set to 1.
• Check SELECT * FROM sys.resource_governor_workload_groups; to ensure the default group under the internal and default pools still exists and has not been renamed or dropped.
• If is_reconfiguration_pending returns 1, this confirms a stuck reconfiguration is the likely cause.

Step 2: Force a Clean Resource Governor Reconfiguration

• Open a new query window in SQL Server Management Studio (SSMS) connected via Dedicated Administrator Connection (DAC) if the instance is unresponsive to normal connections.
• Execute: ALTER RESOURCE GOVERNOR DISABLE; followed by ALTER RESOURCE GOVERNOR RECONFIGURE;
• Re-enable with ALTER RESOURCE GOVERNOR RECONFIGURE; to rebuild the internal classification cache and restore the default group mapping.

Step 3: Restart the SQL Server Service Cleanly

• On Windows Server 2025 / Windows 11, open Services.msc (or use PowerShell: Restart-Service -Name 'MSSQLSERVER' -Force).
• Confirm the SQL Server Agent and Resource Governor dependent services restart without error in Event Viewer under Applications and Services Logs > MSSQL Server.
• This clears any cached classification state left over from the failed reconfiguration.

Step 4: Rebuild Query Store Metadata (If Error Persists)

• Disable Query Store on the affected database: ALTER DATABASE [YourDB] SET QUERY_STORE = OFF;
• Clear existing data: ALTER DATABASE [YourDB] SET QUERY_STORE CLEAR;
• Re-enable Query Store: ALTER DATABASE [YourDB] SET QUERY_STORE = ON (OPERATION_MODE = READ_WRITE);
• This forces SQL Server to re-link Query Store’s classification hooks to the now-repaired Resource Governor groups.

Step 5: Validate System Catalog Integrity

• Run DBCC CHECKCATALOG('master'); and DBCC CHECKCATALOG('msdb'); to rule out underlying metadata corruption.
• If corruption is found, restore the master database from a known-good backup taken before the error first appeared.

Alternative or Verification Steps

• Confirm the fix by running a representative workload and checking SELECT * FROM sys.query_store_runtime_stats; alongside SELECT * FROM sys.dm_resource_governor_workload_groups; to ensure new entries populate without error 12436 appearing in the SQL Server error log.
• If the issue persists after all steps, as a workaround, temporarily disable Resource Governor entirely (EXEC sp_configure 'resource governor', 0; RECONFIGURE; is not valid syntax — instead use ALTER RESOURCE GOVERNOR DISABLE;) while keeping Query Store active, then re-enable Resource Governor once Microsoft support confirms a root-cause patch for the specific SQL Server 2025 build via Cumulative Update.

Why This Error Happens

Error 12437 occurs when SQL Server’s Query Store feature attempts to associate query execution statistics with a Resource Governor workload group, but the global Resource Group context cannot be resolved at runtime. This typically happens when the Resource Governor classifier function is in an inconsistent state, when the sys.resource_governor_workload_groups metadata is corrupted, or when Query Store’s internal catalog views lose synchronization with Resource Governor after an in-place upgrade to SQL Server 2025. It can also surface when Resource Governor is disabled mid-session while Query Store is actively trying to capture runtime stats tied to a workload group.

Step-by-Step Resolution Guide

Step 1: Verify Resource Governor State and Configuration

• Connect via SSMS or sqlcmd and run SELECT * FROM sys.resource_governor_configuration; to confirm Resource Governor is enabled and not mid-reconfiguration.
• Check is_reconfiguration_pending column — if it returns 1, run ALTER RESOURCE GOVERNOR RECONFIGURE; to force a clean reload.
• Review the classifier function (if any) under Databases > master > Programmability > Functions for logic errors that may return NULL or invalid group names.

Step 2: Reset and Reapply Resource Governor Defaults

• Run ALTER RESOURCE GOVERNOR DISABLE; followed immediately by ALTER RESOURCE GOVERNOR RECONFIGURE; to clear any stuck internal state.
• Re-enable with ALTER RESOURCE GOVERNOR RECONFIGURE; and ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION = NULL); if a faulty classifier function was previously assigned.
• Restart the SQL Server service via services.msc on Windows Server 2025 / Windows 11 to flush cached Resource Governor metadata.

Step 3: Rebuild Query Store Metadata

• Disable and re-enable Query Store on the affected database:
  ALTER DATABASE [YourDB] SET QUERY_STORE = OFF;
ALTER DATABASE [YourDB] SET QUERY_STORE = ON;
• Clear existing Query Store data with ALTER DATABASE [YourDB] SET QUERY_STORE CLEAR; to remove any orphaned references to invalid Resource Groups.
• Check SQL Server error logs at C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG for recurrence of Error 12437 after the reset.

Step 4: Apply Latest Cumulative Update

• Confirm your build number with SELECT @@VERSION; and compare against the latest SQL Server 2025 Cumulative Update release notes.
• Download and install the current CU from the Microsoft Update Catalog, as this error has been linked to a known Resource Governor/Query Store synchronization bug in early 2025 RTM builds.
• Reboot the Windows Server 2025 / Windows 11 host after patching to ensure all SQL services reload cleanly.

Alternative or Verification Steps

• Run a test workload and query sys.query_store_runtime_stats and sys.resource_governor_workload_groups together to confirm Resource Group IDs now resolve without error.
• If the issue persists, temporarily disable Resource Governor entirely (ALTER RESOURCE GOVERNOR DISABLE;) to confirm Query Store operates normally, isolating the fault specifically to Resource Governor integration rather than Query Store itself.

Troubleshooting Guide: Error 12435 – Query Store Invalid Internal Table Structure

Why This Error Happens

Error 12435 occurs when the internal Query Store tables within a database become structurally corrupted, typically due to unexpected shutdowns, storage-level corruption, or interrupted upgrade processes between SQL Server versions. This can also happen if a database was restored or attached from an inconsistent backup, or if disk-level I/O errors silently altered the schema metadata of Query Store’s internal catalog views. Since Query Store relies on a fixed internal column structure to track query plans and runtime statistics, any mismatch—even a single column discrepancy—triggers this validation failure and disables Query Store functionality for that database.

Step-by-Step Resolution Guide

Step 1: Verify the Scope of Corruption

• Run DBCC CHECKDB (YourDatabaseName) WITH NO_INFOMSGS, ALL_ERRORMSGS; to confirm whether corruption extends beyond Query Store internal tables.
• Check the SQL Server Error Log (via SSMS or C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG) for accompanying messages referencing specific Query Store table names like plan_persistence or query_context_settings.

Step 2: Disable and Clear Query Store

• Execute ALTER DATABASE YourDatabaseName SET QUERY_STORE = OFF; to stop further writes to the corrupted structure.
• Run ALTER DATABASE YourDatabaseName SET QUERY_STORE CLEAR; only if the database is accessible and you have confirmed backups exist.
• If the CLEAR command fails due to the structural error, proceed to Step 3 instead of forcing it.

Step 3: Rebuild Query Store via Backup/Restore

• Take a full backup of the current (corrupted) database for forensic purposes: BACKUP DATABASE YourDatabaseName TO DISK = 'C:\Backups\YourDatabaseName_Corrupt.bak';
• Restore the last known good backup taken prior to the corruption event using SSMS or RESTORE DATABASE YourDatabaseName FROM DISK = 'C:\Backups\LastGood.bak' WITH REPLACE;
• Re-enable Query Store post-restore: ALTER DATABASE YourDatabaseName SET QUERY_STORE = ON;

Step 4: Apply Latest Cumulative Update

• Download and install the latest Cumulative Update (CU) for SQL Server 2025 from the Microsoft Update Catalog, as this error has known fixes in recent builds.
• Confirm the patch level using SELECT @@VERSION; after installation and reboot the Windows Server 2025/Windows 11 host if prompted.

Alternative or Verification Steps

• After remediation, run SELECT * FROM sys.database_query_store_options; to confirm actual_state_desc shows READ_WRITE instead of ERROR.
• If corruption persists after restore, consider migrating the database to a new database container via Export Data-Tier Application and re-importing, which rebuilds all internal structures including Query Store from scratch.

Troubleshooting Guide: Error 12433 – Query Store Disabled

Why This Error Happens

This error occurs because the Query Store feature has been explicitly disabled at either the database or server level, typically through an ALTER DATABASE SET QUERY_STORE = OFF command or a server-level policy restriction. It can also surface after a database migration or restore operation where Query Store settings were not preserved, or when the operating mode has been forced to “Read Only” or “Off” due to storage size limits being exceeded. Additionally, insufficient permissions on the msdb or user database can prevent the engine from reading the Query Store metadata correctly.

Step-by-Step Resolution Guide

Step 1: Verify Current Query Store Status

• Open SQL Server Management Studio (SSMS) and connect to the target instance.
• Run the following query to check the current state: SELECT actual_state_desc, readonly_reason FROM sys.database_query_store_options;
• Look specifically for a result of OFF or ERROR in the actual_state_desc column.

Step 2: Re-Enable Query Store via T-SQL

• Execute the command to force the feature back on at the database level: ALTER DATABASE [YourDatabaseName] SET QUERY_STORE = ON;
• If the database was previously using a “Read Only” mode due to disk space, specifically set the operation mode: ALTER DATABASE [YourDatabaseName] SET QUERY_STORE (OPERATION_MODE = READ_WRITE);
• Check the C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\DATA directory to ensure there is sufficient disk space for the feature to write logs.

Step 3: Check Server-Level Configuration and Permissions

• Navigate to the SQL Server Configuration Manager to ensure the SQL Server Agent service is running, as Query Store cleanup relies on background tasks.
• Verify the executing user has ALTER permission on the database by running: SELECT * FROM fn_my_permissions('YourDatabaseName', 'DATABASE');
• If running on Windows Server 2025, check the Local Group Policy Editor (gpedit.msc) under Computer Configuration > Administrative Templates for any restrictive SQL Server policies pushed via Group Policy.

Step 4: Restart the SQL Server Service

• Open the Services application (services.msc) on Windows 11 or Windows Server 2025.
• Locate SQL Server (MSSQLSERVER) and select Restart to clear any cached configuration states that might be causing the engine to misread the Query Store flag.
• Re-run the status check query from Step 1 to confirm the change has persisted after the restart.

Alternative or Verification Steps

• To verify the fix, navigate to Object Explorer in SSMS, right-click the database, select Properties, and check the Query Store tab to ensure the “Operation Mode (Requested)” is set to Read Write.
• If the error persists after enabling the feature, use the GUI Wizard specifically: right-click the Database > Reports > Standard Reports > Query Store Usage to force the engine to refresh the metadata cache.

Troubleshooting Guide: Error 12434 — “The Query Store in database %.*ls is invalid”

Why This Error Happens

This error typically occurs when the internal Query Store metadata or its underlying system tables become corrupted due to unexpected shutdowns, disk-level I/O failures, or abrupt termination of SQL Server processes during a Query Store write operation. It can also happen after a failed or interrupted upgrade/migration process where the Query Store catalog views lose synchronization with the physical storage structures. Less commonly, this is triggered by database corruption affecting the sys.plan_persist_* internal tables that Query Store relies on.

Step-by-Step Resolution Guide

Step 1: Verify Database and Query Store Status

• Run DBCC CHECKDB ('YourDatabaseName') WITH NO_INFOMSGS, ALL_ERRORMSGS; to rule out broader database corruption.
• Check the current Query Store state using:

  SELECT actual_state, actual_state_desc, readonly_reason 
  FROM sys.database_query_store_options;

• Review SQL Server Error Logs located at C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG for accompanying corruption messages (e.g., Error 823, 824, or 5180).

Step 2: Disable and Clear Query Store

• Force Query Store into OFF mode to release locks on corrupted metadata:

  ALTER DATABASE YourDatabaseName SET QUERY_STORE = OFF;

• Attempt to clear the existing cache data (this may fail if tables are severely corrupted, but is required before re-enabling):

  ALTER DATABASE YourDatabaseName SET QUERY_STORE CLEAR;

• If the CLEAR command hangs or times out, proceed to Step 3 for a manual reset.

Step 3: Manual Reset via System Table Purge (If Step 2 Fails)

• If standard clearing fails, you may need to manually truncate the internal persisted tables. Place the database in single-user mode first:

  ALTER DATABASE YourDatabaseName SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

• Use a dedicated admin connection (DAC) to manually drop the Query Store internal tables if corruption persists (consult Microsoft support for specific internal table names in SQL Server 2025, as these are build-specific).
• Return the database to multi-user mode: ALTER DATABASE YourDatabaseName SET MULTI_USER;

Step 4: Re-enable and Rebuild Query Store

• Turn the feature back on with default capture settings:

  ALTER DATABASE YourDatabaseName SET QUERY_STORE = ON 
  (OPERATION_MODE = READ_WRITE, DATA_FLUSH_INTERVAL_SECONDS = 900);

• Allow the system to rebuild the Query Store catalog by running a few test queries against the database to force new data capture.

Alternative or Verification Steps

• Verification: Run SELECT * FROM sys.database_query_store_options; and confirm actual_state_desc returns READ_WRITE rather than ERROR or READ_ONLY.
• Alternative Fix: If the error persists after a manual reset, restore the database from the last known good backup taken prior to the corruption event, as this indicates physical-level damage to the MDF/NDF files that cannot be resolved via T-SQL commands alone.

Troubleshooting Guide: Query Store Error 12431

Why This Error Happens

This error occurs when the Query Store background task cannot acquire an update lock on the database because another session or internal process is holding a conflicting lock, typically due to a long-running transaction, blocking session, or heavy concurrent write activity. It can also surface when the database is under memory pressure or experiencing I/O latency that delays lock acquisition within the internal timeout window. In rare cases, this is triggered by Query Store internal state corruption or disk-level contention preventing metadata updates.

Step-by-Step Resolution Guide

Step 1: Identify Blocking Sessions

• Run sp_who2 or query sys.dm_exec_requests to check for blocking SPIDs against your target database.
• Use the following query to pinpoint long-running or blocking transactions:

  SELECT blocking_session_id, session_id, wait_type, wait_time, wait_resource
  FROM sys.dm_exec_requests
  WHERE blocking_session_id <> 0;

• Terminate or wait out any long-held transactions using KILL <session_id> if safe to do so.

Step 2: Check Query Store Operational State

• Query the current Query Store state via:

  SELECT actual_state_desc, readonly_reason
  FROM sys.database_query_store_options;

• If readonly_reason indicates disk space or internal error flags, address the underlying storage issue first.
• Check available disk space on the drive hosting the database files (typically C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\DATA\).

Step 3: Adjust Query Store Flush and Capture Settings

• Reduce flush frequency to lower lock contention by running:

  ALTER DATABASE [YourDBName] SET QUERY_STORE (DATA_FLUSH_INTERVAL_SECONDS = 900);

• Temporarily set Query Store to READ_ONLY mode if the issue persists during peak write loads:

  ALTER DATABASE [YourDBName] SET QUERY_STORE = READ_ONLY;

Step 4: Clear and Rebuild Query Store Data

• If internal corruption is suspected, clear Query Store data using:

  ALTER DATABASE [YourDBName] SET QUERY_STORE CLEAR;

• Re-enable Query Store in READ_WRITE mode afterward and monitor the SQL Server Error Log at C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG for recurrence of Error 12431.

Alternative or Verification Steps

• Verify the fix by monitoring sys.database_query_store_options over a 24-hour cycle to confirm actual_state_desc remains READ_WRITE without reverting to read-only.
• If the error persists, disable Query Store entirely with ALTER DATABASE [YourDBName] SET QUERY_STORE = OFF;, apply the latest Windows Server 2025 and SQL Server 2025 cumulative updates, then re-enable it.

Troubleshooting Guide: SQL Server Error 12432 — Query Store Interval Length Invalid Value

Why This Error Happens

This error occurs when the INTERVAL_LENGTH_MINUTES parameter for Query Store is set to a value outside the accepted range or an invalid data type during an ALTER DATABASE statement. SQL Server 2025 strictly validates this parameter against a fixed set of acceptable integer values (1, 5, 10, 15, 30, 60, or 1440), and any value outside this list—including negative numbers, zero, or non-integer inputs—triggers a rejection at the parser level. This can also happen if a deployment script or ORM migration tool passes a default or null value due to a configuration mismatch.

Step-by-Step Resolution Guide

Step 1: Verify the Current Query Store Configuration

• Open SQL Server Management Studio (SSMS) or Azure Data Studio and connect to the target instance.
• Run the following query to inspect current settings:

  SELECT actual_state_desc, interval_length_minutes 
  FROM sys.database_query_store_options;

• Note the current interval_length_minutes value to compare against the value you are attempting to set.

Step 2: Correct the ALTER DATABASE Syntax

• Open a new query window in SSMS targeting the specific database (not master).
• Execute the corrected command using only a valid, whitelisted integer:

  ALTER DATABASE [YourDatabaseName] 
  SET QUERY_STORE (INTERVAL_LENGTH_MINUTES = 15);

• Ensure you are not accidentally passing a variable containing NULL or a string value (e.g., '15' instead of 15) if running this via a PowerShell or SQLCMD script.

Step 3: Check for Group Policy or Resource Governor Restrictions

• Navigate to the SQL Server Configuration Manager to ensure the instance isn’t restricted by a Resource Governor policy that locks configuration changes.
• On Windows Server 2025, check Local Group Policy Editor (gpedit.msc) under Computer Configuration > Administrative Templates to ensure no policy is blocking T-SQL execution contexts for schema changes.
• If using a CI/CD pipeline (e.g., Azure DevOps or GitHub Actions) to deploy the schema, check the pipeline variables file for a corrupted or empty $(QueryStoreInterval) placeholder.

Step 4: Restart the SQL Server Service (If Cache is Stale)

• Open Services.msc on your Windows Server 2025 host.
• Locate SQL Server (MSSQLSERVER) or your specific named instance.
• Right-click and select Restart to clear any cached invalid configuration states that might be causing the parser to loop on the old invalid value.

Alternative or Verification Steps

• Verification: Re-run the SELECT query from Step 1. If the interval_length_minutes column now reflects 15 (or your chosen valid value), the fix was successful.
• Alternative: If the T-SQL command continues to fail, reset Query Store entirely via the GUI by right-clicking the Database in SSMS, navigating to Properties > Query Store, and manually selecting a value from the dropdown menu for “Statistics Collection Interval” rather than using raw T-SQL.

Troubleshooting Guide: Error 12430 in Microsoft SQL Server 2025

Why This Error Happens

Error 12430 occurs when a stored procedure attempts to execute a Query Store action—such as forcing or unforcing a query plan—using a system procedure that is not valid within the current execution context. This typically happens when Query Store-related procedures (like sp_query_store_force_plan or sp_query_store_unforce_plan) are called from within another stored procedure, trigger, or nested transaction scope where such administrative actions are restricted. It can also arise if the Query Store is in a read-only state or if the target database does not support the specific Query Store operation being invoked.

Step-by-Step Resolution Guide

Step 1: Verify Query Store Operational State

• Check the current state of Query Store by running SELECT actual_state_desc FROM sys.database_query_store_options;
• Confirm the state is READ_WRITE and not READ_ONLY or ERROR.
• If the state shows READ_ONLY, review disk space on the data drive, as Query Store often switches to read-only when storage limits are exceeded.

Step 2: Execute Query Store Procedures Outside Nested Contexts

• Avoid calling sp_query_store_force_plan, sp_query_store_unforce_plan, or similar procedures from inside another stored procedure or trigger.
• Move the Query Store action to a standalone batch or script executed directly against the database.
• Example: Run EXEC sp_query_store_force_plan @query_id = 123, @plan_id = 456; as a top-level command in SQL Server Management Studio (SSMS).

Step 3: Validate Database Compatibility Level

• Ensure the database compatibility level supports the Query Store feature you are invoking.
• Run SELECT compatibility_level FROM sys.databases WHERE name = 'YourDatabaseName';
• If necessary, update it using ALTER DATABASE YourDatabaseName SET COMPATIBILITY_LEVEL = 170; (adjust to the appropriate level for SQL Server 2025).

Step 4: Reset Query Store Settings

• If the error persists, clear and reconfigure Query Store settings using ALTER DATABASE YourDatabaseName SET QUERY_STORE = OFF; followed by ALTER DATABASE YourDatabaseName SET QUERY_STORE = ON;
• Reapply custom configuration options such as QUERY_CAPTURE_MODE and MAX_STORAGE_SIZE_MB after re-enabling.

Alternative or Verification Steps

• After applying the fixes, re-run the original Query Store action directly in SSMS (not nested in a procedure) and confirm no error 12430 is returned.
• If the issue continues, check SQL Server Error Logs located at C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG for additional context on the failure, or use sp_query_store_consistency_check to rule out internal Query Store corruption.

Troubleshooting Guide: Error 12428 – Query Store Missing Internal Table

Why This Error Happens

Error 12428 occurs when the Query Store feature’s internal system tables within a user database have become corrupted, were partially deleted, or failed to initialize correctly during an upgrade or restore operation. This commonly happens after an improper database restore from an incompatible version, a failed in-place upgrade to SQL Server 2025, or disk-level corruption affecting system metadata pages. It can also surface if Query Store was manually manipulated or if a crash occurred during a Query Store cleanup/flush operation, leaving the internal schema in an inconsistent state.

Step-by-Step Resolution Guide

Step 1: Verify the Scope of Corruption

• Run DBCC CHECKDB (YourDatabaseName) WITH NO_INFOMSGS, ALL_ERRORMSGS; to confirm whether corruption extends beyond Query Store.
• Check the SQL Server Error Log (via SSMS or C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG) for the exact internal table name referenced in the 12428 message.
• Cross-reference the timestamp of the error with any recent restore, upgrade, or failover events in Windows Event Viewer.

Step 2: Disable and Clear Query Store

• Execute ALTER DATABASE YourDatabaseName SET QUERY_STORE = OFF; to stop active writes to the broken internal tables.
• Attempt a clean reset using ALTER DATABASE YourDatabaseName SET QUERY_STORE CLEAR; — this often rebuilds the internal schema if corruption is isolated.
• If the CLEAR command fails or hangs, proceed to rebuilding Query Store entirely in the next step.

Step 3: Rebuild Query Store via Database Restore or Recreation

• If the database has a recent clean backup, restore it to a point before the corruption using RESTORE DATABASE YourDatabaseName FROM DISK = 'D:\Backups\YourDatabaseName.bak' WITH REPLACE;.
• If no clean backup exists, re-enable Query Store from scratch: ALTER DATABASE YourDatabaseName SET QUERY_STORE = ON (OPERATION_MODE = READ_WRITE);
• For persistent corruption, export critical data to a new database using the Generate Scripts Wizard in SSMS, then drop and recreate the original database.

Step 4: Apply Latest Cumulative Update

• Confirm your instance is patched to the latest SQL Server 2025 Cumulative Update (CU) via SELECT @@VERSION;.
• Download and install any pending CU from the Microsoft Update Catalog, as early 2025 builds had known Query Store metadata bugs resolved in later patches.
• Restart the SQL Server service (services.msc → SQL Server (MSSQLSERVER) → Restart) after patching.

Alternative or Verification Steps

• Confirm the fix by querying SELECT * FROM sys.database_query_store_options; — it should return a valid row with actual_state_desc showing READ_WRITE instead of throwing an error.
• If issues persist, run Query Store in a staging/test database first using ALTER DATABASE ... SET QUERY_STORE = ON; to isolate whether the problem is environment-specific or tied to the specific database’s internal structure.

Troubleshooting Guide: Error 12429 – Query Store Invalid Internal Table Structure

Why This Error Happens

This error occurs when the internal Query Store tables within a user database become corrupted or structurally inconsistent, often due to an improper shutdown, disk-level corruption, or a failed upgrade/migration process. It can also surface when a database is restored or attached from an incompatible or partially upgraded state, leaving the internal Query Store schema out of sync with what the SQL Server 2025 engine expects. In some cases, antivirus or backup software interfering with live database files while Query Store is actively writing data can trigger this structural mismatch.

Step-by-Step Resolution Guide

Step 1: Verify the Scope of Corruption

• Run DBCC CHECKDB (YourDatabaseName) WITH NO_INFOMSGS, ALL_ERRORMSGS; to confirm whether corruption is isolated to Query Store internal tables or affects the entire database.
• Check the SQL Server error log (default path: C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG) for additional context around Error 12429.
• Note the exact database name and timestamp when the error first appeared.

Step 2: Disable and Clear Query Store

• Open SQL Server Management Studio (SSMS) and connect to the affected instance.
• Run the following to disable Query Store temporarily:
• ALTER DATABASE YourDatabaseName SET QUERY_STORE = OFF;
• Clear any residual corrupted data once re-enabled:
• ALTER DATABASE YourDatabaseName SET QUERY_STORE CLEAR;
• If the database won’t allow this due to corruption, proceed to Step 3 before retrying.

Step 3: Rebuild Query Store Internal Tables

• Set the database to single-user mode to allow exclusive maintenance access:
• ALTER DATABASE YourDatabaseName SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
• Drop and recreate Query Store metadata by turning it off and back on:
• ALTER DATABASE YourDatabaseName SET QUERY_STORE = OFF;
• ALTER DATABASE YourDatabaseName SET QUERY_STORE = ON (OPERATION_MODE = READ_WRITE);
• Return the database to multi-user mode:
• ALTER DATABASE YourDatabaseName SET MULTI_USER;

Step 4: Restore from a Known Good Backup (If Corruption Persists)

• If Steps 1–3 fail to resolve the structural error, restore the database from the most recent clean backup taken before the corruption occurred.
• Use SSMS or RESTORE DATABASE YourDatabaseName FROM DISK = 'C:\Backups\YourDatabaseName.bak' WITH REPLACE;
• Re-enable Query Store only after confirming the restored database passes DBCC CHECKDB cleanly.

Step 5: Check for Underlying Disk or OS-Level Issues

• On Windows Server 2025 / Windows 11, run chkdsk C: /f /r (adjust drive letter as needed) during a scheduled maintenance window to rule out disk corruption.
• Review Windows Event Viewer under Windows Logs > System for Disk or NTFS warnings coinciding with the SQL error timestamp.
• Confirm antivirus exclusions are properly configured for SQL Server data/log directories (typically C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\DATA).

Alternative or Verification Steps

• After remediation, run SELECT * FROM sys.database_query_store_options; to confirm Query Store reports a healthy actual_state_desc of READ_WRITE with no error flags.
• If the issue recurs immediately after re-enabling Query Store, consider leaving it permanently disabled for that database and rely on Extended Events or Query Performance Insight as an alternative monitoring solution until the underlying storage issue is fully resolved.

Troubleshooting Guide: Error 12426 – Plan Cannot Be Deleted Due to Active Forcing Policy

Why This Error Happens

This error occurs when an attempt is made to delete a query plan from the Query Store while that specific plan is still marked as “Forced” (plan forcing is enabled on it). SQL Server 2025 blocks the deletion to prevent the Query Optimizer from losing its active guidance mechanism for that specific query, as removing a forced plan reference without first disabling the policy would orphan the forcing metadata. This typically happens during manual cleanup scripts or automated maintenance plans that attempt to purge the Query Store cache without first checking the is_forced_plan status in sys.query_store_plan.

Step-by-Step Resolution Guide

Step 1: Identify the Query and Plan ID

• Connect to the target instance via SQL Server Management Studio (SSMS) 21 or Azure Data Studio.
• Run the following query to cross-reference the Plan ID from the error message with its parent Query ID:

SELECT plan_id, query_id, is_forced_plan 
FROM sys.query_store_plan 
WHERE plan_id = [Your_Plan_ID_Here];

• Note the query_id returned, as you will need it for the next step.

Step 2: Unforce the Plan Before Deletion

• You cannot delete a plan while is_forced_plan equals 1. You must explicitly disable forcing first.
• Execute the sp_query_store_unforce_plan stored procedure using the IDs gathered in Step 1:

EXEC sp_query_store_unforce_plan @query_id = [Your_Query_ID], @plan_id = [Your_Plan_ID];

• Verify the change by re-running the SELECT statement from Step 1 to confirm is_forced_plan now shows 0.

Step 3: Retry the Deletion or Cleanup

• Once the plan is confirmed as unforced, you can safely remove it from the Query Store.
• Run the specific deletion procedure for the plan:

EXEC sp_query_store_remove_plan @plan_id = [Your_Plan_ID];

• If the error originated from an automated maintenance script (e.g., a “Purge Query Store” job), update that script’s T-SQL logic to include the unforce command prior to any remove_plan or remove_query calls.

Step 4: Check for Replication or Read-Replica Locks (If Applicable)

• If this instance is part of an Always On Availability Group, ensure you are executing the T-SQL commands on the Primary Replica.
• Query Store write operations, including unforcing plans, are rejected on secondary replicas, which can sometimes manifest as a blocking/locking issue resembling a forcing policy error during replication sync.

Alternative or Verification Steps

• Verification: Navigate to SSMS 21 > Database > Query Store > “Queries with Forced Plans” report. The specific Plan ID should no longer appear in this report, confirming the policy has been successfully removed.
• Alternative (GUI Method): If the T-SQL stored procedures fail due to a locked session, open the Query Store “Tracked Queries” report in SSMS, locate the specific Query ID, and manually click the “Unforce Plan” button in the toolbar before attempting to clear the cache again via the database properties menu.

Troubleshooting Guide: SQL Server Error 12427 – Query Store Operation Conflict

Why This Error Happens

This error occurs when an administrator or a maintenance script attempts to run an operation—such as clearing data, changing the Query Store capture mode, or modifying schema-related settings—while the Query Store feature is still actively enabled on the database. SQL Server 2025 blocks these specific operations because Query Store maintains an internal read/write state on its system tables that cannot be structurally altered or cleared while the engine is actively capturing telemetry. Essentially, the engine requires the feature to be in an “OFF” state to release the locks or schema definitions required for the requested maintenance task to complete safely.

Step-by-Step Resolution Guide

Step 1: Identify the Current Query Store State

• Open SQL Server Management Studio (SSMS) and connect to your Windows Server 2025 instance.
• Run the following query to check the current status of the specific database:

  SELECT actual_state_desc FROM sys.database_query_store_options;

• If the result returns READ_WRITE or READ_ONLY, you must proceed to disable it before continuing with your original administrative task.

Step 2: Disable Query Store via T-SQL

• Execute the following command to turn off the feature for the specific database causing the conflict:

  ALTER DATABASE [YourDatabaseName] SET QUERY_STORE = OFF;

• Wait for the command to return “Command(s) completed successfully” before moving to the next step.
• Avoid closing the SSMS session abruptly during this transition to prevent schema lock timeouts.

Step 3: Re-attempt the Original Operation

• Once the feature is confirmed as OFF, retry the specific administrative task that triggered Error 12427 (e.g., ALTER DATABASE ... SET QUERY_STORE CLEAR or schema modifications).
• This sequence is required because SQL Server 2025 does not allow simultaneous “clear” and “enable” states within the same transaction batch.

Step 4: Re-enable Query Store (Optional)

• If you require Query Store for performance monitoring, turn it back on using the GUI or T-SQL after your maintenance task is complete:

  ALTER DATABASE [YourDatabaseName] SET QUERY_STORE = ON;

• Configure the OPERATION_MODE to READ_WRITE in the database properties under the Query Store tab in SSMS.

Alternative or Verification Steps

• Verification: Re-run the SELECT actual_state_desc FROM sys.database_query_store_options; query to confirm the state matches your desired configuration (either OFF or READ_WRITE) without throwing Error 12427.
• Alternative: If the T-SQL command hangs due to active transactions, use the SSMS GUI: Right-click the Database > Properties > Query Store > set “Operation Mode (Requested)” to Off, then apply the changes before retrying your original task.

Troubleshooting Guide: SQL Server Error 12425

Why This Error Happens

This error occurs because the query you are attempting to delete is currently protected by an active Query Store forcing policy. SQL Server 2025 prevents the removal of query metadata while a specific execution plan is being forced on that query ID, as doing so would orphan the forcing policy and potentially destabilize execution plan consistency. This typically happens when an administrator attempts to clean up the Query Store catalog views or run sp_query_store_remove_query without first un-forcing the associated plan.

Step-by-Step Resolution Guide

Step 1: Identify the Forced Plan Status

• Open SQL Server Management Studio (SSMS) and connect to the target instance on Windows Server 2025.
• Run the following query to check the is_forced_plan column for the specific Query ID:
• SELECT * FROM sys.query_store_plan WHERE query_id = <Your_Query_ID> AND is_forced_plan = 1;
• Confirm that the force_failure_count and last_force_failure_reason are not null, indicating an active lock on the deletion process.

Step 2: Unforce the Execution Plan

• Before deleting the query, you must release the forcing policy using the system stored procedure sp_query_store_unforce_plan.
• Execute the following T-SQL command, replacing the IDs with your specific values:
• EXEC sp_query_store_unforce_plan @query_id = <Your_Query_ID>, @plan_id = <Your_Plan_ID>;
• Verify the command completes without returning Error 12425 or related permission errors (ensure you have db_owner or ALTER permissions on the database).

Step 3: Clear the Query from Query Store

• Once the plan is confirmed as unforced, proceed with the removal of the query entry.
• Run the cleanup procedure to remove the specific query data:
• EXEC sp_query_store_remove_query @query_id = <Your_Query_ID>;
• If the error persists, restart the SQL Server service via the Services app (services.msc) on Windows Server 2025 to clear any cached Query Store memory locks, then repeat the step.

Step 4: Check for Automatic Plan Correction Conflicts

• If SQL Server 2025’s automatic tuning feature (Automatic Plan Correction) is enabled, it may be re-forcing the plan immediately after you unforce it.
• Navigate to Database Properties > Automatic Tuning in SSMS and temporarily disable “Force Last Good Plan” for the duration of the maintenance window.
• Re-attempt the deletion command from Step 3 once automatic tuning is paused.

Alternative or Verification Steps

• Verify the Fix: Query the sys.query_store_query view to ensure the specific query_id no longer returns a row, confirming successful deletion.
• Alternative Solution: If the specific query cannot be unforced due to corruption, use ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE; followed by ALTER DATABASE [YourDB] SET QUERY_STORE CLEAR; to perform a full reset of the Query Store (Note: this will delete all Query Store history, not just the single query).

Troubleshooting Guide: SQL Server Error 12422 — Query Store Interval Length Could Not Be Changed

Why This Error Happens

This error occurs because Query Store pre-allocates runtime statistics intervals based on the current INTERVAL_LENGTH_MINUTES setting, and at least one of these intervals has a future-dated timestamp already committed to the internal metadata tables. When you attempt to change the interval length, SQL Server detects this conflicting future interval and blocks the operation to prevent data corruption or overlapping statistics windows. This typically happens after a server time change, a restored database with Query Store enabled, or when the system clock was temporarily set incorrectly.

Step-by-Step Resolution Guide

Step 1: Verify the Current Query Store Configuration and System Time

• Run SELECT * FROM sys.database_query_store_options; to inspect current_storage_size_mb, interval_length_minutes, and actual_state.
• Check the Windows Server 2025 / Windows 11 system clock via w32tm /query /status to confirm there is no time drift.
• Cross-reference sys.query_store_runtime_stats_interval for any start_time or end_time values that are later than the current server GETUTCDATE().

Step 2: Disable and Clear Query Store Temporarily

• Execute ALTER DATABASE [YourDatabaseName] SET QUERY_STORE = OFF; to safely stop new interval allocation.
• Run ALTER DATABASE [YourDatabaseName] SET QUERY_STORE CLEAR; only if historical query data is not needed (this purges existing runtime stats intervals, including the problematic future-dated one).
• If data must be preserved, instead manually flush pending writes with EXEC sp_query_store_flush_db; before proceeding.

Step 3: Re-enable Query Store with the Corrected Interval Length

• Turn Query Store back on with ALTER DATABASE [YourDatabaseName] SET QUERY_STORE = ON;.
• Set the desired interval using ALTER DATABASE [YourDatabaseName] SET QUERY_STORE (INTERVAL_LENGTH_MINUTES = 60); (adjust value as needed).
• Confirm no future intervals remain by re-querying sys.query_store_runtime_stats_interval.

Step 4: Restart the SQL Server Service (If Error Persists)

• Open Services.msc on Windows Server 2025 / Windows 11 and restart the SQL Server (MSSQLSERVER) service, or run net stop MSSQLSERVER && net start MSSQLSERVER from an elevated Command Prompt.
• This clears any cached in-memory Query Store metadata that may still reference the stale future interval even after a database-level clear.

Alternative or Verification Steps

• Verify the fix by running SELECT TOP 5 * FROM sys.query_store_runtime_stats_interval ORDER BY start_time DESC; and confirming no timestamps exceed the current system time.
• If the error recurs immediately, use ALTER DATABASE [YourDatabaseName] SET QUERY_STORE = OFF; followed by a full restart of the SQL Server instance before re-enabling, as this forces a clean reallocation of the interval metadata.

Troubleshooting Guide: SQL Server Error 12423 — Query Store Read/Write Failure

Why This Error Happens

Error 12423 occurs when the Query Store feature in SQL Server 2025 is unable to read from or write to its internal storage tables, typically because the Query Store has reached its configured maximum size and entered read-only mode, or because the database files hosting Query Store data are experiencing I/O contention or corruption. This can also happen when the disk volume hosting the data file runs out of free space, when the Query Store catalog views become internally inconsistent due to an abrupt shutdown, or when background cleanup tasks are blocked by long-running transactions holding locks on Query Store internal tables. In some cases, insufficient permissions for the SQL Server service account on the underlying file system path can also prevent the asynchronous flush-to-disk operation from completing.

Step-by-Step Resolution Guide

Step 1: Verify Query Store Size and Operation Mode

• Run the following query to check current state:

  SELECT actual_state_desc, current_storage_size_mb, max_storage_size_mb 
  FROM sys.database_query_store_options;

• If actual_state_desc returns READ_ONLY, the Query Store has hit its size cap and is no longer accepting writes.
• Confirm whether current_storage_size_mb is close to or equal to max_storage_size_mb.

Step 2: Increase Storage Limit or Force Cleanup

• Increase the max size allocation to give Query Store room to operate:

  ALTER DATABASE [YourDatabaseName] 
  SET QUERY_STORE (MAX_STORAGE_SIZE_MB = 2000);

• If disk space is constrained, instead force an immediate cleanup of stale data:

  ALTER DATABASE [YourDatabaseName] 
  SET QUERY_STORE CLEAR;

• Reset the state back to read-write after adjusting limits:

  ALTER DATABASE [YourDatabaseName] 
  SET QUERY_STORE (OPERATION_MODE = READ_WRITE);

Step 3: Check Underlying Disk Space and File Permissions

• Open Disk Management (diskmgmt.msc) on Windows Server 2025 / Windows 11 and confirm the volume hosting your .mdf/.ndf files has adequate free space (at least 15-20% free is recommended).
• Navigate to the data file directory (commonly C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\DATA\) and verify the SQL Server service account (e.g., NT SERVICE\MSSQLSERVER) has Full Control permissions.
• Use Services.msc to confirm the SQL Server Database Engine service is running under the expected account without recent credential changes.

Step 4: Review for Blocking Transactions

• Query active sessions to identify blocking on Query Store internal objects:

  SELECT blocking_session_id, session_id, wait_type, wait_time 
  FROM sys.dm_exec_requests 
  WHERE blocking_session_id <> 0;

• Terminate any long-running transaction identified as blocking using KILL <session_id> after confirming it is safe to do so.

Step 5: Restart Query Store Capture (If Still Failing)

• As a last resort, disable and re-enable Query Store to rebuild its internal state:

  ALTER DATABASE [YourDatabaseName] SET QUERY_STORE = OFF;
  ALTER DATABASE [YourDatabaseName] SET QUERY_STORE = ON;

• Monitor the SQL Server Error Log immediately after re-enabling for any recurrence of Error 12423.

Alternative or Verification Steps

• Re-run SELECT actual_state_desc FROM sys.database_query_store_options; and confirm it now reports READ_WRITE with no further 12423 entries appearing in the SQL Server Error Log or Windows Event Viewer (Application log).
• If the issue persists after all steps, consider migrating the database to a volume with faster I/O (SSD-backed storage) or temporarily set QUERY_STORE = OFF to isolate whether the issue is Query Store-specific versus a broader storage subsystem failure.

Troubleshooting Guide: Error 12421 – Insufficient Permissions for Query Store Stored Procedure

Why This Error Happens

Error 12421 occurs when a SQL Server login or user account attempts to execute a Query Store stored procedure (such as sys.sp_query_store_remove_query or sys.sp_query_store_flush_db) without being granted the required ALTER or CONTROL permission at the database level. This typically happens because Query Store management procedures require elevated, explicit permissions that are not included in standard roles like db_datareader or db_datawriter. Additionally, in Microsoft SQL Server 2025, tightened security defaults on Windows Server 2025 and Windows 11 environments mean that even members of certain custom roles may be excluded unless permissions are explicitly re-validated after an upgrade or migration.

Step-by-Step Resolution Guide

Step 1: Verify the Current User Permissions

• Open SQL Server Management Studio (SSMS) and connect to the affected instance.
• Run the following query to check the user’s current role memberships:

  SELECT dp.name AS PrincipalName, dp.type_desc, r.name AS RoleName
  FROM sys.database_role_members drm
  JOIN sys.database_principals dp ON drm.member_principal_id = dp.principal_id
  JOIN sys.database_principals r ON drm.role_principal_id = r.principal_id;

• Confirm whether the user belongs to db_owner or has been granted direct ALTER permissions.

Step 2: Grant the Required Query Store Permissions

• Execute the following command to grant the minimum required permission on the target database:

  USE [YourDatabaseName];
  GRANT ALTER ON DATABASE::[YourDatabaseName] TO [YourUserName];

• For broader administrative access, instead add the user to the db_owner role:

  ALTER ROLE db_owner ADD MEMBER [YourUserName];

• Ensure the change is applied at the specific database level, not just the server level.

Step 3: Restart the SQL Server Service to Clear Cached Permissions

• Open Services.msc on Windows Server 2025 or Windows 11.
• Locate SQL Server (MSSQLSERVER) or your named instance.
• Right-click and select Restart to flush cached permission tokens.
• Alternatively, run this command in an elevated PowerShell window:

  Restart-Service -Name 'MSSQLSERVER' -Force

Step 4: Re-Validate Permissions After SQL Server 2025 Security Policy Changes

• Check for any Group Policy or SQL Server Configuration Manager security templates that may override local grants.
• Navigate to SQL Server Configuration Manager > SQL Server Network Configuration and confirm no conflicting security policies are reapplying restrictive defaults.
• Re-run the GRANT statement from Step 2 if permissions appear to reset after a service restart.

Alternative or Verification Steps

• To verify the fix, log in as the affected user and re-run the Query Store procedure (e.g., EXEC sys.sp_query_store_flush_db;) to confirm Error 12421 no longer appears.
• If the issue persists, use the sp_helprotect system procedure to audit all effective permissions on the database and identify any conflicting Deny statements that may be overriding the Grant.

Troubleshooting Guide: SQL Server Error 12419 — Query Store Disabled

Why This Error Happens

This error occurs because the Query Store feature is either disabled at the database level or not supported in the current compatibility mode of the database. It can also happen if the database is in a READ_ONLY state, is a system database (like tempdb or master) that does not support Query Store, or if the database is still in the process of recovering from a failover. Since SQL Server 2025 relies on Query Store for execution plan forcing and performance insights, any command targeting this feature will fail immediately if the underlying mechanism is turned off.

Step-by-Step Resolution Guide

Step 1: Verify the Current Query Store Status

• Open SQL Server Management Studio (SSMS) and connect to your Windows Server 2025 instance.
• Run the following T-SQL query to check the current state:

  SELECT actual_state, actual_state_desc, readonly_reason 
  FROM sys.database_query_store_options;

• If actual_state_desc returns OFF, proceed to Step 2.

Step 2: Enable Query Store via T-SQL

• Execute the ALTER DATABASE command to turn the feature on for your specific database.
• Use the following syntax, replacing YourDatabaseName with your target DB:

  ALTER DATABASE YourDatabaseName 
  SET QUERY_STORE = ON;

• Ensure you have db_owner or sysadmin permissions before running this command, as insufficient permissions will also trigger a failure similar to Error 12419.

Step 3: Configure Query Store Operation Mode

• Simply turning the feature “ON” sometimes leaves it in a READ_ONLY capture mode, which can still throw errors for specific write operations.
• Set the operation mode explicitly to READ_WRITE to allow full functionality:

  ALTER DATABASE YourDatabaseName 
  SET QUERY_STORE (OPERATION_MODE = READ_WRITE);

• Check the disk space allocated to the database; if the MAX_STORAGE_SIZE_MB limit was previously hit, Query Store automatically reverts to READ_ONLY, requiring you to clear old data or increase the cap.

Step 4: Check Compatibility Level

• Confirm the database compatibility level supports the Query Store features you are trying to use (SQL Server 2025 requires Level 160 or higher for newest features).
• Run this command to update the compatibility level if needed:

  ALTER DATABASE YourDatabaseName 
  SET COMPATIBILITY_LEVEL = 160;

Alternative or Verification Steps

• Verify via GUI: Right-click the Database in SSMS Object Explorer, select Properties, navigate to the Query Store page, and confirm the “Operation Mode (Requested)” dropdown is set to Read Write.
• Alternative Fix: If the ALTER DATABASE command times out or fails due to a pending transaction, restart the SQL Server service via services.msc on your Windows Server 2025 machine, then re-run the Step 2 query to force the state change.

Troubleshooting Guide: SQL Server Error 12420 – Query Store Not Started Up

Why This Error Happens

This error occurs when the Query Store feature is enabled at the database metadata level, but the internal storage mechanism has not fully initialized during database startup. This is commonly caused by the database recovering in an inconsistent state, the Query Store internal tables being corrupted or inaccessible, or the database being in a READ_ONLY, EMERGENCY, or SUSPECT state that prevents Query Store from completing its load sequence. In SQL Server 2025, this can also surface if the database was restored from an older compatibility level where Query Store schema changes are still pending an upgrade step.

Step-by-Step Resolution Guide

Step 1: Verify Database and Query Store Status

• Run the following query to check the current state of Query Store for the affected database:

SELECT actual_state, actual_state_desc, readonly_reason 
FROM sys.database_query_store_options;

• Confirm the database itself is ONLINE by checking:

SELECT name, state_desc FROM sys.databases WHERE name = 'YourDatabaseName';

• If state_desc is not ONLINE, address the underlying database state issue first (e.g., restore from backup, run ALTER DATABASE SET ONLINE).

Step 2: Disable and Re-Enable Query Store

• Attempt a clean reset of the Query Store feature using T-SQL:

ALTER DATABASE [YourDatabaseName] SET QUERY_STORE = OFF;
GO
ALTER DATABASE [YourDatabaseName] SET QUERY_STORE = ON;
GO

• This forces SQL Server to reinitialize the internal Query Store catalog views and storage structures.
• If the command hangs or times out, proceed to Step 3 to check for blocking or corruption.

Step 3: Check for Blocking Sessions or Pending Upgrades

• Query sys.dm_exec_requests and sys.dm_tran_locks to ensure no other session is holding a schema lock on the database during startup.
• If the database was recently upgraded from SQL Server 2019/2022, run:

ALTER DATABASE [YourDatabaseName] SET COMPATIBILITY_LEVEL = 170;

• Restart the SQL Server service via services.msc on Windows Server 2025 to force a clean re-attach of all system databases.

Step 4: Rebuild Query Store Storage (If Corrupted)

• If the error persists, the internal Query Store tables may be corrupted. Run DBCC CHECKDB against the database to identify corruption:

DBCC CHECKDB('YourDatabaseName') WITH NO_INFOMSGS, ALL_ERRORMSGS;

• If corruption is confirmed specifically in Query Store internal objects, the safest fix is to clear the Query Store data entirely:

ALTER DATABASE [YourDatabaseName] SET QUERY_STORE CLEAR;

• Note: This step requires Query Store to be in an ON state first; if it cannot reach ON state, you may need to drop and recreate the database’s Query Store via backup/restore to a new database file group.

Alternative or Verification Steps

• Verify the fix by re-running SELECT actual_state_desc FROM sys.database_query_store_options; and confirming the output reads READ_WRITE instead of ERROR or PENDING.
• If the issue persists after a service restart, check the SQL Server Error Log (Management > SQL Server Logs in SSMS) for underlying I/O or tempdb-related errors that may be preventing the Query Store disk structures from loading.

Troubleshooting Guide: Error 12417 — “Only One Query Store Option Can Be Given in ALTER DATABASE Statement”

Why This Error Happens

This error occurs when an ALTER DATABASE statement attempts to set multiple Query Store options within a single SET clause without properly separating them using the correct syntax structure. SQL Server 2025 parses the ALTER DATABASE ... SET QUERY_STORE command expecting only one option assignment per statement block unless explicitly grouped using parentheses with comma separation in the correct clause format. This is typically caused by malformed T-SQL scripts, often introduced by manually edited deployment scripts, outdated migration tools, or copy-pasted code from older SQL Server versions (2016–2019) where Query Store syntax requirements were less strict.

Step-by-Step Resolution Guide

Step 1: Identify the Offending T-SQL Statement

• Open SQL Server Management Studio (SSMS) or Azure Data Studio and locate the exact ALTER DATABASE statement that triggered Error 12417.
• Check the SQL Server Error Log via Object Explorer > Management > SQL Server Logs or query it directly:

  EXEC sp_readerrorlog 0, 1, 'Query Store';

• Confirm the statement includes multiple Query Store options (e.g., OPERATION_MODE, DATA_FLUSH_INTERVAL_SECONDS, MAX_STORAGE_SIZE_MB) combined incorrectly.

Step 2: Correct the ALTER DATABASE Syntax

• Rewrite the statement so that multiple Query Store options are enclosed in a single parenthetical block, separated by commas, rather than using multiple SET keywords.
• Incorrect syntax example (causes Error 12417):

  ALTER DATABASE [YourDB] SET QUERY_STORE = ON SET QUERY_STORE (OPERATION_MODE = READ_WRITE, MAX_STORAGE_SIZE_MB = 1000);

• Corrected syntax:

  ALTER DATABASE [YourDB] SET QUERY_STORE = ON;
  ALTER DATABASE [YourDB] SET QUERY_STORE (
      OPERATION_MODE = READ_WRITE,
      MAX_STORAGE_SIZE_MB = 1000,
      DATA_FLUSH_INTERVAL_SECONDS = 900,
      INTERVAL_LENGTH_MINUTES = 60
  );

• Save the corrected script to C:\Scripts\SQL\QueryStoreFix.sql for future deployment reuse.

Step 3: Validate Deployment and Migration Scripts

• Search all automated deployment pipelines (DACPAC, SSDT projects, or PowerShell DSC configs) for legacy ALTER DATABASE SET QUERY_STORE statements.
• Update any CI/CD templates stored in Azure DevOps or GitHub Actions YAML files that reference outdated Query Store syntax.
• Re-run the deployment in a staging environment on Windows Server 2025 before pushing to production.

Step 4: Apply the Fix to the Target Database

• Execute the corrected script against the target database using an account with ALTER permission on the database (minimum db_owner role).
• Run the command via SSMS or sqlcmd:

  sqlcmd -S localhost -d YourDB -i "C:\Scripts\SQL\QueryStoreFix.sql"

• Confirm no errors are returned in the output console.

Alternative or Verification Steps

• Verify the fix by querying the Query Store configuration state: SELECT * FROM sys.database_query_store_options; and confirm actual_state_desc returns READ_WRITE with no pending errors.
• If the error persists, drop and reinitialize Query Store entirely using ALTER DATABASE [YourDB] SET QUERY_STORE = OFF; followed by a clean re-enable with a single consolidated options block as shown in Step 2.

Troubleshooting Guide: Error 12418 — Mutually Incompatible Options in ALTER DATABASE Statement

Why This Error Happens

Error 12418 occurs when a single ALTER DATABASE statement attempts to change the database state (such as SET OFFLINE, SET ONLINE, SET EMERGENCY, or SET SINGLE_USER) in the same command as a SET QUERY_STORE option. SQL Server 2025 enforces strict separation between these two operation categories because database state transitions require exclusive control over the database, while Query Store configuration changes operate on the metadata layer and cannot be safely applied during a state transition. This is a syntax/batching issue rather than a permissions or corruption problem, and it almost always stems from a script or automation tool that concatenates both operations into one statement.

Step-by-Step Resolution Guide

Step 1: Identify the Offending ALTER DATABASE Statement

• Check the SQL Server error log or SSMS output pane for the exact statement that triggered Error 12418.
• Look specifically for combined clauses like SET SINGLE_USER paired with SET QUERY_STORE = ON in the same line.
• If this originated from an automated deployment script, locate the .sql file or DACPAC/DevOps pipeline step that generated it.

Step 2: Split the Statement into Two Separate ALTER DATABASE Commands

• Separate the database state change and Query Store configuration into distinct batches, for example:

  ALTER DATABASE [YourDB] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
  GO
  ALTER DATABASE [YourDB] SET QUERY_STORE = ON;
  GO

• Ensure each statement ends with a GO batch separator so SQL Server processes them independently.
• Save and re-run the corrected script against the target instance on Windows Server 2025 or Windows 11.

Step 3: Update Automation Scripts and Deployment Templates

• Review PowerShell, SSDT, or Azure DevOps YAML pipelines that generate ALTER DATABASE statements dynamically.
• Patch any template logic (e.g., in Publish.sql or post-deployment scripts) that concatenates state and Query Store options into a single call.
• Re-test the deployment in a staging environment before pushing to production.

Step 4: Apply Query Store Settings After Database Comes Online

• Confirm the database has fully transitioned to the desired state (ONLINE, MULTI_USER, etc.) using sys.databases.
• Only then issue the ALTER DATABASE ... SET QUERY_STORE command with your desired options (ON, CLEAR, OPERATION_MODE, etc.).
• Re-run the script and confirm no error 12418 is raised in the output.

Alternative or Verification Steps

• Verify the fix by querying sys.databases and sys.database_query_store_options to confirm both the database state and Query Store settings applied correctly: SELECT state_desc FROM sys.databases WHERE name = 'YourDB'; and SELECT actual_state_desc FROM sys.database_query_store_options;
• If the error persists, run each ALTER DATABASE command manually and interactively in SSMS (one statement per execution) to isolate which specific option combination is still being parsed together.

Troubleshooting Guide: Error 12415 – Failed to Add Query to Query Store

Why This Error Happens

Error 12415 typically occurs when SQL Server 2025 cannot write new query metadata into the Query Store internal tables, often because the Query Store has reached its configured maximum size limit or has entered a read-only state. This can also happen if the Query Store catalog views are corrupted due to an unexpected shutdown, disk I/O failure, or if the underlying database files are experiencing contention. Additionally, insufficient disk space on the volume hosting the database data files can prevent Query Store from flushing captured query data, triggering this error.

Step-by-Step Resolution Guide

Step 1: Check Query Store Operational State

• Run the following query to inspect the current state of Query Store for the affected database:

SELECT actual_state_desc, readonly_reason, current_storage_size_mb, max_storage_size_mb 
FROM sys.database_query_store_options;

• If actual_state_desc returns READ_ONLY, this confirms the storage limit has been reached or an internal error has forced it into a protective state.

Step 2: Increase Storage Size or Clear Query Store Data

• If current_storage_size_mb is near or equal to max_storage_size_mb, increase the limit using SQL Server Management Studio (SSMS) or T-SQL:

ALTER DATABASE [YourDatabaseName] 
SET QUERY_STORE (MAX_STORAGE_SIZE_MB = 2048);

• Alternatively, if the data is not critical or testing environment, clear the existing Query Store cache to reset the internal catalog:

ALTER DATABASE [YourDatabaseName] SET QUERY_STORE CLEAR;

Step 3: Verify Disk Space and File Growth Settings

• Navigate to the physical location of the database files, typically found at C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\DATA\ on Windows Server 2025.
• Ensure the volume has at least 15-20% free space, as Query Store requires workspace to write transactional metadata.
• Check the Autogrowth settings for the database to ensure files are not restricted to a fixed size that prevents expansion.

Step 4: Restart the Query Store Collection Background Task

• If the above steps do not immediately resolve the issue, force a restart of the Query Store internal task by toggling the operation mode:

ALTER DATABASE [YourDatabaseName] SET QUERY_STORE = OFF;
GO
ALTER DATABASE [YourDatabaseName] SET QUERY_STORE = ON;
GO

• This re-initializes the internal memory structures without requiring a full SQL Server service restart.

Alternative or Verification Steps

• To verify the fix, execute a new query against the affected database and check sys.query_store_query to confirm the query_id for your new query has been logged successfully.
• If the error persists after resizing, check the SQL Server Error Log via SQL Server Management Studio > Management > SQL Server Logs for underlying I/O or corruption errors (such as 824 or 825) that may require a DBCC CHECKDB repair.

Troubleshooting Guide: Error 12413 – Cannot Process Statement SQL Handle

Why This Error Happens

Error 12413 occurs when SQL Server’s Query Store attempts to resolve a query using a sql_handle that no longer exists in the plan cache, typically because the plan was evicted due to memory pressure or a server restart. This error commonly surfaces when DMV queries or monitoring tools (like sys.dm_exec_sql_text) reference a stale or invalidated handle instead of using the persisted Query Store text repository. Corrupted or fragmented Query Store metadata in the user database can also trigger this behavior, especially after an unexpected failover or improper shutdown.

Step-by-Step Resolution Guide

Step 1: Switch to Query Store Text Views Instead of Plan Cache

• Replace any query using sys.dm_exec_sql_text(sql_handle) with a join against sys.query_store_query_text.
• Example: SELECT qt.query_sql_text FROM sys.query_store_query AS q JOIN sys.query_store_query_text AS qt ON q.query_text_id = qt.query_text_id;
• Update any monitoring dashboards (SSMS reports, Grafana, or custom scripts) to reference Query Store views rather than live DMVs.

Step 2: Validate Query Store Health on the Database

• Open SQL Server Management Studio (SSMS) and connect to the instance.
• Right-click the affected database → Properties → Query Store → confirm Operation Mode is set to Read Write.
• Run SELECT * FROM sys.database_query_store_options; to check for a READ_ONLY state caused by storage size limits being hit.
• If the state is ERROR, run ALTER DATABASE [YourDB] SET QUERY_STORE = OFF; followed by ALTER DATABASE [YourDB] SET QUERY_STORE = ON; to reset it.

Step 3: Clear and Rebuild Corrupted Query Store Cache

• Execute ALTER DATABASE [YourDB] SET QUERY_STORE CLEAR; to purge stale handle references.
• Navigate to C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\DATA\ and verify the database’s .mdf/.ndf files are not flagged as read-only or locked by antivirus software.
• Restart the SQL Server service via Services.msc (SQL Server (MSSQLSERVER)) to flush the in-memory plan cache cleanly.

Step 4: Check for Insufficient Permissions on Query Store Views

• Ensure the login has VIEW DATABASE STATE permission by running: GRANT VIEW DATABASE STATE TO [YourLoginName];
• Without this permission, Query Store views return incomplete results, which can trigger fallback attempts to the invalid sql_handle.

Alternative or Verification Steps

• Verify the fix by running the same query through sys.query_store_query_text and confirming it returns the SQL text without throwing Error 12413.
• If the error persists, check Windows Event Viewer (Applications and Services Logs → MSSQLSERVER) for underlying I/O or memory pressure alerts that may indicate the plan cache is being evicted too aggressively, and consider increasing the max server memory setting in SQL Server configuration.

Troubleshooting Guide: SQL Server Error 12414 — Query Store Initialization Failure

Why This Error Happens

Error 12414 occurs when SQL Server 2025 cannot allocate or access the internal storage structures required to initialize Query Store for a database. This is typically caused by insufficient disk space on the volume hosting the database files, corrupted Query Store internal tables, or the database being in a restricted state (such as read-only, suspect, or emergency mode) during startup. It can also occur if the user account running the SQL Server service lacks the necessary NTFS permissions on the data file directory.

Step-by-Step Resolution Guide

Step 1: Verify Disk Space and File Accessibility

• Check available free space on the drive hosting your .mdf and .ldf files (navigate to Server Manager > Storage > Disks on Windows Server 2025).
• Ensure the volume has at least 20% free space, as Query Store requires room to expand internal tracking tables.
• Confirm the database files are not marked read-only via File Explorer properties or ATTRIB command in PowerShell.

Step 2: Check Database State and Permissions

• Open SQL Server Management Studio (SSMS) and run:
  SELECT name, state_desc FROM sys.databases WHERE name = 'YourDatabaseName';
• If the state is not ONLINE, restore the database or run ALTER DATABASE [YourDB] SET ONLINE;
• Verify the SQL Server service account (found in SQL Server Configuration Manager > SQL Server Services) has Full Control NTFS permissions on the data directory, typically located at C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\DATA.

Step 3: Rebuild or Reset Query Store

• Disable and re-enable Query Store using T-SQL:
  ALTER DATABASE [YourDB] SET QUERY_STORE = OFF;
ALTER DATABASE [YourDB] SET QUERY_STORE = ON (OPERATION_MODE = READ_WRITE);
• If corruption is suspected, run DBCC CHECKDB ('YourDB') WITH NO_INFOMSGS, ALL_ERRORMSGS; to identify and repair structural issues.

Step 4: Restart the SQL Server Service

• Open SQL Server Configuration Manager, right-click the SQL Server (MSSQLSERVER) instance, and select Restart.
• Monitor the SQL Server Error Log (Management > SQL Server Logs in SSMS) for confirmation that Query Store initialized successfully on startup.

Alternative or Verification Steps

• Confirm the fix by querying SELECT actual_state_desc FROM sys.database_query_store_options; — a healthy result should return READ_WRITE.
• If the error persists, consider restoring the database from a known-good backup, as this often indicates underlying corruption beyond Query Store’s internal metadata.

Troubleshooting Guide: SQL Server Error 12412 – Query Store Internal Table Access Failure

Why This Error Happens

Error 12412 occurs when the SQL Server Database Engine cannot read or write to the internal Query Store tables, which are physically stored as hidden system objects within the user database. This typically stems from database-level corruption affecting the Query Store schema, insufficient disk space preventing the engine from accessing or extending these internal structures, or a mismatch between the Query Store metadata and the actual data pages caused by an improper shutdown or storage failure. In rarer cases, restrictive file-system permissions on the database files (.mdf/.ndf) in Windows Server 2025 environments can block the Engine service account from reading these internal objects.

Step-by-Step Resolution Guide

Step 1: Verify Database Integrity

• Open SQL Server Management Studio (SSMS) and connect to the affected instance.
• Run DBCC CHECKDB ('YourDatabaseName') WITH NO_INFOMSGS, ALL_ERRORMSGS; to scan for corruption in system and Query Store tables.
• Review the output specifically for errors referencing sys.query_store_* internal objects.

Step 2: Check Disk Space and File Permissions

• Confirm the drive hosting your database files (e.g., D:\SQLData\YourDB.mdf) has sufficient free space; Query Store operations fail silently when disk space drops below operational thresholds.
• Navigate to the database file location in Windows Explorer, right-click properties, and ensure the NT SERVICE\MSSQLSERVER (or named instance equivalent) account has Full Control read/write permissions.
• Restart the SQL Server service via services.msc after adjusting permissions to force a clean file handle re-acquisition.

Step 3: Reset or Repair Query Store

• If corruption is isolated to Query Store and not the base tables, temporarily disable Query Store using:
  ALTER DATABASE YourDatabaseName SET QUERY_STORE = OFF;
• Clear the existing Query Store data to remove corrupted internal state:
  ALTER DATABASE YourDatabaseName SET QUERY_STORE CLEAR;
• Re-enable Query Store with a fresh internal schema:
  ALTER DATABASE YourDatabaseName SET QUERY_STORE = ON;

Step 4: Restore from Backup (If Corruption Persists)

• If DBCC CHECKDB reports unrepairable corruption in the Query Store internal tables, restore the database from the most recent clean backup taken prior to the error’s first occurrence.
• Use RESTORE DATABASE YourDatabaseName FROM DISK = 'BackupPath.bak' WITH REPLACE, RECOVERY; to complete the restoration.

Alternative or Verification Steps

• After applying fixes, run a test query that triggers Query Store writes (e.g., execute a stored procedure repeatedly) and check sys.query_store_runtime_stats to confirm data is populating without Error 12412 reappearing in the SQL Server Error Log.
• If the issue persists across multiple databases on the same instance, consider running Windows Server 2025 Storage Diagnostics (Get-PhysicalDisk | Get-StorageReliabilityCounter) to rule out underlying disk hardware failures affecting the SQL Server data volume.

Troubleshooting Guide: SQL Server Error 12410 — “Cannot Load the Query Store Metadata”

Why This Error Happens

This error typically occurs when the Query Store’s internal metadata tables become corrupted or are inaccessible due to a failed disk I/O operation, abrupt server shutdown, or database crash during a Query Store write operation. It can also occur if the database was restored or attached from a source with an incompatible or damaged Query Store catalog, or if the SQL Server service account lacks sufficient permissions on the database files. In some cases, this is triggered by insufficient disk space preventing the Query Store internal tables from initializing properly.

Step-by-Step Resolution Guide

Step 1: Verify Current Query Store Status and Disk Space

• Open SQL Server Management Studio (SSMS) and run the following query to check the current state:

  SELECT actual_state_desc, readonly_reason 
  FROM sys.database_query_store_options;

• Confirm the underlying disk volume (e.g., D:\SQLData\) has sufficient free space (at least 5–10 GB recommended).
• Check the SQL Server Error Log (via EXEC xp_readerrorlog; or Event Viewer under Windows Logs > Application) for related I/O or corruption warnings preceding Error 12410.

Step 2: Manually Disable and Re-Enable Query Store

• Run the following T-SQL command to forcibly turn off Query Store:

  ALTER DATABASE [YourDatabaseName] SET QUERY_STORE = OFF;

• Wait a few seconds, then re-enable it manually:

  ALTER DATABASE [YourDatabaseName] SET QUERY_STORE = ON 
  (OPERATION_MODE = READ_WRITE);

• If the command hangs or fails, proceed to clear the metadata directly in Step 3.

Step 3: Clear Corrupted Query Store Metadata

• Run the cleanup command to purge potentially corrupted internal tables:

  ALTER DATABASE [YourDatabaseName] SET QUERY_STORE CLEAR;

• If the CLEAR command itself fails due to metadata corruption, take the database offline and back online to reset internal caches:

  ALTER DATABASE [YourDatabaseName] SET OFFLINE;
  ALTER DATABASE [YourDatabaseName] SET ONLINE;

Step 4: Check File System Permissions (Windows Server 2025 / Windows 11)

• Navigate to the SQL Server data directory, typically located at:
  C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\DATA\
• Right-click the .mdf and .ldf files for the affected database, go to Properties > Security, and confirm the NT SERVICE\MSSQLSERVER account has Full Control.
• If permissions were recently altered (e.g., via a migration or restore), reset them to inherit from the parent DATA folder.

Step 5: Restart the SQL Server Service

• Open Services.msc on Windows Server 2025 or Windows 11.
• Locate SQL Server (MSSQLSERVER), right-click, and select Restart.
• Alternatively, use PowerShell:

  Restart-Service -Name 'MSSQLSERVER' -Force

Alternative or Verification Steps

• To verify the fix, re-run SELECT actual_state_desc FROM sys.database_query_store_options; and confirm it returns READ_WRITE instead of ERROR or OFF.
• If the issue persists after all steps, restore the database from the last known good backup, as this indicates deeper corruption in the primary filegroup beyond Query Store’s internal recovery capabilities.

Troubleshooting Guide: SQL Server Error 12411 — Cannot Load Forced Plan from Query Store

Why This Error Happens

Error 12411 typically occurs when the query plan stored in the Query Store has become invalid, incompatible, or corrupted due to schema changes, statistics drift, or an upgrade/migration event (such as moving to SQL Server 2025). This can also happen if the plan references objects (indexes, columns, or temp tables) that no longer exist in their original form, or if the Query Store’s internal metadata tables have inconsistencies. Additionally, insufficient permissions on the msdb or Query Store internal catalog views can prevent SQL Server from properly retrieving and deserializing the forced plan.

Step-by-Step Resolution Guide

Step 1: Verify Query Store Status and Configuration

• Open SQL Server Management Studio (SSMS) and connect to the affected instance.
• Run the following query to check Query Store health:

  SELECT actual_state_desc, readonly_reason 
  FROM sys.database_query_store_options;

• Confirm actual_state_desc returns READ_WRITE and not READ_ONLY or ERROR.
• If readonly_reason is non-zero, note the code for further diagnosis (e.g., disk space or corruption flags).

Step 2: Identify and Unforce the Problematic Plan

• Query the forced plans currently stored:

  SELECT plan_id, query_id, is_forced_plan, force_failure_count, last_force_failure_reason_desc
  FROM sys.query_store_plan
  WHERE is_forced_plan = 1;

• Locate the plan_id associated with the failure (cross-reference last_force_failure_reason_desc).
• Unforce the broken plan using:

  EXEC sys.sp_query_store_unforce_plan @query_id = <query_id>, @plan_id = <plan_id>;

Step 3: Clear Corrupted Query Store Cache

• If unforcing does not resolve the issue, clear the Query Store cache to remove stale metadata:

  ALTER DATABASE [YourDatabaseName] SET QUERY_STORE CLEAR;

• Restart the SQL Server service via Services.msc (Windows Server 2025/Windows 11) or run:

  Restart-Service -Name 'MSSQLSERVER' -Force

• Re-enable Query Store capture if it was temporarily disabled during troubleshooting.

Step 4: Rebuild Statistics and Recompile

• Update statistics on the affected tables to ensure the optimizer has current data:

  EXEC sp_updatestats;

• Force a recompilation of the stored procedure or query plan:

  EXEC sp_recompile 'YourStoredProcedureName';

• Allow SQL Server to generate a new plan naturally before re-forcing it through Query Store if desired.

Step 5: Validate Permissions on Query Store Objects

• Ensure the service account running SQL Server has db_owner or appropriate VIEW SERVER STATE permissions.
• Check permissions using:

  SELECT * FROM fn_my_permissions(NULL, 'DATABASE');

• Grant missing permissions via SSMS under Database Properties > Permissions if required.

Alternative or Verification Steps

• Run SELECT * FROM sys.query_store_query_text WHERE query_text_id = <id>; to confirm the query text aligns correctly with the plan and that no orphaned references remain.
• If the error persists, disable and re-enable Query Store entirely (ALTER DATABASE [YourDB] SET QUERY_STORE = OFF; then ON) to force a full metadata rebuild, then re-establish plan forcing manually.

Troubleshooting Guide: SQL Server Error 12408 – Query Store Read/Write Failure

Why This Error Happens

Error 12408 typically occurs when SQL Server 2025 cannot read from or write to the Query Store internal tables due to database-level corruption, insufficient disk space on the volume hosting the data file, or restrictive file-system/database permissions. This can also surface if the Query Store catalog metadata has become inconsistent after an abrupt shutdown, forced failover, or an interrupted upgrade process. In some cases, the underlying NTFS volume on Windows Server 2025 or Windows 11 has hit an I/O timeout or encountered disk-level errors that prevent the storage engine from committing Query Store writes.

Step-by-Step Resolution Guide

Step 1: Verify Database and Disk Health

• Run DBCC CHECKDB (YourDatabaseName) WITH NO_INFOMSGS, ALL_ERRORMSGS; to rule out corruption.
• Check Windows Event Viewer under Windows Logs > System for Disk or NTFS warning entries (Event ID 7 or 153).
• Confirm free space on the drive hosting your .mdf/.ldf files using File Explorer or Get-PSDrive in PowerShell.

Step 2: Review SQL Server Error Logs for Root Cause

• Open SQL Server Management Studio (SSMS) and navigate to Management > SQL Server Logs.
• Alternatively, run EXEC sp_readerrorlog 0, 1, 'Query Store'; to filter for related entries.
• Look for accompanying errors like 605, 823, or 824, which indicate deeper storage-level corruption tied to the 12408 event.

Step 3: Reset or Repair the Query Store

• If corruption is isolated to Query Store internal tables, disable and clear it using:

  ALTER DATABASE YourDatabaseName SET QUERY_STORE = OFF;

• Clear any residual data with:

  ALTER DATABASE YourDatabaseName SET QUERY_STORE CLEAR;

• Re-enable Query Store with a clean state:

  ALTER DATABASE YourDatabaseName SET QUERY_STORE = ON (OPERATION_MODE = READ_WRITE);

Step 4: Validate File and Folder Permissions

• Confirm the SQL Server service account has full Read/Write/Modify permissions on the data directory (typically C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\DATA).
• Use icacls "C:\Path\To\DataFolder" /grant "NT SERVICE\MSSQLSERVER":(OI)(CI)F in an elevated PowerShell session on Windows Server 2025 to reset permissions if needed.
• Restart the SQL Server service via services.msc after applying permission changes.

Step 5: Restore from Backup if Corruption Persists

• If DBCC CHECKDB reports unrepairable corruption, restore the affected database from the last known good backup.
• Ensure Query Store settings are reconfigured post-restore, as they do not always persist across restore operations.

Alternative or Verification Steps

• After applying fixes, run a test workload and query sys.database_query_store_options to confirm actual_state_desc shows READ_WRITE with no pending errors.
• If the issue persists after a Query Store reset, consider moving the database to a new volume with verified disk integrity, as recurring 12408 errors often point to failing underlying storage hardware.

Troubleshooting Guide: SQL Server Error 12409 — Query Store Cannot Create System Task

Why This Error Happens

Error 12409 typically occurs when SQL Server’s internal background task scheduler is unable to spawn the Query Store collection task due to insufficient worker threads, memory pressure, or a restrictive MAX_WORKER_THREADS configuration. This can also happen if the internal resource governor pool assigned to system tasks is exhausted or if the database is in a transitional state (such as during recovery, restore, or when marked read-only/suspect) that blocks background task creation. In some Windows Server 2025 / Windows 11 environments, corrupted system catalog metadata for Query Store (sys.database_query_store_options) can also prevent the task from initializing properly.

Step-by-Step Resolution Guide

Step 1: Verify SQL Server Worker Thread and Memory Availability

• Open SQL Server Management Studio (SSMS) and run: SELECT scheduler_id, active_workers_count, work_queue_count FROM sys.dm_os_schedulers;
• Check if active_workers_count is near the configured max worker threads limit (Server Properties > Processors tab).
• If memory pressure is suspected, run: SELECT * FROM sys.dm_os_memory_clerks WHERE type = 'MEMORYCLERK_SQLQERESERVATIONS';
• Increase max worker threads via: EXEC sp_configure 'max worker threads', 0; RECONFIGURE; (0 lets SQL Server auto-manage).

Step 2: Check Database State and Query Store Status

• Run SELECT name, state_desc, is_read_only FROM sys.databases WHERE name = 'YourDatabaseName'; to confirm the database is ONLINE and not read-only.
• Verify Query Store status with: SELECT actual_state_desc, readonly_reason FROM sys.database_query_store_options;
• If readonly_reason is non-zero, resolve the underlying cause (e.g., disk space, database in single-user mode) before retrying.
• Restart Query Store manually: ALTER DATABASE [YourDatabaseName] SET QUERY_STORE = OFF; ALTER DATABASE [YourDatabaseName] SET QUERY_STORE = ON;

Step 3: Rebuild Query Store Metadata

• If the catalog is suspected to be corrupted, clear Query Store data using: ALTER DATABASE [YourDatabaseName] SET QUERY_STORE CLEAR;
• Run DBCC CHECKDB('YourDatabaseName') WITH NO_INFOMSGS, ALL_ERRORMSGS; to rule out underlying corruption.
• Navigate to C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ on Windows Server 2025 and review the ERRORLOG file for related stack traces at the time of failure.

Step 4: Restart the SQL Server Service and Validate System Resources

• On Windows Server 2025 / Windows 11, open Services.msc, locate SQL Server (MSSQLSERVER), and restart the service.
• Confirm there is no CPU/Memory throttling applied via Resource Governor: SELECT * FROM sys.resource_governor_resource_pools;
• Ensure Windows Server has sufficient free memory and the SQL Server instance isn’t hitting max server memory limits configured in sp_configure.

Alternative or Verification Steps

• To verify the fix, re-run a few test queries against the affected database and check SELECT * FROM sys.query_store_runtime_stats; to confirm new entries are being logged without error.
• If Error 12409 persists, as a fallback, disable and re-enable Query Store at the database level, or apply the latest Cumulative Update (CU) for SQL Server 2025, as this error is sometimes tied to a scheduler bug fixed in post-RTM patches.

Troubleshooting Guide: SQL Server Error 12406 — Query Plan Not Found in Query Store

Why This Error Happens

This error occurs when the plan_id supplied to a Query Store procedure (such as sp_query_store_force_plan or sp_query_store_unforce_plan) does not correspond to an existing, active plan for the specified query_id in the current database’s Query Store. This typically happens because the plan was aged out or evicted due to Query Store cleanup policies, the query_id/plan_id pair is stale (captured before a database restore, failover, or Query Store clear operation), or the value was manually mistyped. In Windows Server 2025 / Windows 11 environments running SQL Server 2025, this can also surface after automated maintenance jobs (such as sp_purge_query_store_statistics or scheduled cleanup via SQL Agent) silently remove old plan data before an application or script attempts to reference it.

Step-by-Step Resolution Guide

Step 1: Verify the Plan ID Still Exists

• Run the following query against the target database to confirm the plan is still present:

  SELECT plan_id, query_id, last_execution_time 
  FROM sys.query_store_plan 
  WHERE query_id = <your_query_id>;

• If no rows are returned, the plan has already been purged or never existed under that ID.
• Cross-check the query_id value itself against sys.query_store_query to rule out a typo.

Step 2: Check Query Store Retention and Cleanup Settings

• Open SQL Server Management Studio (SSMS) and navigate to Database Properties > Query Store.
• Review the Size Based Cleanup Mode and Stale Query Threshold (Days) settings — aggressive cleanup policies can purge plans faster than expected.
• Adjust retention via T-SQL if needed:

  ALTER DATABASE [YourDB] SET QUERY_STORE (CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 30));

• Confirm no manual purge command (EXEC sp_query_store_flush_db) was recently executed by an admin or scheduled job.

Step 3: Re-fetch a Valid Plan ID Before Re-running the Command

• Instead of hardcoding an old plan_id, dynamically pull the current valid plan using:

  SELECT TOP 1 plan_id 
  FROM sys.query_store_plan 
  WHERE query_id = <your_query_id> 
  ORDER BY last_execution_time DESC;

• Use this freshly retrieved plan_id in your sp_query_store_force_plan or sp_query_store_unforce_plan call.
• Update any PowerShell scripts, SQL Agent jobs, or application code on the Windows Server 2025 host that cache plan_id values to re-query it dynamically rather than storing it statically.

Step 4: Rebuild or Reset Query Store if Corruption Is Suspected

• If plans are disappearing unexpectedly or the Query Store data looks inconsistent, check Query Store health:

  SELECT actual_state_desc, readonly_reason 
  FROM sys.database_query_store_options;

• If readonly_reason indicates corruption or an error state, clear and rebuild Query Store:

  ALTER DATABASE [YourDB] SET QUERY_STORE CLEAR;

• Allow Query Store to recapture plans over normal workload execution before re-attempting forced plan operations.

Alternative or Verification Steps

• After applying the fix, re-run your original command (e.g., sp_query_store_force_plan @query_id, @plan_id) using the freshly validated plan_id and confirm it completes without Error 12406.
• As an alternative, query sys.query_store_plan joined with sys.query_store_query right before every automated plan-forcing operation to guarantee you’re always referencing a currently valid, non-evicted plan.

Troubleshooting Guide: Error 12407 – Query Store Manager Global Instance Not Available

Why This Error Happens

Error 12407 occurs when the SQL Server Query Store subsystem fails to initialize its global manager instance during database startup or recovery, typically because the internal metadata catalog views (sys.query_store_*) have become corrupted or are out of sync with the database’s internal state. This can also happen if the database was upgraded from an older compatibility level and the Query Store internal storage schema wasn’t fully migrated, or if the MDF/LDF files experienced an unexpected shutdown while Query Store was actively flushing data to disk. In some cases, insufficient memory allocation within the internal SQL Server resource pool prevents the Query Store manager from being instantiated for that database context.

Step-by-Step Resolution Guide

Step 1: Verify Query Store Status and Database Health

• Open SQL Server Management Studio (SSMS) and connect to the instance reporting the error.
• Run SELECT name, is_query_store_on FROM sys.databases WHERE name = 'YourDatabaseName'; to confirm current state.
• Execute DBCC CHECKDB ('YourDatabaseName') WITH NO_INFOMSGS, ALL_ERRORMSGS; to rule out underlying corruption in the data files.

Step 2: Disable and Re-enable Query Store

• Run ALTER DATABASE YourDatabaseName SET QUERY_STORE = OFF; to force the manager to release its current (broken) state.
• Wait approximately 30 seconds for the internal cleanup thread to clear cached metadata.
• Re-enable it with ALTER DATABASE YourDatabaseName SET QUERY_STORE = ON (OPERATION_MODE = READ_WRITE);

Step 3: Clear Query Store Internal Storage

• If Step 2 fails, run ALTER DATABASE YourDatabaseName SET QUERY_STORE CLEAR; to purge potentially corrupted internal tables.
• Note: this action is destructive to historical query performance data, so export any critical reports first via sys.query_store_runtime_stats if accessible.
• Restart the SQL Server service via Services.msc or net stop MSSQLSERVER && net start MSSQLSERVER on Windows Server 2025.

Step 4: Check Resource Governor and Memory Allocation

• Navigate to Server Properties > Memory in SSMS and confirm “Minimum server memory” is not set higher than available physical RAM.
• Run sp_configure 'show advanced options', 1; RECONFIGURE; sp_configure 'min server memory'; to validate configuration values.
• Adjust values if the instance is memory-starved, as Query Store requires a dedicated memory grant to initialize its manager object.

Step 5: Apply Latest Cumulative Update

• Check your current build with SELECT @@VERSION;.
• Download and install the latest Cumulative Update (CU) for SQL Server 2025 from the Microsoft Update Catalog, as this error has known fixes in early post-RTM patches.
• Reboot the Windows Server 2025 host after patching to ensure all binaries are correctly swapped.

Alternative or Verification Steps

• To verify the fix, run SELECT * FROM sys.database_query_store_options; — a successful query returning configuration rows (rather than an error) confirms the manager is now active.
• If the issue persists after all steps, consider detaching and re-attaching the database (sp_detach_db / sp_attach_db) to force a full re-initialization of all internal subsystems, including Query Store.

Troubleshooting Guide: Error 12405 – Query Store Not Enabled

Why This Error Happens

This error occurs when a command or procedure attempts to query, modify, or retrieve Query Store data from a database where the Query Store feature has not been activated. SQL Server 2025 requires Query Store to be explicitly enabled per-database since it is disabled by default on newly created or migrated databases. Additionally, this can occur if Query Store was previously enabled but was automatically disabled due to storage size limits being reached or an internal error causing it to revert to an “OFF” state.

Step-by-Step Resolution Guide

Step 1: Verify the Current Query Store Status

• Open SQL Server Management Studio (SSMS) 21 or Azure Data Studio connected to your Windows Server 2025 instance.
• Run the following T-SQL query to check the current state:

SELECT actual_state, actual_state_desc, readonly_reason 
FROM sys.database_query_store_options;

• If actual_state_desc returns OFF, proceed to the next step to enable it.

Step 2: Enable Query Store via T-SQL

• Execute the following command against the target database, replacing [YourDatabase] with the actual database name:

ALTER DATABASE [YourDatabase] 
SET QUERY_STORE = ON 
(OPERATION_MODE = READ_WRITE);

• Ensure the SQL Server service account has write permissions to the database’s primary file group, as Query Store requires disk space within the data file to log execution statistics.

Step 3: Check for Read-Only Mode Due to Storage Limits

• If the Query Store was previously enabled but reverted to “Read-Only,” the MAX_STORAGE_SIZE_MB limit was likely hit.
• Increase the storage allocation and set the cleanup policy using this command:

ALTER DATABASE [YourDatabase] 
SET QUERY_STORE (MAX_STORAGE_SIZE_MB = 2000, 
SIZE_BASED_CLEANUP_MODE = AUTO);

Step 4: Enable via SSMS GUI (Alternative Method)

• Right-click the specific database in Object Explorer and select Properties.
• Navigate to the Query Store tab on the left-hand menu.
• Change the Operation Mode (Requested) dropdown from Off to Read Write, then click OK to apply the configuration.

Alternative or Verification Steps

• Verify the Fix: Re-run the original command that triggered Error 12405. If successful, query sys.query_store_query to confirm that execution data is now being captured.
• Alternative Solution: If the ALTER DATABASE command times out or hangs, restart the SQL Server (MSSQLSERVER) service via the Windows Services console (services.msc) to clear any locked schema resources before retrying the enable command.

Troubleshooting Guide: SQL Server Error 12403 – Query Plan Not Found in Query Store

Why This Error Happens

Error 12403 occurs when a stored procedure such as sp_query_store_force_plan or sp_query_store_unforce_plan is executed with a plan_id that does not exist in the Query Store for the specified database. This typically happens because the plan was already evicted from Query Store due to cleanup policies (size-based or time-based retention), the database was altered or restored causing Query Store data to reset, or the plan_id was copied from a different database context than the one currently targeted. It can also occur if Query Store was recently cleared manually using ALTER DATABASE ... SET QUERY_STORE CLEAR or if the capture mode excluded the plan before it was persisted.

Step-by-Step Resolution Guide

Step 1: Verify the Correct Database Context

• Confirm you are connected to the intended database, not master or a different one, since plan_id values are database-specific.
• Run SELECT DB_NAME(); in SSMS to validate the current session database.
• Re-run your query after executing USE [YourDatabaseName]; if the context was incorrect.

Step 2: Query the Query Store Catalog Views to Validate plan_id

• Execute the following to check if the plan_id currently exists:
• SELECT plan_id, query_id, last_compile_batch_sql_handle FROM sys.query_store_plan WHERE plan_id = <your_plan_id>;
• If no rows return, the plan has been purged or never existed in this database.
• Cross-check against sys.query_store_query and sys.query_store_runtime_stats to find a currently valid planid for the same queryid.

Step 3: Check Query Store Retention and Cleanup Settings

• Navigate to SSMS > Database Properties > Query Store page, or run:
• SELECT actual_state_desc, size_based_cleanup_mode_desc, stale_query_threshold_days FROM sys.database_query_store_options;
• If size_based_cleanup_mode_desc is set to AUTO, old plans may be evicted once the store nears its max size (max_storage_size_mb).
• Increase max_storage_size_mb via ALTER DATABASE [YourDB] SET QUERY_STORE (MAX_STORAGE_SIZE_MB = 2048); if plans are being purged too aggressively.

Step 4: Re-Identify the Target Plan and Reissue the Command

• Use sys.query_store_plan joined with sys.query_store_query_text to find the current valid plan_id associated with the problematic query hash or SQL text.
• Update your forcing/unforcing script with the corrected plan_id value.
• Re-run the stored procedure, e.g., EXEC sp_query_store_force_plan @query_id = <id>, @plan_id = <new_valid_id>;

Step 5: Rebuild Query Store Data if Corruption Is Suspected

• If catalog views return inconsistent or unexpected results even after validation, check SQL Server error logs for Query Store corruption messages (Event ID 12408 or similar).
• Run ALTER DATABASE [YourDB] SET QUERY_STORE CLEAR; to reset Query Store contents (note: this is destructive to historical plan data).
• Allow new query executions to repopulate the Query Store before attempting to force or query specific plans again.

Alternative or Verification Steps

• Verify the fix by re-running SELECT * FROM sys.query_store_plan WHERE plan_id = <plan_id>; and confirming a row is returned before retrying the forcing command.
• If the issue persists across multiple planids, use SSMS’s built-in “Query Store” reports (Top Resource Consuming Queries) to visually select and force plans instead of manually specifying planid values via T-SQL.

Troubleshooting Guide: SQL Server Error 12404 — Query Store Not in Read-Write Mode

Why This Error Happens

This error occurs when the Query Store for a specific database has transitioned out of read-write mode, typically because it was switched to read-only mode automatically after hitting its configured maximum size limit, or because an administrator manually disabled or changed its operation mode. It can also happen if the database is in a restricted state (such as read-only, single-user, or emergency mode) that prevents Query Store from accepting write operations. Additionally, corruption in the internal Query Store catalog views or insufficient disk space on the volume hosting the database files can trigger this same read-only fallback behavior.

Step-by-Step Resolution Guide

Step 1: Check the Current Query Store State and Size

• Run the following query to inspect the current operational state:
• SELECT actual_state_desc, readonly_reason, current_storage_size_mb, max_storage_size_mb FROM sys.database_query_store_options;
• If actual_state_desc returns READ_ONLY, note the value of readonly_reason to confirm whether it is due to size cap, disk space, or another internal issue.
• Confirm the database itself is online and writable using SELECT state_desc FROM sys.databases WHERE name = 'YourDatabaseName';

Step 2: Increase Max Storage Size or Clear Query Store Data

• If the current_storage_size_mb is at or near max_storage_size_mb, increase the cap using:
• ALTER DATABASE [YourDatabaseName] SET QUERY_STORE (MAX_STORAGE_SIZE_MB = 2000);
• Alternatively, purge accumulated data to free space with:
• ALTER DATABASE [YourDatabaseName] SET QUERY_STORE CLEAR;
• Ensure the underlying drive hosting the .mdf/.ndf files on Windows Server 2025 has sufficient free space (check via File Explorer or Get-PSDrive in PowerShell).

Step 3: Manually Force Query Store Back to Read-Write Mode

• Execute the following T-SQL command to explicitly set the operation mode:
• ALTER DATABASE [YourDatabaseName] SET QUERY_STORE = ON (OPERATION_MODE = READ_WRITE);
• If the command fails silently, restart the SQL Server service via services.msc or Restart-Service MSSQLSERVER in an elevated PowerShell session on Windows Server 2025.
• Re-check the database is not stuck in SINGLE_USER or EMERGENCY mode, which can block configuration changes.

Step 4: Rebuild Query Store if Corruption Is Suspected

• If toggling the mode does not resolve the issue, disable and re-enable Query Store entirely:
• ALTER DATABASE [YourDatabaseName] SET QUERY_STORE = OFF;
• ALTER DATABASE [YourDatabaseName] SET QUERY_STORE = ON;
• Run DBCC CHECKDB('YourDatabaseName') WITH NO_INFOMSGS, ALL_ERRORMSGS; to rule out underlying database corruption as the root cause.

Alternative or Verification Steps

• Verify the fix by re-running SELECT actual_state_desc FROM sys.database_query_store_options; and confirming it now reports READ_WRITE.
• If the problem persists after a service restart, check the SQL Server Error Log (via SSMS or %ProgramFiles%\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\) for related I/O or storage warnings that may indicate a deeper disk-level issue.

Troubleshooting Guide: SQL Server Error 12402 – Query ID Not Found in Query Store

Why This Error Happens

This error occurs when the query_id supplied to a Query Store stored procedure (such as sp_query_store_force_plan, sp_query_store_unforce_plan, or sp_query_store_remove_query) does not exist in the current Query Store data for the target database. This typically happens because the Query Store has been cleared or has rotated out older queries due to size limits or cleanup policies, because the query was captured in a different database than the one currently in context, or because the query_id was copied from a stale report, cached dashboard, or a different server environment entirely. It can also occur if Query Store was recently disabled and re-enabled, which resets internal ID tracking.

Step-by-Step Resolution Guide

Step 1: Verify the Correct Database Context

• Confirm you are connected to the correct database where the query_id was originally captured, since query_id values are not unique across databases.
• Run SELECT DB_NAME(); in SQL Server Management Studio (SSMS) to confirm the active database context.
• If using a multi-database environment on Windows Server 2025, double-check the connection string or USE [DatabaseName]; statement at the top of your script.

Step 2: Re-Query the Query Store for a Valid Query ID

• Run the following query to check if the query_id currently exists:

SELECT query_id, object_id, query_text_id 
FROM sys.query_store_query 
WHERE query_id = <your_query_id>;

• If no rows are returned, the query has likely aged out of the Query Store or was never captured.
• Retrieve a fresh, valid query_id by joining sys.query_store_query with sys.query_store_query_text to match the actual query text you intend to target.

Step 3: Check Query Store Retention and Cleanup Settings

• Navigate to SSMS → Database Properties → Query Store page to review the “Size Based Cleanup Mode” and “Stale Query Threshold (Days)” settings.
• If retention is set too aggressively (e.g., 1–3 days), increase the “Stale Query Threshold” value to prevent queries from being purged before you can act on them.
• Alternatively, run this T-SQL command to adjust retention programmatically:

ALTER DATABASE [YourDatabaseName] 
SET QUERY_STORE (CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 30));

Step 4: Confirm Query Store Is Enabled and Not Recently Reset

• Verify Query Store is active by running:

SELECT actual_state_desc FROM sys.database_query_store_options;

• If the state shows OFF or was recently toggled from OFF to READ_WRITE, all previous query_id values are invalidated and a new capture cycle has started.
• Allow sufficient workload execution time for Query Store to recapture the query and generate a new, valid query_id.

Alternative or Verification Steps

• To verify the fix, rerun your original stored procedure (e.g., sp_query_store_force_plan) using the newly retrieved query_id from Step 2 and confirm it executes without raising Error 12402.
• As an alternative, bypass manual ID lookup entirely by using the Query Store GUI in SSMS (Query Store → Top Resource Consuming Queries) to visually select and force/unforce a plan, which automatically resolves the correct query_id for you.

Troubleshooting Guide: SQL Server Error 12359 — “Ledger Tables Are Not Supported With %S_MSG”

Why This Error Happens

This error occurs when an operation is attempted against a ledger table that conflicts with a feature or configuration not yet compatible with SQL Server’s Ledger functionality, such as certain types of replication, Change Data Capture (CDC), bulk import operations, or specific index types. The SQL Server Database Engine explicitly blocks these operations to preserve the cryptographic chain of custody and tamper-evidence guarantees that ledger tables are designed to provide. Essentially, the engine is refusing the action because allowing it would silently break the integrity verification chain of the ledger history table.

Step-by-Step Resolution Guide

Step 1: Identify the Exact Conflicting Feature

• Check the full error message text where %S_MSG is substituted — this placeholder reveals the specific blocking cause (e.g., “with Change Data Capture,” “with the specified index type,” or “with sparse columns”).
• Run the following query to inspect ledger-related metadata on the affected table:

SELECT name, ledger_type, ledger_type_desc 
FROM sys.tables 
WHERE object_id = OBJECT_ID('YourTableName');

• Cross-reference the result with Microsoft’s documented list of unsupported features for ledger tables (e.g., FILESTREAM, columnstore indexes on updatable ledger tables, or in-place CDC).

Step 2: Remove or Reconfigure the Conflicting Dependency

• If the issue stems from CDC, disable it on the table before converting to ledger format:

EXEC sys.sp_cdc_disable_table 
    @source_schema = N'dbo', 
    @source_name = N'YourTableName', 
    @capture_instance = N'all';

• If the issue involves an unsupported index (e.g., a disallowed columnstore or XML index), drop the index using:

DROP INDEX IX_UnsupportedIndex ON dbo.YourTableName;

• If bulk insert or BCP is triggering the error, switch to standard batched INSERT statements, as some bulk-load code paths bypass ledger validation checks.

Step 3: Recreate the Table as a Proper Ledger Table

• Instead of altering an existing non-ledger table, create a new table explicitly with ledger syntax to avoid hidden metadata conflicts:

CREATE TABLE dbo.YourTableName
(
    ID INT PRIMARY KEY,
    Data NVARCHAR(100)
)
WITH (SYSTEM_VERSIONING = ON, LEDGER = ON);

• Migrate data from the old table into this new ledger-enabled table using a standard INSERT INTO ... SELECT statement.

Step 4: Validate SQL Server Build and Patch Level

• Confirm you are running a build of SQL Server 2025 that has the latest Cumulative Update (CU) applied, as early builds have had restrictive ledger compatibility matrices.
• Check version via:

SELECT @@VERSION;

• Update via Windows Server 2025 / Windows 11 using SQL Server Setup or the Microsoft Update Catalog if the build number is outdated.

Alternative or Verification Steps

• After applying the fix, attempt the original operation again (e.g., re-run the CDC setup, bulk insert, or index creation) and confirm Error 12359 no longer appears in SQL Server Management Studio (SSMS) or the SQL Server Error Log.
• As an alternative, use sys.fn_hadr_is_ledger_table_enabled() or query sys.database_ledger_transactions to confirm the ledger chain remains unbroken and that no verification errors surface when running sp_verify_database_ledger.

Troubleshooting Guide: SQL Server Error 12401

Why This Error Happens

Error 12401 occurs when a T-SQL statement, stored procedure call, or command-line utility (such as sqlcmd or bcp) includes the same option or parameter flag listed twice within a single command. SQL Server’s parser evaluates each option against an internal list and throws this error as soon as it detects a duplicate, since it cannot determine which instance of the value should take precedence. This is typically caused by manual scripting errors, copy-paste mistakes in automation scripts, or parameter concatenation logic in application code that unintentionally appends the same flag multiple times.

Step-by-Step Resolution Guide

Step 1: Identify the Exact Command or Statement Triggering the Error

• Check the SQL Server Error Log via sqlcmd -S localhost -Q "EXEC sp_readerrorlog" or SQL Server Management Studio (SSMS) to capture the full error message, including the duplicated option name.
• Review the Windows Event Viewer under Applications and Services Logs > MSSQLSERVER for correlated timestamps.
• If the error originates from a scheduled job, open SQL Server Agent > Jobs and inspect the specific job step history.

Step 2: Audit the Script or Command Syntax for Duplicate Parameters

• Open the offending script, stored procedure, or batch file in a text editor.
• Look specifically for repeated flags such as WITH RECOMPILE, ENCRYPTION, CHECK_POLICY, or command-line switches like -U, -P, -S in sqlcmd/bcp calls.
• Example of a faulty statement:

  CREATE LOGIN TestUser WITH PASSWORD = 'Pass123', CHECK_POLICY = ON, CHECK_POLICY = OFF;

• Remove the duplicate instance, keeping only the intended value.

Step 3: Check Dynamic SQL and Application-Generated Queries

• If the query is built dynamically (e.g., via C#, PowerShell, or an ORM like Entity Framework), inspect the string concatenation logic in the source code.
• Search for loops or conditional blocks that may append the same option twice under certain application states.
• In PowerShell scripts invoking Invoke-Sqlcmd, verify that parameters aren’t being passed both explicitly and via a splatted hashtable simultaneously.

Step 4: Validate Configuration Files and Automation Templates

• If using SQL Server Agent jobs, SSIS packages, or Azure DevOps/CI pipeline YAML files to generate T-SQL, check the template files located in paths such as C:\Program Files\Microsoft SQL Server\160\DTS\Packages\ for hardcoded duplicate parameters.
• Correct the template and redeploy the job or package.

Step 5: Test the Corrected Script in Isolation

• Run the corrected statement directly in SSMS or via sqlcmd against a non-production instance first.
• Confirm no syntax errors are returned before redeploying to production environments on Windows Server 2025.

Alternative or Verification Steps

• Re-run the exact command or job step that previously failed and confirm Error 12401 no longer appears in the SQL Server Error Log (EXEC sp_readerrorlog 0, 1, 'Error: 12401').
• If the error persists despite manual review, enable SQL Server Profiler or Extended Events to trace the exact final string sent to the engine, as the duplication may be happening at a middleware or connection-pooling layer rather than in the raw script itself.

Troubleshooting Guide: Error 12357 – UTF8 Collation Indexes Not Supported

Why This Error Happens

This error occurs because SQL Server 2025 restricts certain index types and features from being created directly on character columns (CHAR/VARCHAR) that use a UTF8-enabled collation. Specific features—such as certain full-text indexes, XML indexes, or spatial indexes—have not extended compatibility to the UTF8 encoding model and rely on the underlying byte-ordering assumptions of legacy collations. This typically surfaces during a schema migration or when applying a UTF8 collation retroactively to a table that already has specialized indexes defined on it.

Step-by-Step Resolution Guide

Step 1: Identify the Conflicting Column and Index

• Run sp_help 'TableName' or query sys.columns joined with sys.indexes to locate which specific column is using a UTF8 collation (e.g., Latin1_General_100_CI_AS_SC_UTF8).
• Check the specific index type involved (Full-Text, Spatial, or XML) as these are the primary triggers for Error 12357.
• Use the following query to audit collations across the database:

  SELECT name, collation_name FROM sys.columns WHERE collation_name LIKE '%UTF8%';

Step 2: Modify the Column Collation (Primary Fix)

• Since the unsupported index type is required, you generally need to revert the specific column to a standard non-UTF8 collation (e.g., Latin1_General_100_CI_AS).
• Execute an ALTER TABLE command to change the column definition:

  ALTER TABLE dbo.YourTable 
  ALTER COLUMN YourColumn VARCHAR(255) 
  COLLATE Latin1_General_100_CI_AS NOT NULL;

• Ensure no active transactions are locking the table before running this schema change in Windows Server 2025 environments.

Step 3: Rebuild the Index

• Once the collation is reverted on the specific column, attempt to create the index again using SQL Server Management Studio (SSMS) or T-SQL.
• Drop any “ghost” index metadata that may have remained from the failed creation attempt using DROP INDEX IF EXISTS.
• Re-run the creation script:

  CREATE FULLTEXT INDEX ON dbo.YourTable(YourColumn) KEY INDEX PK_YourTable;

Step 4: Isolate UTF8 Usage to Non-Indexed Columns

• If UTF8 is a business requirement for storage efficiency, restructure the schema so that UTF8 collations are only applied to columns that do not require Full-Text, Spatial, or XML indexing.
• Keep “identifier” or “key” columns requiring advanced indexing in standard double-byte or Latin collations, while using UTF8 strictly for large text storage fields.

Alternative or Verification Steps

• Verification: After altering the collation, run DBCC CHECKDB on the specific table and attempt the index build again; if the build completes without Error 12357, the collation conflict is resolved.
• Alternative: If UTF8 is strictly required for compliance/storage reasons, consider using a computed column with a standard collation that shadows the UTF8 column specifically for indexing purposes.

Troubleshooting Guide: Error 12358 — CDC Trigger Conflict on ALTER/DROP TABLE

Why This Error Happens

Error 12358 occurs when Change Data Capture (CDC) is enabled on a database and its internal DDL triggers conflict with existing or user-defined triggers on ALTER TABLE and DROP TABLE operations. This typically happens because SQL Server 2025 registers hidden system triggers (MSchange_capture_ddl) under the database scope, and if another trigger already occupies that scope or the CDC metadata tables (cdc.change_tables, cdc.ddl_history) are out of sync, the engine blocks the schema change to prevent corruption of capture instances. Missing db_owner permissions or an interrupted CDC cleanup job can also leave orphaned trigger definitions that trigger this error.

Step-by-Step Resolution Guide

Step 1: Verify CDC and Trigger Status

• Open SQL Server Management Studio (SSMS) and connect to the affected instance.
• Run SELECT * FROM sys.triggers WHERE parent_class = 0; to list all database-level triggers.
• Check specifically for MSchange_capture_ddl and any custom triggers bound to ALTER_TABLE or DROP_TABLE events.

Step 2: Disable and Clean Up Conflicting CDC Instances

• Run EXEC sys.sp_cdc_disable_table @source_schema = 'dbo', @source_name = 'YourTable', @capture_instance = 'all'; to remove capture instances tied to the table causing the conflict.
• Follow with EXEC sys.sp_cdc_disable_db; if the issue persists at the database level.
• Manually drop the orphaned trigger using DROP TRIGGER MSchange_capture_ddl ON DATABASE; if it still exists after disabling CDC.

Step 3: Re-enable CDC with Correct Permissions

• Ensure the executing login has db_owner role membership: ALTER ROLE db_owner ADD MEMBER [YourLogin];
• Re-enable CDC cleanly: EXEC sys.sp_cdc_enable_db;
• Re-enable table-level capture: EXEC sys.sp_cdc_enable_table @source_schema = 'dbo', @source_name = 'YourTable', @role_name = NULL;

Step 4: Restart SQL Server Agent and Validate Jobs

• Open Services.msc on Windows Server 2025 / Windows 11 and restart SQL Server Agent (MSSQLSERVER).
• Confirm the CDC capture and cleanup jobs are running under SQL Server Agent > Jobs in SSMS.
• Check job history for any failed executions related to cdc.*_capture or cdc.*_cleanup.

Alternative or Verification Steps

• Run EXEC sys.sp_cdc_help_change_data_capture; to confirm the capture instance is active and no duplicate triggers remain.
• If the error recurs, as a workaround, perform schema changes (ALTER/DROP TABLE) only after temporarily disabling CDC with sp_cdc_disable_table, then re-enable it post-change.

Troubleshooting Guide: SQL Server Error 12356 — “Comparison” Mismatch Failure

Why This Error Happens

Error 12356 typically occurs when SQL Server 2025 encounters a collation or data type mismatch during a comparison operation, often triggered by corrupted query plan cache entries or inconsistent collation settings between a database and its tempdb instance. This can also result from a failed in-place upgrade on Windows Server 2025 that left residual registry keys pointing to deprecated comparison algorithms. In some cases, missing permissions on the MSSQLSERVER service account prevent SQL Server from accessing comparison metadata stored in system tables.

Step-by-Step Resolution Guide

Step 1: Verify Collation Consistency

• Open SQL Server Management Studio (SSMS) and run SELECT DATABASEPROPERTYEX('YourDBName', 'Collation'); to check the database-level collation.
• Compare this output against the server-level collation using SELECT SERVERPROPERTY('Collation');.
• If mismatched, document both values before proceeding to the next step — do not attempt to change collation live on a production database.

Step 2: Clear and Rebuild the Query Plan Cache

• Open a new query window in SSMS and execute DBCC FREEPROCCACHE; to clear corrupted cached execution plans.
• Restart the SQL Server service via PowerShell using Restart-Service -Name 'MSSQLSERVER' -Force.
• Confirm the service restarted cleanly by checking Event Viewer under Applications and Services Logs > MSSQL Server for Event ID 17147.

Step 3: Repair Registry and Service Account Permissions

• Navigate to HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQLServer in Registry Editor (regedit.exe).
• Verify the DefaultCollation key matches the expected server collation value from Step 1.
• Grant the SQL Server service account (e.g., NT Service\MSSQLSERVER) Full Control on C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\Data via Windows Security properties.

Step 4: Run Database Consistency Check

• Execute DBCC CHECKDB('YourDBName') WITH NO_INFOMSGS, ALL_ERRORMSGS; to scan for underlying corruption tied to the comparison error.
• Review output for any Msg 12356 references tied to specific table indexes or constraints.

Alternative or Verification Steps

• Re-run the original query or stored procedure that triggered Error 12356 and confirm it executes without exceptions in SSMS.
• If the error persists, restore the affected database from the most recent verified backup using RESTORE DATABASE YourDBName FROM DISK = 'BackupPath' WITH REPLACE; and re-test the comparison operation.

Troubleshooting Guide: Error 12355 – Cannot Alter Persistence of a Computed Column in a Memory-Optimized Table

Why This Error Happens

Error 12355 occurs because SQL Server’s memory-optimized engine does not support in-place schema modifications for computed column persistence (PERSISTED/NOT PERSISTED) due to the way memory-optimized tables compile their schema into native DLLs. Unlike disk-based tables, memory-optimized tables require the entire row format and checksum structure to remain static after creation, so ALTER TABLE ALTER COLUMN cannot modify this attribute. This is a hard architectural limitation in SQL Server 2025, not a configuration error, bug, or permissions issue.

Step-by-Step Resolution Guide

Step 1: Confirm the Table Type and Column Definition

• Run sp_help 'YourTableName' or query sys.tables to confirm is_memory_optimized = 1.
• Check sys.computed_columns to verify the current is_persisted value for the affected column.
• Note the exact computed column formula (e.g., AS (ColumnA + ColumnB)), as you will need to re-declare it.

Step 2: Script Out Dependent Objects

• Identify any indexes, constraints, or natively compiled stored procedures referencing the computed column.
• Use sp_helptext or SSMS “Generate Scripts” to capture definitions of dependent native modules.
• Drop dependent natively compiled procedures/indexes temporarily, since they will fail to compile once the column is dropped.

Step 3: Drop and Re-Create the Column with Desired Persistence

• Run the following pattern:

ALTER TABLE dbo.YourTable
DROP COLUMN YourComputedColumn;
GO

ALTER TABLE dbo.YourTable
ADD YourComputedColumn AS (YourExpression) PERSISTED;
GO

• Replace PERSISTED with no persistence flag if you want a non-persisted column instead.
• If the table has a memory-optimized filegroup checkpoint pending, run CHECKPOINT before altering the schema to avoid blocking.

Step 4: Recreate Dependent Indexes and Natively Compiled Objects

• Re-create any hash or nonclustered indexes that referenced the dropped column.
• Re-deploy natively compiled stored procedures using CREATE PROCEDURE ... WITH NATIVE_COMPILATION.
• Recompile any dependent views or in-memory OLTP cross-container queries.

Step 5: Validate Memory-Optimized Table Recompilation

• Restart the SQL Server service or run ALTER TABLE ... REBUILD equivalent operation if the DLL does not reflect the schema change.
• Confirm the new schema is compiled by checking sys.dm_os_loaded_modules for the updated memory-optimized table DLL path (typically under C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\DATA\xtp\).

Alternative or Verification Steps

• Verify the fix by querying sys.computed_columns again to confirm is_persisted matches your intended value, then run a test SELECT to ensure the computed values calculate correctly.
• If dropping the column is not feasible due to heavy dependencies, consider creating a new computed column with the desired persistence alongside the old one, migrate application references, then drop the legacy column during a maintenance window.

Troubleshooting Guide: Error 12354 in Microsoft SQL Server 2025

Why This Error Happens

Error 12354 occurs when a SQL Server function—such as a string manipulation, full-text search, or comparison function—is executed against a column or variable using the Japanese_140 collation, but the specific build or edition of SQL Server 2025 does not have the underlying linguistic component (NLS module) compiled to support that function. This typically happens because the Japanese_140 collation relies on newer Unicode-based sorting rules that certain legacy or specific T-SQL functions (like older LIKE pattern matches or specific STRING_SPLIT configurations) cannot parse. Additionally, this can be triggered by a mismatch between the OS-level language pack installed on Windows Server 2025/Windows 11 and the SQL Server instance’s collation settings.

Step-by-Step Resolution Guide

Step 1: Identify the Conflicting Collation and Function

• Open SQL Server Management Studio (SSMS) and run the query that triggered the error to identify the specific function (%ls) mentioned in the error log.
• Check the column collation using the command: SELECT name, collation_name FROM sys.columns WHERE object_id = OBJECT_ID('YourTableName');
• Verify if the collation is strictly Japanese_140_SC_UTF8 or a variant, as UTF-8 variants often cause this specific conflict with legacy functions.

Step 2: Update Windows Language and Regional Support

• Navigate to Settings > Time & Language > Language & Region on your Windows Server 2025 or Windows 11 machine.
• Ensure the Japanese language pack is fully installed, not just added as a preference, as missing base characters can cause SQL Server to throw this support error.
• Restart the “SQL Server (MSSQLSERVER)” service via services.msc after the language pack installation completes to refresh the OS-level locale cache.

Step 3: Modify the Query to Use a COLLATE Override

• Rewrite the specific query causing the error to force a compatible collation for that single operation rather than changing the whole database.
• Use the following syntax structure in your T-SQL script:
• SELECT YourColumn COLLATE Japanese_XJIS_140_CI_AS FROM YourTable WHERE Condition;
• This bypasses the UTF-8 specific limitation by reverting to the double-byte character set (DBCS) version of the Japanese collation for that specific transaction.

Step 4: Apply the Latest Cumulative Update (CU)

• Check the current build number by running: SELECT @@VERSION;
• Visit the Microsoft documentation to download the latest Cumulative Update for SQL Server 2025, as this specific function/collation conflict is often patched in service updates.
• Install the update and perform a full restart of the server to ensure the native client libraries (SQLNCLI) are updated alongside the engine.

Alternative or Verification Steps

• Verification: Re-run the original failing query in SSMS. If successful, check the ERRORLOG file located in C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ to confirm no new instances of Error 12354 have been logged.
• Alternative: If the error persists, rebuild the specific database using a Latin1_General_100_CI_AS collation for the master database while keeping Japanese_140 only at the column level for the specific Japanese text fields, isolating the conflict from system functions.

Why This Error Happens

Error 12352 occurs when a script or application attempts to execute a system stored procedure that modifies schema metadata (such as sp_cdc_enable_db, sp_fulltext_database, or similar catalog-altering procedures) while an explicit user transaction (BEGIN TRAN) is still open. SQL Server 2025 enforces stricter transactional isolation on schema-altering system procedures to prevent metadata corruption and replication/CDC inconsistencies. Because these procedures internally manage their own transaction scope, wrapping them in an external BEGIN TRANSACTION...COMMIT block causes a conflict that the engine rejects outright.

Step-by-Step Resolution Guide

Step 1: Identify the Offending Script or Procedure Call

• Review the T-SQL batch or stored procedure that triggered the error in SQL Server Management Studio (SSMS) or the application’s execution log.
• Look specifically for calls to system procedures like sp_cdc_enable_table, sp_fulltext_database, sp_cdc_enable_db, or similar schema-modifying procs.
• Check if these calls are nested inside a BEGIN TRAN ... COMMIT TRAN block, including implicit transactions set via SET IMPLICIT_TRANSACTIONS ON.

Step 2: Remove the System Procedure Call from the User Transaction

• Edit the script to move the system stored procedure call outside of any BEGIN TRANSACTION / COMMIT TRANSACTION wrapper.
• Execute the schema-altering procedure as a standalone, autocommitted statement before or after the user transaction logic.
• Example fix:

  EXEC sys.sp_cdc_enable_db;
  GO
  BEGIN TRAN
      -- your normal DML operations here
  COMMIT TRAN

Step 3: Check and Disable Implicit Transaction Settings

• Open SSMS and navigate to Tools > Options > Query Execution > SQL Server > Advanced.
• Confirm that SET IMPLICIT_TRANSACTIONS is set to False for the connection or session running the script.
• For application-level connections (ODBC, ADO.NET, JDBC), verify the connection string or driver configuration does not force implicit transaction mode.

Step 4: Validate SQL Server Compatibility Level and Service Pack

• Run SELECT compatibility_level FROM sys.databases WHERE name = 'YourDatabaseName'; to confirm the database is running under SQL Server 2025’s expected compatibility level (160 or higher).
• Apply the latest Cumulative Update (CU) for SQL Server 2025 via SQL Server Setup or Microsoft Update Catalog, as early builds had stricter enforcement bugs around this error that were patched.
• Restart the SQL Server service via services.msc or Restart-Service MSSQLSERVER in PowerShell after patching.

Alternative or Verification Steps

• Re-run the script with the system procedure isolated outside the transaction block and confirm Error 12352 no longer appears in the SSMS Messages tab or the SQL Server Error Log (EXEC sp_readerrorlog).
• If the error persists, use SELECT @@TRANCOUNT; before executing the system procedure to confirm no transaction is active (value should return 0).

Troubleshooting Guide: Error 12353 – Computed Columns in Memory-Optimized Index Must Be PERSISTED

Why This Error Happens

This error occurs because memory-optimized tables in SQL Server store computed column values differently than disk-based tables, requiring the computed value to be physically materialized rather than calculated on the fly. When you attempt to create an index on a computed column within a memory-optimized table, SQL Server’s engine rejects the operation unless the PERSISTED keyword is explicitly declared, as the indexing mechanism for memory-optimized OLTP tables cannot reference non-materialized (virtual) values. Essentially, the query optimizer needs a static, stored value to build the hash or range index against, which a standard (non-persisted) computed column does not provide.

Step-by-Step Resolution Guide

Step 1: Identify the Offending Table and Column

• Locate the specific memory-optimized table causing the error by checking the SQL Server Management Studio (SSMS) error log or the specific CREATE TABLE/ALTER TABLE script that triggered Error 12353.
• Run the following query to find computed columns and check their persistence status: SELECT name, is_persisted FROM sys.computed_columns WHERE object_id = OBJECT_ID('YourSchema.YourTableName');
• Identify which specific computed column(s) have is_persisted set to 0.

Step 2: Modify the Table Schema to Include PERSISTED

• Since altering computed column definitions on existing memory-optimized tables is restrictive, you will likely need to drop and recreate the table or column definition.
• Update your T-SQL script to include the PERSISTED keyword immediately after the computed column formula.
• Example syntax correction: ALTER TABLE dbo.YourTable ADD TotalPrice AS (Quantity * UnitPrice) PERSISTED;
• If the table already contains data, back up the data to a temporary disk-based staging table before dropping and recreating the memory-optimized table with the corrected schema.

Step 3: Recreate the Index

• Once the computed column is successfully altered to PERSISTED, attempt to recreate the index that initially failed.
• Use the specific memory-optimized index syntax (Hash or Nonclustered) relevant to your workload: CREATE INDEX IX_YourIndex ON dbo.YourTable (TotalPrice);
• Monitor the “Messages” tab in SSMS to confirm the index creation completes without triggering Error 12353 again.

Step 4: Restart the Database Engine Service (If Schema Locks Persist)

• If you encounter schema lock errors while trying to modify the memory-optimized table (common with Hekaton engine locks), restart the SQL Server service.
• On Windows Server 2025/Windows 11, open Services.msc, locate SQL Server (MSSQLSERVER) or your specific instance name, and select Restart.
• Alternatively, use PowerShell: Restart-Service -Name 'MSSQLSERVER' -Force

Alternative or Verification Steps

• Verification: Re-run the sys.computed_columns query from Step 1 to confirm the is_persisted column now returns a value of 1 for the modified column, and check sys.indexes to ensure the new index status is is_disabled = 0.
• Alternative: If you cannot modify the column to be PERSISTED due to non-deterministic functions (e.g., using GETDATE()), remove the computed column from the index definition entirely and handle the calculation logic at the application layer or via a natively compiled stored procedure instead.

Troubleshooting Guide: Error 12350 — DML Operations Not Supported on Table-Valued Functions

Why This Error Happens

This error occurs because SQL Server 2025 does not permit direct INSERT, UPDATE, or DELETE statements to be executed against a table-valued function (TVF), as TVFs are designed to be read-only result sets rather than updatable objects. The root cause is typically an application or stored procedure attempting to treat an inline or multi-statement TVF as if it were a base table or an updatable view. This often surfaces after migrating legacy T-SQL code to SQL Server 2025, where stricter query validation now catches DML patterns that may have previously failed silently or were caught by a different error path.

Step-by-Step Resolution Guide

Step 1: Identify the Offending Query

• Capture the full error text, including the %S_MSG placeholder, which will specify the exact function name being targeted.
• Use SQL Server Profiler or Extended Events (sqlserver.error_reported filtered on Error = 12350) to trace the exact T-SQL batch triggering the issue.
• Check the application connection string or ORM-generated SQL (e.g., Entity Framework, Dapper) for auto-generated DML statements referencing a function.

Step 2: Redirect DML to the Underlying Base Table

• Open the definition of the table-valued function using sp_helptext 'YourFunctionName' in SQL Server Management Studio (SSMS).
• Identify the base table(s) referenced inside the function’s SELECT statement.
• Rewrite the application logic or stored procedure to perform the INSERT/UPDATE/DELETE directly against the underlying base table instead of the function wrapper.
• Example: Replace UPDATE dbo.fn_GetActiveUsers SET Status = 1 with UPDATE dbo.Users SET Status = 1 WHERE UserID IN (SELECT UserID FROM dbo.fn_GetActiveUsers()).

Step 3: Implement an Updatable View or Stored Procedure (If Abstraction Is Required)

• If the application requires an abstraction layer, create a standard SQL View instead of a TVF, ensuring it meets the criteria for an updatable view (single base table, no aggregates, no DISTINCT).
• Alternatively, encapsulate the DML logic inside a dedicated stored procedure (e.g., usp_UpdateActiveUsers) that performs the write operation on the base table safely.
• Update the application code to call the stored procedure rather than querying the function with a DML verb.

Step 4: Validate Compatibility Level and Code Migration

• Run SELECT compatibility_level FROM sys.databases WHERE name = 'YourDatabaseName'; to confirm the database compatibility level matches SQL Server 2025 standards.
• Review any legacy code migrated from older SQL Server versions (2016/2019) that might have relied on undocumented behavior now strictly enforced in 2025.

Alternative or Verification Steps

• Verify the Fix: Re-run the original transaction against the base table or new stored procedure and confirm the operation commits without throwing Error 12350; check the SQL Server Error Log to ensure no further occurrences are logged.
• Alternative Workaround: If refactoring the application is not immediately feasible, create an INSTEAD OF trigger on a standard view that mimics the TVF logic, allowing the trigger to handle the underlying INSERT/UPDATE/DELETE logic transparently.

Troubleshooting Guide: Error 12351 in SQL Server 2025

Why This Error Happens

This error occurs because natively compiled stored procedures and functions operate in a restricted execution environment that does not support interop with standard (interpreted) T-SQL modules. SQL Server’s In-Memory OLTP engine enforces this limitation because natively compiled modules are compiled directly to machine code at creation time, and the compiled execution plan cannot dynamically resolve calls to non-natively-compiled objects. This typically happens when a developer migrates or writes a natively compiled function that attempts to call a regular stored procedure, a CLR function, or a standard T-SQL function using the EXECUTE statement.

Step-by-Step Resolution Guide

Step 1: Identify the Offending EXECUTE Call

• Open the natively compiled function or procedure definition using sp_helptext '<SchemaName.FunctionName>' in SQL Server Management Studio (SSMS).
• Locate the specific EXECUTE or EXEC statement that references a non-native object.
• Check whether the target of the EXECUTE call was created WITH NATIVE_COMPILATION by running sp_helptext against the target object as well.

Step 2: Convert the Target Object to Natively Compiled (If Possible)

• Rewrite the target procedure/function using the CREATE PROCEDURE ... WITH NATIVE_COMPILATION, SCHEMABINDING AS BEGIN ATOMIC ... syntax.
• Ensure the rewritten object only uses T-SQL constructs supported by the In-Memory OLTP engine (no CURSOR, TRY/CATCH with certain error types, or dynamic SQL).
• Validate the memory-optimized tables referenced are accessible to both objects.

Step 3: Refactor the Calling Function to Remove Interop Dependency

• If the target object cannot be natively compiled (e.g., it uses CLR, linked servers, or dynamic SQL), move the logic that requires the EXECUTE call outside the natively compiled function.
• Call the natively compiled function first from a standard interpreted stored procedure, then handle the non-native EXECUTE call separately in that outer interpreted procedure.
• Update the application or calling layer (e.g., C# Data Access Layer, Entity Framework interceptor) to orchestrate the two calls sequentially instead of nesting them.

Step 4: Recompile and Deploy the Corrected Module

• Drop and recreate the natively compiled function using DROP FUNCTION followed by CREATE FUNCTION ... WITH NATIVE_COMPILATION.
• Confirm compilation succeeds without errors in SSMS’s “Messages” tab.
• Redeploy the updated object to any synchronized environments (Dev, QA, Production) using your standard deployment pipeline (e.g., Azure DevOps, SSDT DACPAC).

Alternative or Verification Steps

• Run the natively compiled function manually via EXEC in SSMS against a sample dataset to confirm Error 12351 no longer appears in the Messages pane.
• If native compilation of the dependent object is not feasible, use sys.sql_modules to query the uses_native_compilation column and confirm your execution chain now only mixes native-to-native or interpreted-to-interpreted calls, never crossing the boundary from inside a natively compiled module.

Troubleshooting Guide: Error 12349 — Operation Not Supported for Memory-Optimized Tables with Columnstore Index

Why This Error Happens

This error occurs because the operation you are attempting (such as an online index rebuild, certain DML batch operation, or a schema change) is not supported on memory-optimized tables that have a columnstore index applied to them in SQL Server 2025. This typically stems from a limitation in the Hekaton engine’s interaction with columnstore metadata, where certain T-SQL commands or maintenance tasks bypass the required in-memory OLTP compatibility checks. It can also be triggered by outdated database compatibility levels or attempting operations reserved for disk-based tables on memory-optimized structures.

Step-by-Step Resolution Guide

Step 1: Verify Database Compatibility Level

• Open SQL Server Management Studio (SSMS) and connect to your SQL Server 2025 instance.
• Run the following query to check the current compatibility level:

SELECT name, compatibility_level FROM sys.databases WHERE name = 'YourDatabaseName';

• Ensure the compatibility level is set to 170 (SQL Server 2025) using:

ALTER DATABASE YourDatabaseName SET COMPATIBILITY_LEVEL = 170;

Step 2: Identify Unsupported Operations on the Table

• Query the table definition to confirm it is memory-optimized with a columnstore index:

SELECT t.name AS TableName, i.type_desc AS IndexType
FROM sys.tables t
JOIN sys.indexes i ON t.object_id = i.object_id
WHERE t.is_memory_optimized = 1 AND i.type_desc LIKE '%COLUMNSTORE%';

• Avoid running unsupported operations such as ALTER INDEX REBUILD directly on the columnstore index; instead, use supported maintenance paths like dropping and recreating the index.

Step 3: Drop and Recreate the Columnstore Index Correctly

• Use the supported syntax to remove the index before attempting schema or maintenance changes:

DROP INDEX IF EXISTS YourColumnstoreIndexName ON dbo.YourTableName;

• Recreate the index using the correct memory-optimized syntax:

CREATE COLUMNSTORE INDEX YourColumnstoreIndexName 
ON dbo.YourTableName;

• Ensure no other sessions or applications are locking the table during this operation (check via sys.dm_tran_locks).

Step 4: Update SQL Server 2025 to the Latest Cumulative Update

• Navigate to Control Panel > Programs > Programs and Features on Windows Server 2025 / Windows 11 to check the currently installed SQL Server build.
• Download and install the latest Cumulative Update (CU) from the Microsoft Update Catalog, as early 2025 builds had known restrictions with columnstore indexes on memory-optimized tables that were patched in later releases.
• Restart the SQL Server service via SQL Server Configuration Manager after the update completes.

Step 5: Review Application Code for Legacy Syntax

• Audit stored procedures and ETL jobs for legacy T-SQL commands (e.g., TRUNCATE TABLE or MERGE statements) that may not be certified for use on memory-optimized columnstore tables.
• Replace unsupported commands with natively compiled stored procedure logic where possible.

Alternative or Verification Steps

• To verify the fix, re-run the original query or application transaction that triggered Error 12349; if successful, check the SQL Server Error Log (Management > SQL Server Logs in SSMS) to confirm no new 12349 entries appear.
• If the error persists after patching, consider migrating the columnstore index to a standard disk-based table as a temporary workaround while Microsoft Support investigates further compatibility fixes for the specific build.

Troubleshooting Guide: Error 12347 in Microsoft SQL Server 2025

Why This Error Happens

Error 12347 occurs when a user-defined function (UDF) is declared with a parameter using a MAX length specifier (such as VARCHAR(MAX), NVARCHAR(MAX), or VARBINARY(MAX)) in a context where the SQL Server engine does not support max-length parameters for that specific function type. This is typically a schema definition issue caused by legacy T-SQL scripts, migrated code, or third-party deployment tools that generate function signatures incompatible with SQL Server 2025’s parameter validation rules. It can also surface during database upgrades from older compatibility levels where max-length parameters were previously tolerated in a different execution context.

Step-by-Step Resolution Guide

Step 1: Identify the Offending Function Definition

• Open SQL Server Management Studio (SSMS) and connect to the affected instance.
• Run the following query to locate recently modified or created UDFs:

SELECT name, object_id, modify_date 
FROM sys.objects 
WHERE type IN ('FN', 'TF', 'IF') 
ORDER BY modify_date DESC;

• Cross-reference the SQL Server Error Log (C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG) for the exact object name tied to Error 12347.

Step 2: Review and Correct the Parameter Declaration

• Script out the function definition using SSMS (Right-click Function > Script Function as > CREATE To).
• Locate the parameter using MAX (e.g., @InputData VARCHAR(MAX)).
• Replace the MAX specifier with a fixed length if full max-length support isn’t required for that function type, for example changing VARCHAR(MAX) to VARCHAR(8000).
• If MAX is required, confirm the function type supports it (Scalar UDFs have different limitations than Table-Valued Functions in strict policy mode).

Step 3: Adjust Database Compatibility Level

• Check the current compatibility level, as SQL Server 2025 defaults to a stricter parser:

SELECT name, compatibility_level 
FROM sys.databases 
WHERE name = 'YourDatabaseName';

• If the database is running on an older compatibility level (e.g., 130 or 140) migrated into SQL Server 2025, update it to align with the new engine’s expectations:

ALTER DATABASE YourDatabaseName 
SET COMPATIBILITY_LEVEL = 170;

Step 4: Redeploy the Corrected Function

• Drop the broken function object:

DROP FUNCTION IF EXISTS dbo.YourFunctionName;

• Re-create the function using the corrected syntax from Step 2.
• Restart the SQL Server (MSSQLSERVER) service via services.msc on Windows Server 2025 to clear any cached execution plans referencing the old object.

Alternative or Verification Steps

• Verify the fix by executing the function directly with a sample payload (SELECT dbo.YourFunctionName('TestData');) to confirm no 12347 error is thrown in the Messages pane.
• If the error persists after redeployment, use SQL Server Configuration Manager to check for duplicate instances or stale metadata caches, and run DBCC FREEPROCCACHE to purge any lingering invalid execution plans.

Troubleshooting Guide: SQL Server Error 12348 — “Max Length Literals Not Supported”

Why This Error Happens

This error occurs when a Transact-SQL statement uses the MAX keyword (e.g., VARCHAR(MAX), NVARCHAR(MAX), VARBINARY(MAX)) in a context where SQL Server 2025 expects a fixed numeric length literal, such as in certain system function calls, computed column definitions, or legacy compatibility-level operations. It typically stems from code written against an older database compatibility level being executed under stricter parsing rules, or from a third-party tool generating dynamic SQL that improperly references MAX in an unsupported clause. Occasionally, this is also triggered by corrupted or outdated query plan cache entries that reference deprecated syntax structures no longer valid in SQL Server 2025.

Step-by-Step Resolution Guide

Step 1: Identify the Offending Query or Object

• Open SQL Server Management Studio (SSMS) and check the SQL Server Error Log (Management > SQL Server Logs) for the exact statement or object name tied to Error 12348.
• Run the following query to capture recent failing statements from the plan cache:

  SELECT text, usecounts, cacheobjtype 
  FROM sys.dm_exec_cached_plans 
  CROSS APPLY sys.dm_exec_sql_text(plan_handle)
  WHERE text LIKE '%MAX%';

• Note any stored procedures, views, or computed columns using (MAX) outside of standard column definitions.

Step 2: Correct the Syntax in the Affected Code

• Replace invalid uses of MAX with an explicit numeric length where required (e.g., change CONVERT(VARCHAR(MAX), expr, MAX) to CONVERT(VARCHAR(MAX), expr, 0)).
• If the issue originates in a computed column or CHECK constraint, redefine it using a supported fixed-length literal:

  ALTER TABLE dbo.YourTable 
  ALTER COLUMN YourColumn VARCHAR(8000);

• Save and redeploy the corrected script to the SQL Server 2025 instance.

Step 3: Update Database Compatibility Level

• Navigate to Databases > [Your Database] > Properties > Options in SSMS.
• Set the Compatibility Level to 160 (SQL Server 2025) to ensure the parser enforces current syntax rules consistently.
• Alternatively, run:

  ALTER DATABASE YourDatabase SET COMPATIBILITY_LEVEL = 160;

Step 4: Clear the Procedure and Plan Cache

• Flush any stale cached plans that may still reference the old invalid syntax:

  DBCC FREEPROCCACHE;

• Restart the SQL Server (MSSQLSERVER) service via Services.msc on Windows Server 2025 to ensure a clean parsing state.

Alternative or Verification Steps

• Re-run the original query or application process that triggered Error 12348 and confirm it executes without exception; check the Windows Event Viewer > Applications and Services Logs > MSSQLSERVER for recurrence.
• If the error persists, use SQL Server Profiler or Extended Events to trace the exact statement at runtime and confirm no third-party ORM or ETL tool (e.g., SSIS package) is dynamically injecting unsupported MAX literals into generated SQL.

Troubleshooting Guide: Error 12345 – Max Length Data Types Not Supported as Return Type in Natively Compiled UDF

Why This Error Happens

This error occurs because SQL Server’s natively compiled user-defined functions (UDFs) do not support variable-length “max” data types—such as VARCHAR(MAX), NVARCHAR(MAX), or VARBINARY(MAX)—as a return type. Natively compiled modules are compiled to native machine code ahead of execution, and the underlying memory allocation model requires fixed, predictable sizes for return values, which MAX types cannot guarantee. This limitation is enforced by the In-Memory OLTP engine architecture in SQL Server 2025 and has not changed from earlier versions.

Step-by-Step Resolution Guide

Step 1: Identify the Offending Function Definition

• Open SQL Server Management Studio (SSMS) and locate the natively compiled function throwing Error 12345.
• Review the RETURNS clause in the CREATE FUNCTION statement.
• Look specifically for declarations like RETURNS VARCHAR(MAX), RETURNS NVARCHAR(MAX), or RETURNS VARBINARY(MAX).

Step 2: Modify the Return Type to a Fixed-Length Equivalent

• Change the return type to a bounded length, for example:
• VARCHAR(MAX) → VARCHAR(8000)
• NVARCHAR(MAX) → NVARCHAR(4000)
• VARBINARY(MAX) → VARBINARY(8000)
• Update the CREATE OR ALTER FUNCTION script accordingly:

CREATE OR ALTER FUNCTION dbo.MyNativeFunc (@input NVARCHAR(4000))
RETURNS NVARCHAR(4000)
WITH NATIVE_COMPILATION, SCHEMABINDING
AS
BEGIN ATOMIC WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'us_english')
    RETURN @input;
END

• Deploy the updated function using SSMS or sqlcmd -S <ServerName> -i UpdateFunction.sql.

Step 3: Recompile and Validate the Module

• Drop and recreate the function to force native recompilation:

DROP FUNCTION IF EXISTS dbo.MyNativeFunc;

• Re-run the corrected CREATE FUNCTION script.
• Check the SQL Server Error Log (Management > SQL Server Logs in SSMS, or C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG on Windows Server 2025) to confirm Error 12345 no longer appears.

Step 4: Refactor Calling Code if Necessary

• If client applications or stored procedures expect MAX-length results, add explicit casting at the call site:

SELECT CAST(dbo.MyNativeFunc(@input) AS NVARCHAR(MAX));

• This allows the natively compiled function to return a fixed-length value while downstream consumers still work with larger strings as needed.

Alternative or Verification Steps

• Verify the fix by executing the function directly in SSMS (SELECT dbo.MyNativeFunc('test')) and confirming it returns results without throwing Error 12345.
• If MAX data types are a hard business requirement, convert the function to a standard (interpreted) T-SQL function instead of a natively compiled one, since interpreted functions fully support MAX return types.

Troubleshooting Guide: SQL Server Error 12346 — “Max Length Defaults Are Not Supported with %S_MSG”

Why This Error Happens

This error occurs when a column or parameter definition attempts to use the MAX length specifier (e.g., VARCHAR(MAX), NVARCHAR(MAX), VARBINARY(MAX)) in a context where SQL Server 2025 does not permit default length inheritance, typically within computed columns, CLR type definitions, or specific system catalog operations. The root cause is usually a schema migration script or ORM-generated DDL statement that was written for an older SQL Server version and does not explicitly define a length, relying instead on a default that the Database Engine now rejects. This can also surface after an in-place upgrade to SQL Server 2025 when legacy compatibility levels force a stricter parsing rule against ambiguous MAX declarations.

Step-by-Step Resolution Guide

Step 1: Identify the Offending Object or Script

• Open SQL Server Management Studio (SSMS) and check the Activity Monitor or SQL Server Error Log (C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG) to find the exact timestamp of the error.
• Cross-reference the timestamp with your deployment script or application query to locate the specific CREATE TABLE or ALTER TABLE statement causing the fault.
• Look specifically for column definitions missing explicit parentheses, such as VARCHAR instead of VARCHAR(50) or VARCHAR(MAX).

Step 2: Correct the Schema Definition Syntax

• Edit the DDL script to explicitly define length parameters rather than relying on system defaults.
• Replace ambiguous definitions: Change DECLARE @Variable VARCHAR to DECLARE @Variable VARCHAR(8000) or VARCHAR(MAX) depending on the data requirement.
• If using computed columns, ensure the underlying function explicitly casts the output length, e.g., CAST(ColumnA AS NVARCHAR(4000)).

Step 3: Verify Database Compatibility Level

• Open a New Query window in SSMS and check the current compatibility level using:
• SELECT name, compatibility_level FROM sys.databases WHERE name = 'YourDatabaseName';
• If the level is outdated (e.g., 130 or lower) but you require 2025 features, update it using:
• ALTER DATABASE YourDatabaseName SET COMPATIBILITY_LEVEL = 170;
• Re-run the failed script to see if the stricter modern parser resolves the ambiguity automatically.

Step 4: Clear Plan Cache and Retry Execution

• Sometimes a cached execution plan from a previous version retains the invalid “default max” logic.
• Clear the specific plan cache for the database to force a fresh compile:
• DBCC FREEPROCCACHE;
• Restart the SQL Server (MSSQLSERVER) service via the Windows Services console (services.msc) if the issue persists after clearing the cache.

Alternative or Verification Steps

• Verification: Re-run the original CREATE or ALTER statement in a test environment; the query should execute successfully without throwing Error 12346, and the resulting column metadata should show a specific max_length value (not -1 unless explicitly intended for MAX types) when checked via sp_help 'TableName'.
• Alternative: If modifying the source script is not immediately possible, create a view that explicitly casts the problematic column to a fixed length to bypass the strict validation check during data retrieval.

Troubleshooting Guide: Error 12344 — “Only Natively Compiled Modules Can Be Used with %S_MSG”

Why This Error Happens

This error occurs when a Transact-SQL statement or module attempts to reference a feature, object, or syntax element that is only supported within natively compiled modules (such as those used with memory-optimized tables in In-Memory OLTP). SQL Server 2025 enforces strict compilation boundaries between interpreted T-SQL and natively compiled modules, so mixing unsupported constructs—like certain system functions, cursors, or dynamic SQL—inside a natively compiled stored procedure triggers this validation failure. This typically stems from a schema migration, an upgraded compatibility level, or a module that was not properly rewritten to comply with native compilation restrictions.

Step-by-Step Resolution Guide

Step 1: Identify the Offending Module

• Open SQL Server Management Studio (SSMS) and connect to the affected instance.
• Run the following query to locate natively compiled objects and check their definitions:

  SELECT name, object_id, type_desc 
  FROM sys.sql_modules 
  WHERE uses_native_compilation = 1;

• Review the module definition for unsupported elements (e.g., OPENROWSET, cursors, or non-deterministic functions).

Step 2: Review and Correct the Module Code

• Check the procedure or function for syntax not permitted in natively compiled modules, such as:
• Multi-statement table-valued functions
• Nested stored procedure calls outside native scope
• String or XML functions unsupported in native compilation
• Rewrite the logic using only supported T-SQL surface area for native modules, referencing the Native Compilation Compatibility List in SQL Server 2025 documentation (C:\Program Files\Microsoft SQL Server\160\Tools\Binn\ for local doc cache if installed offline).

Step 3: Recompile the Module

• Drop and recreate the natively compiled module using the corrected syntax:

  DROP PROCEDURE IF EXISTS dbo.YourNativeProc;
  GO
  CREATE PROCEDURE dbo.YourNativeProc
  WITH NATIVE_COMPILATION, SCHEMABINDING
  AS BEGIN ATOMIC WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'us_english')
      -- corrected logic here
  END;
  GO

• Confirm compilation succeeds without triggering Error 12344.

Step 4: Validate Database Compatibility Level

• Ensure the database compatibility level supports native compilation features expected in SQL Server 2025:

  SELECT compatibility_level FROM sys.databases WHERE name = 'YourDatabaseName';

• If outdated, update it:

  ALTER DATABASE YourDatabaseName SET COMPATIBILITY_LEVEL = 170;

Alternative or Verification Steps

• After recompilation, execute the procedure manually (EXEC dbo.YourNativeProc;) and confirm no Error 12344 appears in SQL Server Logs or Event Viewer under Applications and Services Logs > Microsoft > SQL Server.
• If the issue persists, consider converting the problematic logic into a standard (interpreted) stored procedure instead of a natively compiled one, especially if the required functionality is not supported natively in SQL Server 2025.

Troubleshooting Guide: Error 12342 – EXECUTE Statement Compatibility Issue

Why This Error Happens

This error occurs when a T-SQL EXECUTE statement attempts to call a stored procedure or module from within a natively compiled stored procedure (used for In-Memory OLTP) that is not itself natively compiled. SQL Server 2025’s native compilation engine restricts execution context strictly to natively compiled modules calling other natively compiled modules, disallowing interpreted T-SQL calls in that scope. This typically surfaces after a migration, an upgrade from an older SQL Server version, or when a developer mixes interpreted and natively compiled procedures without proper isolation.

Step-by-Step Resolution Guide

Step 1: Identify the Offending Module

• Open SQL Server Management Studio (SSMS) and locate the exact stored procedure referenced in the error message (%S_MSG placeholder).
• Run the following query to check compilation type:

  SELECT name, uses_native_compilation 
  FROM sys.sql_modules m
  JOIN sys.objects o ON m.object_id = o.object_id
  WHERE o.name = 'YourProcedureName';

• Confirm whether the calling procedure is natively compiled (uses_native_compilation = 1) and whether the called module is not.

Step 2: Refactor the Execution Logic

• If the called module is a standard (interpreted) procedure, move the logic outside the natively compiled module, or rewrite the target procedure to also be natively compiled using WITH NATIVE_COMPILATION, SCHEMABINDING.
• Alternatively, restructure the calling procedure to be a standard interpreted procedure if native compilation isn’t strictly required for performance.
• Example fix syntax:

  CREATE OR ALTER PROCEDURE dbo.YourProc
  WITH NATIVE_COMPILATION, SCHEMABINDING
  AS
  BEGIN ATOMIC WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'us_english')
      -- native-compatible T-SQL only
  END;

Step 3: Validate Native Compilation Compatibility

• Review the native compilation feature list for SQL Server 2025 to ensure all referenced objects (tables, functions, procedures) within the module are supported (memory-optimized tables, limited data types, no cross-database calls).
• Remove unsupported constructs such as MERGE, dynamic SQL, or CURSOR statements that are invalid inside natively compiled modules.

Step 4: Recompile and Deploy

• Drop and recreate the affected stored procedures using sqlcmd or SSMS to force a clean recompilation:

  sqlcmd -S localhost -d YourDatabase -i "C:\Scripts\RecreateProc.sql"

• Restart the SQL Server service via Windows Services (services.msc) or PowerShell:

  Restart-Service -Name 'MSSQLSERVER'

Alternative or Verification Steps

• Execute the original failing call manually in SSMS and confirm Error 12342 no longer appears in the Messages pane or Windows Event Viewer (Application log).
• If the error persists, consider isolating in-memory OLTP logic entirely into its own natively compiled module chain, avoiding any mixed calls to interpreted T-SQL procedures.

Troubleshooting Guide: Error 12343 — TRIGGER_NESTLEVEL Argument Error

Why This Error Happens

This error occurs when a Transact-SQL statement calls the TRIGGER_NESTLEVEL function with more than one argument, which exceeds the function’s supported parameter limit. The function is designed to accept either zero arguments (returning the overall trigger nesting level) or exactly one argument (an object ID to check nesting level for a specific trigger). This typically results from a coding error in custom trigger logic, a faulty migration script, or legacy code that was written against an incompatible or deprecated syntax assumption.

Step-by-Step Resolution Guide

Step 1: Identify the Offending Trigger or Script

• Open SQL Server Management Studio (SSMS) and check the SQL Server error log via Management > SQL Server Logs for the exact statement or trigger name tied to Error 12343.
• Run the following query to search system triggers for usage of the function:

  SELECT OBJECT_NAME(object_id), definition
  FROM sys.sql_modules
  WHERE definition LIKE '%TRIGGER_NESTLEVEL%';

• Note the file path if the trigger was deployed via a .sql deployment script (commonly found in C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Scripts\).

Step 2: Correct the Function Call Syntax

• Edit the trigger definition using ALTER TRIGGER in SSMS.
• Ensure the function call uses zero or one argument only. Valid examples:
• SELECT TRIGGER_NESTLEVEL();
• SELECT TRIGGER_NESTLEVEL(OBJECT_ID('dbo.YourTriggerName'));
• Remove any additional arguments (such as a DML type flag like 'INSERT') that may have been mistakenly included from outdated documentation or copied code.

Step 3: Redeploy and Recompile the Trigger

• Save the corrected trigger script and execute it against the target database.
• Force a recompilation to clear any cached invalid execution plans:

  EXEC sp_recompile 'dbo.YourTriggerName';

• Restart the SQL Server service via services.msc on Windows Server 2025/Windows 11 if the error persists in cached query plans.

Step 4: Audit Related Stored Procedures

• Check linked stored procedures or ORM-generated code (e.g., Entity Framework migrations) that might dynamically construct the TRIGGER_NESTLEVEL call with extra parameters.
• Update any third-party extensions or middleware configuration files referencing this function to match the SQL Server 2025 documented syntax.

Alternative or Verification Steps

• Verify the fix by executing the trigger manually in a test transaction and confirming no errors appear in SELECT * FROM sys.messages WHERE message_id = 12343; logs.
• If the error persists after code correction, use DBCC FREEPROCCACHE to clear the entire plan cache (use cautiously in production) to rule out stale execution plan interference.

Troubleshooting Guide: Error 12340 – EXECUTE Statement Object Name Requirement

Why This Error Happens

This error occurs when the EXECUTE (or EXEC) statement is used with a variable, string literal, or quoted identifier instead of a direct, unqualified or fully-qualified object name. SQL Server 2025 enforces strict parsing rules for direct execution calls, meaning constructs like EXEC @ProcName or EXEC 'MyProc' are rejected because the engine expects a literal object reference to validate permissions and execution plans at parse time. This typically happens when developers attempt to dynamically execute a stored procedure name without using the proper dynamic SQL syntax.

Step-by-Step Resolution Guide

Step 1: Identify the Offending Query

• Open SQL Server Management Studio (SSMS) and locate the exact line number referenced in the error output (visible in the Messages tab).
• Look specifically for syntax patterns such as EXEC @variable or EXECUTE 'string_name'.
• Check Application Event Viewer logs on Windows Server 2025 (Event Viewer > Applications and Services Logs > MSSQLSERVER) to cross-reference the timestamp of the failure.

Step 2: Convert to Dynamic SQL using sp_executesql

• Instead of directly executing a variable, wrap the variable in the system stored procedure sp_executesql.
• Rewrite the code from:
  EXEC @ProcName
• To the correct syntax:
  EXEC sp_executesql @ProcName
• If parameters are involved, ensure they are defined using the N'@param1 int, @param2 varchar(50)' format directly within the sp_executesql call to maintain type safety.

Step 3: Validate Object Name Formatting

• If the goal was to execute a static procedure (not dynamic), remove the quotes or variable brackets entirely.
• Ensure the syntax follows the standard format: EXEC dbo.YourStoredProcedureName;
• Verify there are no hidden Unicode or quoted identifiers (QUOTED_IDENTIFIER setting) interfering with the object name by running SELECT SESSIONPROPERTY('QUOTED_IDENTIFIER'); to check the current session settings.

Step 4: Restart the SQL Service and Clear Cache

• Navigate to the Windows Server 2025 Services manager (services.msc).
• Restart the SQL Server (MSSQLSERVER) service to clear any corrupted execution plans cached in memory.
• Alternatively, run DBCC FREEPROCCACHE; in a new query window to clear the plan cache without a full service restart.

Alternative or Verification Steps

• Verification: Execute the corrected script in a new query window; if the “Command(s) completed successfully” message appears without Error 12340, the syntax fix was successful.
• Alternative: If the error persists despite correct syntax, check if the stored procedure exists in a different schema ownership (e.g., dbo vs. a custom schema) and explicitly define the schema prefix in the EXEC command.

Microsoft SQL Server 2025 Troubleshooting Guide: Error 12341

Why This Error Happens

Error 12341 occurs because the EXECUTE (or EXEC) statement has been combined with a WITH clause in a syntax pattern that SQL Server does not support, typically when attempting to pass a Common Table Expression (CTE) or query hints directly into a stored procedure call. This usually happens when the WITH clause is intended to define recompile, result sets, or encryption options but is placed incorrectly relative to the execution context. It can also surface when legacy T-SQL scripts, written for older SQL Server versions, are migrated to SQL Server 2025 without updating the deprecated or restructured execution syntax.

Step-by-Step Resolution Guide

Step 1: Identify the Offending Query

• Open SQL Server Management Studio (SSMS) or Azure Data Studio connected to your Windows Server 2025 instance.
• Locate the exact batch or stored procedure call that triggered Error 12341 by checking the SQL Server error log at C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG.
• Note the line number and statement referenced in the error output.

Step 2: Correct the WITH Clause Syntax

• Separate the WITH clause from the EXECUTE statement; a CTE must be defined and resolved in its own statement before being referenced.
• If using WITH RECOMPILE or WITH RESULT SETS, ensure correct placement immediately after the procedure name, for example:

  EXEC dbo.MyProcedure WITH RECOMPILE;

• If the intent was to use a CTE as input, restructure the logic into a temporary table or table variable first, then call EXECUTE separately.

Step 3: Update Deprecated Scripts

• Run the Database Engine Tuning Advisor or sp_helptext on affected stored procedures to locate legacy syntax patterns.
• Replace any outdated execution constructs with syntax compatible with the SQL Server 2025 engine (compatibility level 170).
• Save and version-control the corrected script in your deployment pipeline.

Step 4: Apply Compatibility Level Checks

• Verify the database compatibility level using:

  SELECT name, compatibility_level FROM sys.databases WHERE name = 'YourDatabase';

• If necessary, update it to 170 to ensure full support for current EXECUTE syntax rules:

  ALTER DATABASE YourDatabase SET COMPATIBILITY_LEVEL = 170;

Alternative or Verification Steps

• Re-run the corrected stored procedure or batch in SSMS and confirm Error 12341 no longer appears in the Messages tab or ERRORLOG.
• If the error persists, isolate the WITH clause entirely into a preceding ;WITH CTE AS (...) block and execute the dependent logic as a separate batch to confirm syntax compliance.

Why This Error Happens

This error occurs because SQL Server 2025 restricts IDENTITY column configurations on memory-optimized tables (Hekaton engine) to a strict seed and increment value of (1,1). When a table defined with MEMORY_OPTIMIZED = ON attempts to use a custom seed or increment—such as IDENTITY(100, 5)—the engine’s internal sequence generator rejects the schema because it lacks the native support for non-standard identity jumps found in disk-based tables. This is typically triggered during a CREATE TABLE or ALTER TABLE statement migrated from a legacy on-disk schema without adjusting the identity properties for in-memory compatibility.

Step-by-Step Resolution Guide

Step 1: Identify the Offending Table Schema

• Open SSMS (SQL Server Management Studio) connected to your Windows Server 2025 instance.
• Run a query against sys.tables and sys.identity_columns to locate tables where is_memory_optimized = 1 and either seed_value <> 1 or increment_value <> 1.
• Note the exact table name and schema returned by the error message (%S_MSG will reference the object name in the actual log output).

Step 2: Modify the Identity Definition to Default Values

• Since you cannot ALTER an identity seed/increment on an existing memory-optimized table, you must export the data and recreate the table.
• Script out the table using CREATE TABLE and change the identity definition to the required standard:

  [ID] INT IDENTITY(1,1) NOT NULL PRIMARY KEY NONCLUSTERED

• Ensure the MEMORY_OPTIMIZED = ON and DURABILITY = SCHEMA_AND_DATA (or SCHEMA_ONLY) clauses remain intact in the new CREATE TABLE statement.

Step 3: Migrate Data and Validate Dependencies

• Use SELECT INTO or an INSERT INTO... SELECT statement to move data from the old disk-based or incorrectly configured table into the new memory-optimized table.
• If the application logic strictly requires a specific seed/increment, you must implement this at the application layer or via a SEQUENCE object rather than a native IDENTITY property, as sequences support custom increments and can be used with memory-optimized tables via a default constraint.

Step 4: Recompile Natively Compiled Objects

• If you are using natively compiled stored procedures that reference the old table structure, drop and recreate these modules.
• Navigate to your deployment scripts located typically in C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\DATA\ (adjust path for your specific instance) to update any corresponding .sql deployment files to reflect the new identity syntax.

Alternative or Verification Steps

• Verification: Re-run the original INSERT statement or application transaction that triggered the error. If the insert completes without Error 12339 and the IDENTITY value increments by exactly 1 each time, the fix is successful.
• Alternative: If a specific seed/increment is a hard business requirement, bypass the IDENTITY property entirely by using a SEQUENCE object (CREATE SEQUENCE... START WITH... INCREMENT BY) and assign it as the column’s default value using NEXT VALUE FOR, as this method is fully compatible with custom increments on memory-optimized tables.

Troubleshooting Guide: Error 12337 – Unsupported Sync Method with Database Snapshot Parameters

Why This Error Happens

This error occurs when the sp_addpublication stored procedure is configured with a sync_method of database snapshot or database snapshot character, but the publication type does not support these values—typically because the publication is set up for transactional replication with immediate_sync or includes features like updatable subscriptions that are incompatible with snapshot isolation. SQL Server 2025 enforces stricter validation on replication metadata, so mismatched sync methods between the publication and its articles trigger this failure. Additionally, this can happen if the distribution database or publisher configuration was upgraded from an older SQL Server version where these parameter combinations were loosely validated.

Step-by-Step Resolution Guide

Step 1: Verify the Publication Type and Sync Method Compatibility

• Open SQL Server Management Studio (SSMS) and connect to the Publisher instance.
• Run EXEC sp_helppublication to review the current sync_method value for the affected publication.
• Confirm whether the publication type is “transactional,” “snapshot,” or “merge,” since database snapshot sync methods are only valid for transactional publications without certain restrictive options (e.g., updatable subscribers).

Step 2: Drop and Recreate the Publication with a Supported Sync Method

• If the publication uses an incompatible configuration, drop it using EXEC sp_droppublication @publication = 'YourPublicationName'.
• Recreate the publication using a supported sync_method, such as concurrent or native, depending on your replication requirements:

EXEC sp_addpublication 
    @publication = 'YourPublicationName', 
    @sync_method = N'concurrent';

• Avoid specifying database snapshot or database snapshot character unless the publication explicitly supports snapshot-based initialization without updatable subscriptions.

Step 3: Check for Conflicting Article or Subscription Properties

• Run EXEC sp_helparticle @publication = 'YourPublicationName' to inspect article-level settings that may conflict with the sync method.
• Disable any updatable subscription options or immediate-sync flags tied to the publication, as these are incompatible with database snapshot methods.
• Update the publication properties using sp_changepublication if minor adjustments (rather than a full drop/recreate) resolve the conflict.

Step 4: Validate Distribution Database Configuration

• On the Distributor instance, confirm the distribution database is properly configured and not corrupted by running EXEC sp_helpdistributor.
• If replication metadata appears inconsistent (common after an in-place upgrade to SQL Server 2025), consider reinitializing the distribution database via EXEC sp_adddistributiondb after removing the old one with sp_dropdistributiondb.

Alternative or Verification Steps

• After recreating the publication, run EXEC sp_helppublication again and confirm the sync_method column reflects a supported value (e.g., concurrent or native) with no errors returned.
• As an alternative, initialize the subscription manually using a backup/restore method instead of a snapshot-based sync if database snapshot methods continue to fail due to environment-specific restrictions.

Microsoft SQL Server 2025 Troubleshooting Guide: Error 12338

Why This Error Happens

Error 12338 occurs when the LEN or SUBSTRING functions are called on a column, variable, or expression that uses a Supplementary Character (SC)-enabled collation, typically in conjunction with features or operators that do not support SC-aware processing. This usually happens when a UTF-16 collation handling surrogate pairs is combined with older function overloads, specific FOR XML operations, or certain full-text/legacy query constructs that only process data at the UCS-2 level. Essentially, the engine detects characters outside the Basic Multilingual Plane (BMP) and halts because the calling context cannot safely interpret the surrogate pair.

Step-by-Step Resolution Guide

Step 1: Identify the Offending Column or Collation

• Run SELECT name, collation_name FROM sys.columns WHERE object_id = OBJECT_ID('YourTableName') to locate columns using an SC collation (e.g., Latin1_General_100_CI_AS_SC or similar variants ending in _SC).
• Check the specific query or stored procedure that triggered the error to confirm whether LEN() or SUBSTRING() is being applied directly to this column.

Step 2: Modify the Query to Avoid Unsupported Function Combinations

• Replace direct calls to SUBSTRING() or LEN() on the SC column with a COLLATE clause to temporarily cast the string to a non-SC collation if supplementary characters are not required for that specific operation:
  SELECT LEN(YourColumn COLLATE Latin1_General_100_CI_AS) FROM YourTable;
• Avoid nesting these functions inside FOR XML PATH or Full-Text Search CONTAINSTABLE predicates, as these specific combinations are known triggers for Error 12338 in SQL Server 2025.

Step 3: Update Database Compatibility Level

• Open SQL Server Management Studio (SSMS) and right-click the database, then select Properties > Options.
• Ensure the Compatibility Level is set to the latest supported mode (160 or higher) to ensure the Query Optimizer uses the newest SC-handling logic paths.
• Run ALTER DATABASE YourDB SET COMPATIBILITY_LEVEL = 160; via T-SQL if the GUI setting fails to apply.

Step 4: Apply the Latest Cumulative Update (CU)

• Navigate to the Microsoft Update Catalog and verify the current build number of your instance using SELECT @@VERSION;.
• Download and install the latest Cumulative Update for SQL Server 2025, as early builds have known indexing bugs regarding SC collations in Windows Server 2025 environments.
• Restart the SQL Server service via services.msc or net stop MSSQLSERVER && net start MSSQLSERVER after the patch installation.

Alternative or Verification Steps

• To verify the fix, re-run the original query in SSMS; if successful, the result set should display character lengths correctly without throwing the %S_MSG placeholder error.
• As an alternative, if the SC collation is not strictly required for internationalization needs, consider altering the column collation entirely via ALTER TABLE YourTable ALTER COLUMN YourColumn VARCHAR(50) COLLATE Latin1_General_100_CI_AS; to permanently resolve compatibility issues with legacy string functions.