Troubleshooting Guide: Error 10719 in SQL Server 2025

Why This Error Happens

This error occurs because SQL Server’s In-Memory OLTP engine enforces strict durability consistency rules between related memory-optimized tables. A table with DURABILITY = SCHEMA_AND_DATA guarantees that both schema and data persist across restarts and survive a crash, but a table with DURABILITY = SCHEMA_ONLY only retains its structure, losing all data on restart. Allowing a foreign key from a durable table to a non-durable table would create an unresolvable referential integrity gap any time the server restarts and the SCHEMA_ONLY table’s data is wiped, so SQL Server blocks the relationship outright at design time.

Step-by-Step Resolution Guide

Step 1: Identify the Conflicting Table Definitions

• Open SQL Server Management Studio (SSMS) or Azure Data Studio and connect to the target instance.
• Run the following query to check durability settings on both tables:

SELECT name, durability_desc 
FROM sys.tables 
WHERE name IN ('YourParentTable', 'YourChildTable');

• Confirm which table is set to SCHEMA_AND_DATA and which is set to SCHEMA_ONLY.

Step 2: Decide on a Durability Alignment Strategy

• Determine whether the referenced (parent) table actually needs its data persisted across restarts.
• If data persistence is required for both tables, plan to alter the SCHEMA_ONLY table to SCHEMA_AND_DATA.
• If data loss on restart is acceptable for the relationship, plan to remove the durability mismatch by converting the parent table to SCHEMA_ONLY as well (only if business logic allows).

Step 3: Recreate the Table with Matching Durability

• Since DURABILITY cannot be altered with a simple ALTER TABLE statement, you must drop and recreate the table:

DROP TABLE dbo.YourSchemaOnlyTable;

CREATE TABLE dbo.YourSchemaOnlyTable
(
    Id INT NOT NULL PRIMARY KEY NONCLUSTERED,
    -- other columns
)
WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA);

• Re-insert any required seed data since SCHEMA_ONLY tables lose all rows on restart.

Step 4: Re-Establish the Foreign Key Constraint

• Once both tables share DURABILITY = SCHEMA_AND_DATA, add the foreign key:

ALTER TABLE dbo.YourChildTable
ADD CONSTRAINT FK_Child_Parent 
FOREIGN KEY (ParentId) REFERENCES dbo.YourSchemaOnlyTable(Id);

• Validate the constraint was created successfully without triggering Error 10719.

Step 5: Rebuild Dependent Objects and Indexes

• Check for natively compiled stored procedures or indexes that referenced the old table object (since drop/recreate changes the object_id).
• Recompile any natively compiled modules using sp_recompile or by altering the procedure with WITH SCHEMABINDING re-validated.

Alternative or Verification Steps

• Verify the fix: Re-run the sys.tables query from Step 1 to confirm both tables now report SCHEMA_AND_DATA, then attempt an INSERT on the child table referencing a valid parent key to confirm referential integrity is enforced.
• Alternative workaround: If persisting data on the SCHEMA_ONLY table is not feasible (e.g., it’s used purely as a staging/temp table), remove the foreign key constraint entirely and enforce referential integrity at the application layer instead, since memory-optimized tables do not strictly require database-level FKs for performance-critical staging scenarios.

Troubleshooting Guide: SQL Server Error 10718 — “Query Hints Are Not Allowed in Nested INSERT”

Why This Error Happens

This error occurs because SQL Server 2025 does not permit query hints (such as OPTION clauses) to be applied directly to an INSERT statement that contains a nested INSERT...EXEC, a nested DML statement inside a CTE, or an INSERT sourced from a nested SELECT with hints at the wrong scope. The parser identifies that the hint is attached to the outer statement wrapper rather than the innermost valid SELECT, causing a syntax validation failure during the query compilation phase. This is typically a T-SQL authoring issue rather than a server configuration, permissions, or networking problem.

Step-by-Step Resolution Guide

Step 1: Identify the Offending Query Hint Placement

• Open the stored procedure, script, or application-generated query that triggered Error 10718 in SQL Server Management Studio (SSMS) or Azure Data Studio.
• Locate the OPTION (...) clause (e.g., OPTION (RECOMPILE), OPTION (MAXDOP 1), OPTION (HASH JOIN)) and confirm it is attached to an INSERT statement that wraps a nested INSERT, MERGE, or multi-level SELECT.
• Check the exact statement structure — nested inserts commonly appear inside CTEs, derived tables, or INSERT...EXEC constructs.

Step 2: Relocate the Query Hint to the Correct Scope

• Move the OPTION (...) clause so it is applied only to the innermost SELECT statement that actually requires the hint, not the outer INSERT wrapper.
• Example correction:

  INSERT INTO TargetTable (Col1, Col2)
  SELECT Col1, Col2
  FROM SourceTable
  OPTION (RECOMPILE);

• If the hint was intended for a nested SELECT inside a CTE, apply it directly after that CTE’s final SELECT, not after the outer INSERT.

Step 3: Refactor Nested INSERT/EXEC Constructs

• If using INSERT INTO TargetTable EXEC dbo.SomeProcedure, remove any OPTION clause from this line entirely, since hints are not valid in this syntax context.
• Move any required hints (like RECOMPILE) inside the stored procedure definition itself, applied to the internal query that needs it.
• Save the corrected script and re-run it in a test/staging instance of SQL Server 2025 before deploying to production.

Step 4: Validate Compatibility Level Settings

• Run SELECT compatibility_level FROM sys.databases WHERE name = 'YourDatabaseName'; to confirm the database is running under SQL Server 2025’s expected compatibility level.
• If the compatibility level is outdated (e.g., inherited from a migrated SQL Server 2016/2017 database), update it using:

  ALTER DATABASE YourDatabaseName SET COMPATIBILITY_LEVEL = 170;

• Re-test the query after updating, as older compatibility levels can sometimes produce inconsistent hint-parsing behavior.

Alternative or Verification Steps

• Re-execute the corrected query in SSMS and confirm it completes without raising Error 10718; check the Messages tab for a clean “rows affected” output.
• If the error persists, isolate the nested statement by running it independently (without the outer INSERT wrapper) to confirm the hint works in isolation, then incrementally reintroduce the outer structure to pinpoint the exact nesting level causing the conflict.

Troubleshooting Guide: SQL Server Error 10717

Why This Error Happens

Error 10717 occurs when a Transact-SQL statement attempts to combine an OUTPUT clause (represented as %S_MSG in the error message) with a nested DML operation inside the FROM clause of an INSERT statement. SQL Server’s query processor cannot resolve the OUTPUT clause when the data source itself contains a composable DML operation, such as INSERT ... OUTPUT used as a derived table. This is a T-SQL syntax limitation rather than a server configuration or permissions issue, and it typically surfaces after a migration, application update, or when legacy stored procedures are executed against SQL Server 2025 for the first time.

Step-by-Step Resolution Guide

Step 1: Identify the Offending Query

• Review SQL Server error logs via SQL Server Management Studio (SSMS) under Management > SQL Server Logs to locate the exact stored procedure or ad-hoc query triggering Error 10717.
• Use sys.dm_exec_query_stats combined with sys.dm_exec_sql_text to pull the full text of recently failed queries.
• Confirm the query structure contains a nested INSERT (or UPDATE/DELETE) inside a FROM clause alongside an OUTPUT clause.

Step 2: Refactor the Query Structure

• Break the nested DML operation into two separate steps instead of combining them in a single composable statement.
• First, execute the nested INSERT ... OUTPUT INTO @TableVariable to capture results into a temporary table or table variable.
• Then, use that table variable as the source for the outer INSERT statement, removing the illegal nested composition.
• Example pattern: declare a TABLE variable, populate it via the inner OUTPUT INTO, then reference that variable in the outer INSERT ... SELECT.

Step 3: Update Application and Procedure Code

• Open the affected .sql files or stored procedure definitions in SSMS or Azure Data Studio.
• Search for patterns like INSERT INTO ... FROM (INSERT INTO ...) and apply the two-step refactor from Step 2.
• Save and redeploy the updated stored procedures using ALTER PROCEDURE to ensure the compiled plan is refreshed.

Step 4: Clear Cached Execution Plans

• Run DBCC FREEPROCCACHE (in a controlled maintenance window) to remove any stale cached plans referencing the old, invalid syntax.
• Restart the SQL Server service via Services.msc on Windows Server 2025/Windows 11 if the error persists after code changes, ensuring the instance fully reloads metadata.

Alternative or Verification Steps

• Re-run the original query or trigger the application workflow that previously failed; confirm no Error 10717 appears in the SQL Server Error Log or Windows Event Viewer under Applications and Services Logs > MSSQLSERVER.
• As an alternative, if refactoring isn’t immediately feasible, wrap the nested DML logic inside a user-defined function or a multi-statement procedure that separates the OUTPUT capture from the final INSERT, avoiding the composable statement restriction entirely.

Troubleshooting Guide: SQL Server Error 10716 — “A Nested INSERT Statement”

Why This Error Happens

Error 10716 occurs when a Data Manipulation Language (DML) statement, such as an INSERT, UPDATE, or DELETE, is nested inside another DML statement in a way that SQL Server 2025 does not support—most commonly when a subquery used in an OUTPUT clause or a data-modifying CTE attempts to perform another write operation. This is typically caused by poorly structured T-SQL logic rather than a server misconfiguration, often introduced during a migration from an older SQL Server version where looser syntax was tolerated. It can also surface after upgrading to Windows Server 2025 if legacy stored procedures or ORM-generated queries (e.g., from Entity Framework or Hibernate) rely on deprecated nested DML patterns that the new Query Optimizer now flags more strictly.

Step-by-Step Resolution Guide

Step 1: Identify the Offending Query

• Open SQL Server Management Studio (SSMS) and locate the exact batch ID referenced in the error log via Event Viewer > Windows Logs > Application.
• Run SELECT * FROM sys.dm_exec_query_stats cross-referenced with sys.dm_exec_sql_text to pinpoint the exact nested statement.
• Check if the query uses an OUTPUT INTO clause that references a table targeted by a trigger, which is a common hidden trigger for this error.

Step 2: Refactor the Nested Insert Logic

• Break the nested statement into two distinct, sequential steps using a temporary table or a table variable.
• Example fix: Instead of INSERT INTO TableA OUTPUT INSERTED.* INTO TableB (SELECT * FROM TableC), first insert into a #TempTable, then perform the secondary insert from the temp table into TableB.
• Ensure no INSTEAD OF triggers on the target table are attempting to re-fire the same INSERT command recursively.

Step 3: Update Compatibility Level and Drivers

• Navigate to Server Properties > Database Properties in SSMS and verify the Compatibility Level is explicitly set to 170 (SQL Server 2025) rather than inheriting a legacy level like 110.
• On Windows 11/Server 2025 clients, update the ODBC Driver to the latest version (18.x or higher) via C:\Windows\System32\odbcad32.exe to ensure the client-side driver correctly parses the new T-SQL syntax rules.
• Restart the SQL Server (MSSQLSERVER) service via services.msc to clear the query plan cache.

Step 4: Clear the Procedure Cache

• If the error persists due to a stale execution plan, run DBCC FREEPROCCACHE to force SQL Server to recompile the stored procedures.
• Isolate the specific plan using DBCC FREEPROCCACHE (plan_handle) to avoid flushing the cache for the entire instance in a production environment.

Alternative or Verification Steps

• Verification: Re-run the specific transaction in a Test environment with SET STATISTICS PROFILE ON to confirm the execution plan no longer shows a “Nested Loop” conflict on the write operation.
• Alternative: If refactoring the code is not immediately possible, wrap the nested logic in a MERGE statement instead of a standard INSERT, as the MERGE syntax in SQL Server 2025 handles conditional nested logic without triggering the 10716 restriction.

Troubleshooting Guide: SQL Server Error 10715 – ‘Is Not a Valid Hint’

Why This Error Happens

Error 10715 occurs when SQL Server’s query parser encounters a hint in a query (within an OPTION clause, WITH clause, or OPTIMIZE FOR statement) that it does not recognize as a valid, supported keyword. This is typically caused by a typo in the hint name, the use of a deprecated hint that has been removed in SQL Server 2025, or application code written for a different database platform (or an older/newer T-SQL dialect) that uses incompatible syntax. It can also surface after a Compatibility Level change, where hints valid in older modes are no longer parsed the same way.

Step-by-Step Resolution Guide

Step 1: Identify the Exact Offending Query

• Capture the exact T-SQL statement throwing error 10715 using SQL Server Profiler, Extended Events, or by reviewing the application’s error logs.
• Look for the specific hint string referenced in the error message (e.g., OPTION (HASH JOIN) vs. an invalid variant like OPTION (HASHJOIN)).
• Check C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG for the full query context if captured.

Step 2: Validate Hint Syntax Against Supported Keywords

• Cross-reference the hint against the official Microsoft documentation for supported Query, Table, and Join hints in SQL Server 2025.
• Correct common mistakes such as missing parentheses, extra commas, or hints merged together without spaces.
• Example of a corrected syntax:

  SELECT * FROM Orders WITH (NOLOCK) OPTION (RECOMPILE);

Step 3: Check for Deprecated or Removed Hints

• Review the “Deprecated Database Engine Features” documentation for SQL Server 2025, as some legacy hints (common in SQL Server 2012–2016 era code) may have been fully removed in this release.
• If a legacy hint is found, replace it with its modern equivalent or remove it if it no longer serves a purpose under the current Query Optimizer.
• Update any ORM frameworks (like Entity Framework or Dapper) or stored procedures that may be auto-generating the invalid hint string.

Step 4: Verify Database Compatibility Level

• Open SQL Server Management Studio (SSMS) and run the following command to check the current compatibility level:

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

• If the hint relies on behavior from a specific compatibility mode, ensure the database is set to the correct level (e.g., 170 for SQL Server 2025) using:

  ALTER DATABASE YourDatabaseName SET COMPATIBILITY_LEVEL = 170;

Alternative or Verification Steps

• Verification: Re-run the corrected query in a new SSMS query window connected to the Windows Server 2025 instance; confirm the query executes successfully and returns the expected execution plan without triggering Error 10715.
• Alternative: If the hint is syntactically correct but still fails, temporarily remove the hint entirely to confirm the query runs, then consult the Microsoft Q&A forums or Azure Data Studio’s IntelliSense validation to confirm the hint is supported in the 2025 engine build you are running.

Why This Error Happens

This error occurs because the T-SQL MERGE statement syntax restricts each WHEN MATCHED, WHEN NOT MATCHED BY TARGET, or WHEN NOT MATCHED BY SOURCE clause to containing only one UPDATE, one DELETE, or one INSERT action. Error 10714 is raised when a developer mistakenly writes multiple identical action types (e.g., two UPDATE actions) within the same WHEN clause, which the SQL Server 2025 parser flags as invalid during query compilation. This is purely a syntax/logic error in the query itself rather than a server configuration or permissions issue.

Step-by-Step Resolution Guide

Step 1: Locate the Offending MERGE Statement

• Open SQL Server Management Studio (SSMS) connected to your SQL Server 2025 instance.
• Check the Messages tab for the exact line number referenced alongside Error 10714.
• Copy the full MERGE statement into a new query window for isolated editing.

Step 2: Audit the WHEN Clauses for Duplicate Actions

• Review each WHEN MATCHED, WHEN NOT MATCHED BY TARGET, and WHEN NOT MATCHED BY SOURCE block individually.
• Confirm that each block contains only a single UPDATE, DELETE, or INSERT action, never two of the same type in one block.
• Example of invalid syntax to look for:

  WHEN MATCHED THEN
      UPDATE SET Col1 = Source.Col1
      UPDATE SET Col2 = Source.Col2

Step 3: Consolidate Duplicate Actions into a Single Statement

• Merge the duplicate UPDATE (or INSERT/DELETE) actions into one combined SET clause instead of two separate statements.
• Corrected example:

  WHEN MATCHED THEN
      UPDATE SET Col1 = Source.Col1, Col2 = Source.Col2

• If you need conditional logic (e.g., update differently based on a condition), use multiple WHEN MATCHED clauses with AND conditions rather than duplicate action types in one clause:

  WHEN MATCHED AND Source.Status = 'A' THEN
      UPDATE SET Col1 = Source.Col1
  WHEN MATCHED AND Source.Status = 'B' THEN
      UPDATE SET Col2 = Source.Col2

Step 4: Validate Query Syntax Before Execution

• Highlight the corrected MERGE statement in SSMS and use Ctrl+F5 (Parse) to validate syntax without executing it.
• Confirm no additional instances of Error 10714 or related parser errors (like 10712 or 10713) appear.

Alternative or Verification Steps

• Execute the corrected MERGE statement against a test table or within a transaction (BEGIN TRAN ... ROLLBACK) to confirm it runs without error 10714 before deploying to production.
• If the MERGE logic is overly complex, consider refactoring it into separate explicit UPDATE, INSERT, and DELETE statements wrapped in a single transaction as an alternative to a single MERGE block.

Troubleshooting Guide: MSSQL Error 10713 – MERGE Statement Termination

Why This Error Happens

This error occurs because the T-SQL parser in SQL Server 2025 strictly enforces ANSI SQL compliance rules requiring a MERGE statement to be terminated with a semi-colon (;). Unlike older engine versions that sometimes tolerated missing terminators due to lenient parsing modes, the updated query compiler treats this omission as a hard syntax error rather than a warning. This is purely a syntax-level requirement and is not related to permissions, corrupted files, or network configuration.

Step-by-Step Resolution Guide

Step 1: Locate the Offending MERGE Statement

• Open the query or stored procedure flagged in SQL Server Management Studio (SSMS) or Azure Data Studio.
• Use Ctrl+F to search for the keyword MERGE within the script.
• Note the line number referenced in the Error 10713 message in the Messages pane.

Step 2: Add the Required Semi-Colon Terminator

• Scroll to the end of the full MERGE statement, which typically concludes after the WHEN MATCHED, WHEN NOT MATCHED, or OUTPUT clauses.
• Insert a semi-colon (;) immediately after the final clause of the statement, for example:

  MERGE INTO TargetTable AS T
  USING SourceTable AS S
  ON T.ID = S.ID
  WHEN MATCHED THEN UPDATE SET T.Value = S.Value
  WHEN NOT MATCHED THEN INSERT (ID, Value) VALUES (S.ID, S.Value);

• Save the script and re-run it in SSMS using Execute (F5).

Step 3: Audit Related Scripts and Stored Procedures

• Run a text search across your .sql deployment files or source control repository for all instances of MERGE INTO or MERGE (.
• Check each instance to confirm a semi-colon exists at the termination point of the statement.
• If using automated deployment pipelines (e.g., DACPAC or SSDT projects), rebuild the project locally to catch syntax errors before deployment to Windows Server 2025.

Step 4: Update Legacy Code Generation Tools

• If the MERGE statements are auto-generated by an ORM or legacy script generator, check the tool’s settings or update to the latest version to ensure it appends semi-colons by default.
• Configure “Force Terminator” or “Strict ANSI Mode” settings if the tool offers them.

Alternative or Verification Steps

• Verify the fix: Re-execute the corrected script in SSMS; a successful run with no red error text in the Messages pane confirms Error 10713 is resolved.
• Alternative check: Enable SET ANSI_WARNINGS ON; at the top of your session to catch similar missing-terminator issues in other T-SQL statements before they cause runtime failures.

Troubleshooting Guide: SQL Server Error 10712 – Pseudocolumn Cannot Be Part of an Index

Why This Error Happens

This error occurs when a Transact-SQL statement attempts to include a system-generated pseudocolumn—such as $ACTION, $PARTITION, $IDENTITY, or a row-level security predicate column—directly within an index definition or as a partitioning column. SQL Server 2025 reserves these pseudocolumns for internal engine operations (like MERGE statement output or partition function evaluation), and they do not represent actual stored data that can be indexed or physically partitioned. This typically happens due to a scripting error, a migrated schema from an older database engine with different pseudocolumn rules, or a misunderstanding of which function-derived columns are indexable.

Step-by-Step Resolution Guide

Step 1: Identify the Offending Column in the Script

• Open the failing script in SQL Server Management Studio (SSMS) or Azure Data Studio and locate the CREATE INDEX or CREATE TABLE ... PARTITION BY statement referenced in the error message.
• Check the %.*ls placeholder in the actual error output—it will specify the exact pseudocolumn name (e.g., $PARTITION.MyPartitionFunction(ColumnName)).
• Run sp_helpindex 'YourTableName' to review existing index definitions that might reference computed or system columns.

Step 2: Replace the Pseudocolumn with a Persisted Computed Column

• Since pseudocolumns cannot be indexed directly, create a physical computed column that derives the same value.
• Use syntax similar to:

ALTER TABLE dbo.YourTable
ADD PartitionValue AS ($PARTITION.MyPartitionFunction(DateColumn)) PERSISTED;

• Ensure the PERSISTED keyword is included, as SQL Server 2025 requires physically stored values for indexing.
• Once persisted, build the index against PartitionValue instead of the pseudocolumn.

Step 3: Correct Partitioning Syntax

• If the error originates from a CREATE TABLE or CREATE INDEX ... ON PARTITION SCHEME statement, verify you are referencing the actual partitioning column (e.g., OrderDate) rather than the $PARTITION function itself.
• Update the statement to:

CREATE PARTITION SCHEME MyScheme
AS PARTITION MyPartitionFunction
TO ([PRIMARY], [FG1], [FG2]);

• Confirm the partition function is applied to a standard column in the CREATE TABLE statement, not within the index key list.

Step 4: Validate Row-Level Security or System-Versioned Columns

• If using temporal tables, ensure SysStartTime and SysEndTime are not confused with pseudocolumns—these are valid for indexing.
• For Row-Level Security (RLS), confirm no security predicate function output is being referenced directly as an index key.

Step 5: Rebuild and Test the Index

• Drop any partially created index objects using DROP INDEX IF EXISTS.
• Re-run the corrected CREATE INDEX statement with the new persisted computed column.
• Check SQL Server Error Logs (Dumps and Logs folder under C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\) to confirm no residual errors.

Alternative or Verification Steps

• Run SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID('YourTable') to confirm the new index was created successfully without referencing pseudocolumns.
• If persisted computed columns are not viable due to performance constraints, consider using an indexed view that pre-calculates the pseudocolumn-derived value instead.

Troubleshooting Guide: SQL Server Error 10711

An action of type ‘INSERT’ is not allowed in the ‘%S_MSG’ clause of a MERGE statement.

Why This Error Happens

This error occurs because SQL Server’s MERGE statement syntax strictly enforces which Data Manipulation Language (DML) actions are valid within specific clauses—specifically, an INSERT action cannot be placed inside a WHEN MATCHED or WHEN NOT MATCHED BY SOURCE clause. This is not a permissions or connectivity issue, but rather a T-SQL syntax violation where the logic of the query does not align with the MERGE statement’s defined grammar. It typically arises from a copy-paste error or a misunderstanding of how WHEN NOT MATCHED [BY TARGET] differs from WHEN MATCHED.

Step-by-Step Resolution Guide

Step 1: Identify the Offending Clause

• Open SSMS (SQL Server Management Studio) or Azure Data Studio and locate the exact line number referenced in the error output.
• Look specifically for an INSERT keyword following a WHEN MATCHED or WHEN NOT MATCHED BY SOURCE clause.
• Note that INSERT logic is only valid within the WHEN NOT MATCHED [BY TARGET] clause.

Step 2: Correct the MERGE Statement Syntax

• Move the INSERT logic exclusively under the WHEN NOT MATCHED BY TARGET THEN INSERT (...) VALUES (...) block.
• Ensure your WHEN MATCHED THEN clause only contains UPDATE SET or DELETE actions.
• Verify your WHEN NOT MATCHED BY SOURCE THEN clause (if used) only contains UPDATE SET or DELETE actions.
• Example of valid structure:

  MERGE INTO TargetTable AS Target
  USING SourceTable AS Source
  ON Target.ID = Source.ID
  WHEN MATCHED THEN
      UPDATE SET Target.Value = Source.Value
  WHEN NOT MATCHED BY TARGET THEN
      INSERT (ID, Value) VALUES (Source.ID, Source.Value);

Step 3: Validate Query Parsing

• In SSMS, highlight the corrected query and use the Parse feature (Ctrl+F5) to check syntax without executing the query.
• Check the Messages tab for confirmation that the command completed successfully without syntax errors.

Step 4: Check for Linked Server or ORM-Generated Scripts

• If the MERGE statement was auto-generated by an ORM (like Entity Framework) or a schema comparison tool (like SSDT/DACPAC), regenerate the script after updating the tool to the latest version compatible with SQL Server 2025.
• Manually audit generated scripts before execution, as older tooling versions may format MERGE logic incorrectly.

Alternative or Verification Steps

• Verification: Execute the query in a non-production/staging environment on Windows Server 2025 and confirm that the INSERT operation successfully adds rows to the target table without triggering Error 10711.
• Alternative: If complex MERGE logic continues to cause confusion or errors, consider refactoring the operation into separate, standard INSERT INTO... SELECT and UPDATE... FROM statements wrapped in an explicit transaction (BEGIN TRAN/COMMIT) for easier debugging and maintenance.

Troubleshooting Guide: SQL Server Error 10710 – Invalid Action in MERGE ‘WHEN NOT MATCHED’ Clause

Why This Error Happens

This error occurs because the T-SQL MERGE statement syntax strictly limits the WHEN NOT MATCHED clause to only INSERT actions, yet the query is attempting to execute an UPDATE or DELETE action within that specific branch. This is typically caused by a logic error in the query where the developer intended to place that action in the WHEN MATCHED clause instead. It can also happen if the query was auto-generated or ported from a different SQL dialect (like PostgreSQL or MySQL) that handles upsert logic differently than SQL Server 2025.

Step-by-Step Resolution Guide

Step 1: Review the MERGE Statement Syntax

• Open the query in SQL Server Management Studio (SSMS) or Azure Data Studio connected to your Windows Server 2025 instance.
• Locate the WHEN NOT MATCHED [BY TARGET] clause specifically, as this clause only accepts the INSERT keyword.
• Check if an UPDATE or DELETE statement is mistakenly nested inside this block.

Step 2: Correct the Clause Logic

• Move any UPDATE logic to the WHEN MATCHED THEN UPDATE SET... clause, as this is the only clause that supports updates.
• If you need to handle rows existing only in the target table (not the source), ensure that logic resides in the WHEN NOT MATCHED BY SOURCE clause, not WHEN NOT MATCHED [BY TARGET].
• Verify that the INSERT statement in the WHEN NOT MATCHED clause includes a column list and corresponding VALUES, rather than an UPDATE SET syntax.

Step 3: Validate Table Schema and Alias Consistency

• Ensure that column names referenced in the INSERT action match the exact schema of the target table.
• Check for typos in table aliases (e.g., S for source, T for target) that might cause the parser to misinterpret which table the action applies to.
• Run sp_help 'YourTableName' in a new query window to confirm the current schema matches the columns used in the INSERT clause.

Step 4: Recompile and Execute the Query

• Save the corrected script and clear the procedure cache if this was part of a stored procedure using DBCC FREEPROCCACHE (use cautiously in production environments).
• Execute the query again in a test environment to confirm the parser accepts the syntax.

Alternative or Verification Steps

• Verification: Run the MERGE statement with a SET NOCOUNT ON; flag in a transaction that you roll back (BEGIN TRAN... ROLLBACK) to confirm the syntax compiles without triggering Error 10710 before committing to production data.
• Alternative: If the MERGE logic remains overly complex and prone to errors, consider refactoring the logic into separate, explicit UPDATE and INSERT statements wrapped in a single transaction to achieve the same “upsert” result without the restrictive clause limitations.

Troubleshooting Guide: SQL Server Error 10709

Why This Error Happens

This error occurs when a VALUES clause in a table value constructor contains rows with an inconsistent number of columns, such as VALUES (1, 'A'), (2, 'B', 'C'). SQL Server strictly enforces that every row inserted via a multi-row INSERT statement, or used in a derived table, must have identical column counts. This is typically caused by manually constructed INSERT scripts, dynamically generated SQL from application code, or copy-paste errors when combining multiple row sets.

Step-by-Step Resolution Guide

Step 1: Identify the Offending Statement

• Check the SQL Server error log or the application’s query log to locate the exact INSERT ... VALUES statement that triggered Error 10709.
• If the query was generated dynamically (e.g., from an ORM or a stored procedure building SQL strings), capture the actual rendered SQL text using SQL Server Profiler or Extended Events (sqlserver.sql_statement_completed).

Step 2: Audit Column Counts Across All Rows

• Manually count the comma-separated values inside each parenthesis group in the VALUES clause.
• Pay special attention to NULL placeholders — a missing NULL for an optional column is the most common cause of mismatched counts.
• Example of a broken statement:

INSERT INTO dbo.Employees (ID, Name, Department)
VALUES (1, 'John', 'Sales'),
       (2, 'Jane');

• Corrected version:

INSERT INTO dbo.Employees (ID, Name, Department)
VALUES (1, 'John', 'Sales'),
       (2, 'Jane', NULL);

Step 3: Validate Dynamic SQL Generation Logic

• If the query is built programmatically (C#, Python, PowerShell, etc.), inspect the loop or string-concatenation logic that appends each row tuple.
• Ensure that every iteration appends the same number of placeholders/parameters, especially when optional or nullable fields are conditionally skipped.
• Add a validation check before execution, such as comparing columns.Count against each row’s value array length, to fail early with a clearer custom error.

Step 4: Test the Corrected Query in SSMS

• Open SQL Server Management Studio (SSMS) connected to your Windows Server 2025 instance.
• Run the corrected INSERT statement in isolation against a test table to confirm it executes without Error 10709.
• Use SET NOEXEC ON combined with SET PARSEONLY ON beforehand if you only want to validate syntax without committing data.

Step 5: Re-deploy and Monitor

• Push the corrected script or application code update to the affected environment.
• Monitor 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) for recurrence of Error 10709 over the next few execution cycles.

Alternative or Verification Steps

• Verify the fix by executing the corrected statement directly in SSMS or Azure Data Studio and confirming a successful “rows affected” message with no error raised.
• As an alternative to multi-row VALUES constructors, especially for dynamically generated inserts, switch to individual INSERT statements per row or use a table-valued parameter (TVP) passed into a stored procedure, which eliminates column-count mismatch risks entirely.

Troubleshooting Guide: SQL Server Error 10708 — “DEFAULT is not allowed on the right hand side of ‘%.*ls'”

Why This Error Happens

This error occurs when a T-SQL statement attempts to use the DEFAULT keyword in an invalid context, such as within an expression, comparison, or on the right-hand side of an assignment where only a literal value or valid expression is expected. It is almost always caused by malformed INSERT, UPDATE, or stored procedure syntax rather than a server configuration or permissions issue. This can also surface after a migration or script generation process (e.g., from an older SQL Server version or third-party ORM) that produces incompatible syntax with SQL Server 2025’s parser.

Step-by-Step Resolution Guide

Step 1: Identify the Offending Query

• Check the SQL Server Error Log or SSMS output pane for the exact statement that triggered Error 10708.
• Use SELECT * FROM sys.dm_exec_requests combined with sys.dm_exec_sql_text(sql_handle) if the query was executed from an application and you need to capture it live.
• Look specifically for patterns like UPDATE table SET column = DEFAULT WHERE column2 = DEFAULT — the second DEFAULT on a WHERE clause is invalid.

Step 2: Correct the T-SQL Syntax

• Replace DEFAULT usage in WHERE clauses, comparison operators, or function arguments with the actual literal value or a subquery that retrieves the column’s default constraint value.
• Valid usage is limited to INSERT INTO table (col) VALUES (DEFAULT) or UPDATE table SET col = DEFAULT — never in filtering or comparison logic.
• If generated dynamically, inspect the application/ORM layer (e.g., Entity Framework, Dapper) for faulty SQL string generation and patch the query builder logic.

Step 3: Validate Default Constraints on the Table

• Run sp_helpconstraint 'YourTableName' to confirm the column actually has a DEFAULT constraint defined.
• If no default constraint exists, DEFAULT cannot be used for that column at all — you must either add one via ALTER TABLE YourTableName ADD CONSTRAINT DF_ColumnName DEFAULT (value) FOR ColumnName or explicitly supply a value.

Step 4: Re-run the Corrected Script in a Test Environment

• Execute the revised query against a non-production database or restored backup copy first.
• Confirm Error 10708 no longer appears in the SSMS Messages tab or Windows Event Viewer under Applications and Services Logs > MSSQLSERVER.

Alternative or Verification Steps

• Re-execute the corrected statement in production during a low-traffic window and monitor sys.dm_exec_sql_text or the SQL Server Error Log to confirm Error 10708 no longer appears.
• If the error persists after syntax correction, use SQL Server Profiler or Extended Events to trace the exact statement text being sent to the engine, as the issue may originate from a cached or parameterized query plan rather than the raw script you reviewed.

Troubleshooting Guide: Error 10707 – CUBE()/ROLLUP() Not Allowed in Current Compatibility Mode

Why This Error Happens

This error occurs because the database you are querying is running in a SQL Server compatibility level lower than 100 (SQL Server 2008), which predates the syntax support for CUBE() and ROLLUP() as standalone grouping functions. Even though the SQL Server 2025 engine itself fully supports this syntax, a database can be configured to emulate an older compatibility level after being migrated, restored, or attached from a legacy system. As a result, the query optimizer rejects the modern grouping construct because it is parsing the T-SQL against outdated language rules.

Step-by-Step Resolution Guide

Step 1: Identify the Current Compatibility Level

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

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

• Confirm that the compatibility_level value is below 100 (e.g., 80 or 90).

Step 2: Update the Database Compatibility Level

• Execute the following T-SQL command to raise the compatibility level to a supported mode (150 or higher is recommended for SQL Server 2025 features):

ALTER DATABASE [YourDatabaseName] 
SET COMPATIBILITY_LEVEL = 160;

• Alternatively, use the GUI: Right-click the database in SSMS > Properties > Options > set “Compatibility level” to “SQL Server 2022 (160)” or higher.

Step 3: Verify Application and Linked Server Dependencies

• Check if any linked servers or legacy applications specifically rely on older T-SQL behaviors that might break with a compatibility level change.
• Review the Windows Server 2025 Event Viewer (under Applications and Services Logs > MSSQLSERVER) for any dependent warnings triggered immediately after the change.

Step 4: Re-run the Query

• Re-execute the original query containing the CUBE() or ROLLUP() function in SSMS or your application.
• Confirm that Error 10707 no longer appears in the results pane or application logs.

Alternative or Verification Steps

• Verification: Run SELECT compatibility_level FROM sys.databases WHERE name = 'YourDatabaseName'; again to confirm the value is now 100 or higher.
• Alternative Fix: If changing the compatibility level is not feasible due to legacy app constraints, rewrite the query using the classic GROUP BY ... WITH CUBE or GROUP BY ... WITH ROLLUP syntax, which is supported across all compatibility levels.

Troubleshooting Guide: SQL Server Error 10706

Why This Error Happens

Error 10706 occurs when a GROUP BY clause using grouping sets (such as GROUPING SETS, ROLLUP, or CUBE) exceeds the internal limit on the number of expressions SQL Server can process when expanding those sets. This typically happens because each grouping set combination multiplies the total expression count internally, and complex or deeply nested grouping definitions can quickly surpass the engine’s hard-coded maximum. It is a query design limitation rather than a server configuration or permissions issue, meaning the fix must occur at the T-SQL level rather than through system settings.

Step-by-Step Resolution Guide

Step 1: Identify the Offending Query

• Capture the exact query from the application log or SQL Server error details (Event ID 10706 in Windows Event Viewer or SQL Server error log).
• Run the query manually in SQL Server Management Studio (SSMS) connected to your SQL Server 2025 instance to confirm it reproduces the error.
• Count the total number of columns referenced inside GROUP BY, GROUPING SETS(), ROLLUP(), or CUBE().

Step 2: Reduce the Complexity of Grouping Sets

• Break large GROUPING SETS definitions into smaller, separate queries combined with UNION ALL instead of one massive grouping set list.
• Replace CUBE() with explicit GROUPING SETS() listing only the combinations you actually need, since CUBE generates every possible combination and can rapidly exceed limits.
• Example rewrite:

  SELECT Region, Product, SUM(Sales)
  FROM Sales
  GROUP BY GROUPING SETS (
      (Region, Product),
      (Region),
      ()
  );

Step 3: Refactor Using Temp Tables or CTEs

• Stage intermediate aggregations into a temporary table (#TempAgg) or Common Table Expression (CTE) to split the workload.
• Perform a second-pass aggregation on the staged results, reducing the number of expressions needed in any single GROUP BY statement.
• Store intermediate results in tempdb, ensuring tempdb has sufficient space under C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\DATA\.

Step 4: Update Statistics and Query Plan Cache

• Run EXEC sp_updatestats; on the affected database to ensure the optimizer isn’t compounding the issue with stale cardinality estimates.
• Clear the problematic cached plan using DBCC FREEPROCCACHE (test environments only) to force recompilation after the query rewrite.

Alternative or Verification Steps

• Re-run the modified query in SSMS and confirm no Error 10706 appears in the Messages tab; also check Windows Event Viewer under Applications and Services Logs > MSSQLSERVER for recurrence.
• If rewriting is not feasible, use UNION ALL of separate GROUP BY queries as a structural workaround — this fully avoids the grouping set expression limit entirely.

Troubleshooting Guide: Error 10705 – Subqueries Not Allowed in the OUTPUT Clause

Why This Error Happens

This error occurs because SQL Server’s OUTPUT clause is designed to reference only the inserted/deleted pseudo-tables or columns directly affected by the DML operation, and it does not support correlated or standalone subqueries within its column list. Developers often attempt to pull additional lookup data (such as a join to another table) directly inside the OUTPUT clause, which violates T-SQL syntax rules. This is a query-design limitation rather than a server configuration, permissions, or corruption issue.

Step-by-Step Resolution Guide

Step 1: Identify the Offending Query

• Open the query that triggered Error 10705 in SQL Server Management Studio (SSMS) or Azure Data Studio.
• Locate the OUTPUT clause and check for nested SELECT statements, scalar subqueries, or correlated subqueries inside it.
• Example of invalid syntax to look for:

  UPDATE Orders
  SET Status = 'Shipped'
  OUTPUT inserted.OrderID, (SELECT CustomerName FROM Customers WHERE Customers.ID = inserted.CustomerID)
  WHERE OrderID = 1001;

Step 2: Refactor Using a Temporary Table or Table Variable

• Modify the query so the OUTPUT clause only references inserted/deleted columns, then join separately.
• Capture the output into a table variable or temp table first:

  DECLARE @OutputTable TABLE (OrderID INT, CustomerID INT);

  UPDATE Orders
  SET Status = 'Shipped'
  OUTPUT inserted.OrderID, inserted.CustomerID INTO @OutputTable
  WHERE OrderID = 1001;

• Perform the subquery/join logic afterward:

  SELECT o.OrderID, c.CustomerName
  FROM @OutputTable o
  JOIN Customers c ON o.CustomerID = c.ID;

Step 3: Use a CTE with MERGE for Complex Scenarios

• If the logic requires combining OUTPUT with lookup data in a single statement, wrap the DML operation in a Common Table Expression (CTE) and use MERGE.
• This allows post-processing of the output without violating the subquery restriction:

  WITH UpdatedOrders AS (
      SELECT OrderID, CustomerID FROM Orders WHERE OrderID = 1001
  )
  MERGE Orders AS target
  USING UpdatedOrders AS source
  ON target.OrderID = source.OrderID
  WHEN MATCHED THEN
      UPDATE SET Status = 'Shipped'
  OUTPUT inserted.OrderID, inserted.CustomerID;

Step 4: Validate T-SQL Compatibility Level

• Confirm the database is running a compatibility level that fully supports modern OUTPUT clause behavior (SQL Server 2025 defaults to 170, but legacy databases may be lower).
• Check via:

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

• If needed, update it:

  ALTER DATABASE YourDatabaseName SET COMPATIBILITY_LEVEL = 170;

Alternative or Verification Steps

• Re-run the modified query in SSMS and confirm it executes without raising Error 10705; check the Messages tab for a clean completion status.
• If the error persists, use SQL Server Profiler or Extended Events to capture the exact failing statement text, as the error may originate from a nested stored procedure or trigger rather than the query you initially suspected.

Why This Error Happens

Error 10704 occurs when a parameterless THROW statement is executed outside the scope of a CATCH block within a TRY...CATCH construct. SQL Server 2025 requires that a bare THROW (used to rethrow the original exception) be the first and only rethrow mechanism invoked directly inside the CATCH block—placing it inside nested stored procedures, dynamic SQL execution, or after other statements breaks the exception context. This typically results from refactored error-handling logic where the THROW call was accidentally moved outside its originating CATCH scope or into a called sub-procedure.

Step-by-Step Resolution Guide

Step 1: Locate the Offending T-SQL Block

• Open SQL Server Management Studio (SSMS) connected to your SQL Server 2025 instance.
• Run SELECT * FROM sys.messages WHERE message_id = 10704; to confirm the exact error text returned by the engine.
• Use sys.dm_exec_procedure_stats or Query Store to identify which stored procedure or batch last triggered the error.

Step 2: Correct the TRY…CATCH Structure

• Ensure the parameterless THROW; statement sits immediately inside the CATCH block, with no intervening SELECT, PRINT, or transaction statements before it.
• Example of correct syntax:

  BEGIN TRY
      -- risky operation
  END TRY
  BEGIN CATCH
      THROW;
  END CATCH

• If you need to log the error before rethrowing, capture variables (ERROR_NUMBER(), ERROR_MESSAGE(), etc.) first, then call THROW; as the final statement in the CATCH block.

Step 3: Audit Nested Procedure Calls

• Check if the THROW call was placed inside a called sub-procedure instead of the outer CATCH block — a bare THROW only works in the exact CATCH scope where the exception was caught.
• Replace any sub-procedure-level bare THROW with THROW 50000, @ErrorMessage, 1; (parameterized form) if it needs to propagate across procedure boundaries.

Step 4: Redeploy and Clear Plan Cache

• Save and redeploy the corrected stored procedure or script using sqlcmd or SSMS Object Explorer.
• Run DBCC FREEPROCCACHE; (in a non-production window) to ensure SQL Server 2025 recompiles the procedure with the updated logic rather than using a cached execution plan.

Alternative or Verification Steps

• Re-execute the original transaction that caused the failure and confirm Error 10704 no longer appears in the SQL Server Error Log (EXEC xp_readerrorlog 0, 1;) or Windows Event Viewer under Applications and Services Logs > MSSQLSERVER.
• If the error persists, replace the bare THROW entirely with a manually constructed RAISERROR statement as a temporary workaround while you audit all nested TRY...CATCH layers for scope violations.

Troubleshooting Guide: SQL Server Error 10703 – Too Many Grouping Sets

Why This Error Happens

This error occurs when a query uses GROUP BY GROUPING SETS, CUBE, or ROLLUP clauses that expand into more combinations than SQL Server’s query optimizer can process, exceeding the internal limit (typically 4,096 grouping sets). This is not a configuration or permissions issue but a query design limitation triggered when multiple grouping elements are combined or nested, causing an exponential increase in the number of generated sets. Complex queries with several CUBE or ROLLUP operations combined together are the most common cause, as the cross-product of these combinations can quickly exceed the allowed threshold.

Step-by-Step Resolution Guide

Step 1: Identify the Offending Query

• Capture the exact query text from the application or SQL Server error log that triggered Error 10703.
• Use SQL Server Management Studio (SSMS) to run the query in isolation and confirm it reproduces the error.
• Check for multiple GROUPING SETS, CUBE, or ROLLUP clauses combined in a single GROUP BY statement.

Step 2: Calculate and Reduce the Grouping Set Combinations

• Manually count how many grouping combinations your query generates; combining a CUBE with N columns produces 2^N sets, and combining multiple CUBE/ROLLUP clauses multiplies these values together.
• Break the single large query into two or more smaller queries, each handling a subset of the grouping sets.
• Use UNION ALL to combine the results of the smaller queries instead of one monolithic GROUPING SETS statement.

Step 3: Rewrite Using Explicit Grouping Sets

• Replace broad CUBE or ROLLUP operators with an explicit GROUPING SETS list that only includes the specific combinations your report actually needs.
• Example: instead of CUBE(Col1, Col2, Col3, Col4), specify GROUPING SETS ((Col1, Col2), (Col3, Col4), (Col1, Col3)).
• Remove any redundant or unused grouping combinations that are not consumed by downstream reporting tools.

Step 4: Adjust Application or Reporting Layer Logic

• If the query is auto-generated by a BI tool (e.g., Power BI, SSRS, or a third-party reporting engine), check the tool’s aggregation settings for excessive dimension combinations.
• Limit the number of dimensions selected simultaneously in pivot-style reports that dynamically generate CUBE/ROLLUP SQL.
• Update any stored procedures in C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Binn\ related scripts or custom T-SQL modules to reflect the simplified grouping logic.

Step 5: Test and Deploy the Revised Query

• Run the modified query in a non-production environment first (e.g., a test instance on Windows Server 2025).
• Monitor execution using SQL Server Profiler or Extended Events to confirm no further occurrences of Error 10703.
• Deploy the corrected stored procedure, view, or report definition to production once validated.

Alternative or Verification Steps

• Re-run the original failing query (or its replacement) in SSMS and confirm it executes successfully without raising Error 10703.
• If rewriting the query is not feasible, consider pre-aggregating data into a staging table or indexed view, then apply simpler GROUP BY logic on top of that pre-aggregated dataset to avoid large grouping set expansions entirely.

Troubleshooting Guide: SQL Server Error 10702 – “WITH CUBE and WITH ROLLUP options are not permitted with a ROLLUP”

Why This Error Happens

This error occurs when a T-SQL query mixes the legacy WITH ROLLUP or WITH CUBE syntax (placed at the end of a GROUP BY clause) with the newer ANSI standard GROUP BY ROLLUP(...) or GROUP BY CUBE(...) syntax within the same statement. SQL Server 2025’s parser treats these as mutually exclusive grammar structures because the old T-SQL extension syntax and the new ISO/ANSI grouping syntax cannot be combined logically. This is purely a query-authoring/syntax issue and is not related to permissions, services, or corrupted files.

Step-by-Step Resolution Guide

Step 1: Identify the Conflicting Query

• Open SQL Server Management Studio (SSMS) 21 or Azure Data Studio connected to your SQL Server 2025 instance.
• Locate the exact query referenced in the error message (check the Messages tab for the line number).
• Look specifically for a GROUP BY clause containing both ROLLUP(...) syntax and a trailing WITH ROLLUP or WITH CUBE keyword.

Step 2: Rewrite Using Only the Modern ANSI Syntax

• Remove the legacy WITH ROLLUP / WITH CUBE keywords entirely from the end of the GROUP BY clause.
• Replace with the standard syntax, for example:

  -- Incorrect (mixed syntax)
  SELECT Region, Product, SUM(Sales)
  FROM SalesData
  GROUP BY ROLLUP(Region, Product) WITH ROLLUP;

  -- Correct (ANSI syntax only)
  SELECT Region, Product, SUM(Sales)
  FROM SalesData
  GROUP BY ROLLUP(Region, Product);

• If you intended to use CUBE, apply the same fix: GROUP BY CUBE(Region, Product);

Step 3: Validate Compatibility Level Settings

• Run the following to check the database compatibility level, since syntax handling can vary slightly between modes:

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

• If the database is running in an older compatibility level (e.g., 100 or lower), consider updating it to at least 160 to align with SQL Server 2025 parsing behavior:

  ALTER DATABASE YourDatabaseName SET COMPATIBILITY_LEVEL = 160;

Step 4: Re-run and Parse the Query

• Use the “Parse” button in SSMS (Ctrl+F5) to validate syntax without executing the full query.
• Confirm no red squiggly underlines or IntelliSense warnings appear near the GROUP BY clause.
• Execute the corrected query against a test environment first before deploying to production.

Alternative or Verification Steps

• Verify the fix by executing the corrected query and confirming the result set includes the expected subtotal and grand total rows (NULL markers in grouped columns).
• If legacy stored procedures or reports rely heavily on WITH ROLLUP/WITH CUBE, use SQL Server Database Engine Tuning Advisor or a full-text search across your sys.sql_modules to batch-identify and refactor all occurrences:

  SELECT OBJECT_NAME(object_id), definition 
  FROM sys.sql_modules 
  WHERE definition LIKE '%WITH ROLLUP%' OR definition LIKE '%WITH CUBE%';

Troubleshooting Guide: Error 10701 — READONLY Option Invalid in EXECUTE or CREATE AGGREGATE Statement

Why This Error Happens

This error occurs when the READONLY keyword is incorrectly applied to a parameter within an EXECUTE statement or a CREATE AGGREGATE statement, contexts where SQL Server does not permit this option. Typically, this is caused by a syntax error where a developer copies parameter definitions from a stored procedure (where READONLY is valid for table-valued parameters) into an incompatible execution context. Additionally, corrupted or outdated CLR assembly metadata associated with a custom aggregate can trigger this error even when the syntax appears correct on the surface.

Step-by-Step Resolution Guide

Step 1: Review the Statement Syntax

• Open the specific T-SQL script or stored procedure call that triggered Error 10701 in SQL Server Management Studio (SSMS).
• Locate the parameter list and check for the READONLY keyword immediately following a table-valued parameter (e.g., @TVP MyTableType READONLY).
• Remove READONLY from the statement if it is being used inside an EXECUTE call; this keyword is only valid in the CREATE PROCEDURE definition, not in the execution call itself.

Step 2: Validate the CREATE AGGREGATE Definition

• If the error originates from a CREATE AGGREGATE statement, verify that you are not attempting to mark the input parameter as READONLY, as user-defined aggregates do not support this modifier on their internal parameters.
• Check the syntax against the current Microsoft documentation for SQL Server 2025, as aggregate parameter handling differs from standard stored procedures.
• Correct the syntax to: CREATE AGGREGATE [dbo].[AggregateName] (@Value [int]) RETURNS [int] EXTERNAL NAME [AssemblyName].[ClassName].

Step 3: Rebuild and Redeploy the CLR Assembly

• If the syntax appears correct but the error persists, the issue may lie in a stale CLR assembly cache.
• Navigate to the assembly directory, typically located at C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Binn\, to verify the DLL version timestamp.
• Drop the existing assembly using DROP AGGREGATE [AggregateName]; followed by DROP ASSEMBLY [AssemblyName];.
• Re-run the CREATE ASSEMBLY command pointing to the updated DLL path, then recreate the aggregate without the READONLY modifier.

Step 4: Restart the SQL Server Service

• Open the Services console (services.msc) on your Windows Server 2025 or Windows 11 machine.
• Locate SQL Server (MSSQLSERVER) or the specific named instance.
• Right-click and select Restart to clear any cached execution plans that might be holding the invalid syntax reference.

Alternative or Verification Steps

• To verify the fix, execute the corrected script in a new query window in SSMS; the batch should complete successfully without raising Error 10701 or output “Command(s) completed successfully.”
• If the error persists specifically with CREATE AGGREGATE, try dropping and fully recreating the aggregate from scratch using a minimal, boilerplate syntax template to rule out hidden corruption in the original script file.

Troubleshooting Guide: Error 10700 – Table-Valued Parameter Is READONLY

Why This Error Happens

This error occurs because table-valued parameters (TVPs) in SQL Server are READONLY by design, and any attempt to perform INSERT, UPDATE, or DELETE operations directly against the TVP inside a stored procedure or function will trigger this failure. This typically happens when a developer writes T-SQL logic that tries to modify the TVP data directly instead of copying it into a temporary table or table variable first. It can also surface after a code migration or upgrade to SQL Server 2025 where legacy procedures were not updated to respect the READONLY constraint enforced on TVP parameters.

Step-by-Step Resolution Guide

Step 1: Identify the Offending Stored Procedure or Query

• Review the full error message to locate the exact TVP name referenced (the %.*ls placeholder will be replaced with the actual parameter name in the real error output).
• Open SQL Server Management Studio (SSMS) and use sp_helptext 'YourProcedureName' to review the procedure definition.
• Search the code for any INSERT INTO @TVPName, UPDATE @TVPName, or DELETE FROM @TVPName statements.

Step 2: Copy TVP Data into a Temporary Table or Table Variable

• Instead of modifying the TVP directly, declare a local temporary table (#TempTable) or a table variable (DECLARE @LocalTable TABLE (...)) with a matching schema.
• Use SELECT * INTO #TempTable FROM @TVPName; to copy the data out of the READONLY parameter.
• Perform all required INSERT, UPDATE, or DELETE operations against #TempTable instead of the original TVP.

Step 3: Update Application-Side Code (If Applicable)

• If the TVP is populated via ADO.NET, Entity Framework, or ODBC from a C# or .NET application, verify that the SqlParameter.SqlDbType is set to Structured and that no client-side logic attempts to reuse the same DataTable object for write-back operations after passing it to SQL Server.
• Ensure the application is only using the TVP for passing data into SQL Server, not expecting SQL Server to return modified data through the same parameter.

Step 4: Redeploy and Test the Corrected Procedure

• Save the updated stored procedure script and deploy it to your SQL Server 2025 instance using SSMS or sqlcmd.
• Run EXEC sp_recompile 'YourProcedureName'; to clear any cached execution plans tied to the old procedure logic.
• Re-run the original failing query or application workflow to confirm the error no longer appears.

Alternative or Verification Steps

• Verify the fix by executing the stored procedure manually in SSMS with a sample TVP input and confirming no Error 10700 is raised in the Messages tab.
• If the issue persists, check for nested procedure calls where the TVP is passed further down the call stack to another procedure that also attempts to modify it directly—this requires applying the same temp table fix at that deeper level.

Troubleshooting Guide: SQL Server Error 10683

Why This Error Happens

Error 10683 occurs when SQL Server attempts to create an object—such as a constraint, index, or statistic—that already exists on the target table under the same name. This typically happens due to duplicate or conflicting T-SQL deployment scripts being executed more than once, residual metadata left behind from an incomplete rollback, or schema migration tools (such as DACPAC deployments or ORM migrations) attempting to reapply changes without checking for existing objects. In some cases, it also surfaces when two different sessions or automated jobs try to provision the same schema object concurrently.

Step-by-Step Resolution Guide

Step 1: Identify the Conflicting Object

• Open SQL Server Management Studio (SSMS) and connect to the affected instance.
• Run the following query to locate the existing object by name:

  SELECT name, object_id, type_desc 
  FROM sys.objects 
  WHERE name = 'YourObjectName';

• Confirm whether the object belongs to the table referenced in the error message.

Step 2: Drop or Rename the Existing Object (If Safe)

• If the object is a leftover from a failed migration, drop it safely:

  IF OBJECT_ID('dbo.YourObjectName', 'U') IS NOT NULL
      DROP INDEX YourObjectName ON dbo.YourTable;

• For constraints, use ALTER TABLE ... DROP CONSTRAINT instead.
• Always back up the database or take a snapshot before dropping objects in production.

Step 3: Update Deployment Scripts to Use Existence Checks

• Modify your T-SQL or migration scripts to include conditional guards, for example:

  IF NOT EXISTS (SELECT * FROM sys.indexes WHERE name = 'IX_YourIndex')
  BEGIN
      CREATE INDEX IX_YourIndex ON dbo.YourTable(ColumnName);
  END

• If using Entity Framework, dbForge, or SSDT/DACPAC, regenerate the migration script to avoid duplicate CREATE statements.

Step 4: Check for Concurrent Job Execution

• Open SQL Server Agent on Windows Server 2025 and review any overlapping jobs under SQL Server Agent > Jobs.
• Stagger scheduled deployment or ETL jobs that may be racing to create the same schema object simultaneously.

Alternative or Verification Steps

• Re-run the deployment script or application startup process and confirm Error 10683 no longer appears in the SQL Server error log (Management > SQL Server Logs in SSMS).
• If the issue persists, restore a clean copy of the database schema from a known-good backup and reapply migrations sequentially rather than in parallel.

Troubleshooting Guide: Error 10682 in Microsoft SQL Server 2025

Why This Error Happens

Error 10682 occurs when you attempt to use the DROP_EXISTING option to convert a clustered or nonclustered index into a different index type (such as converting a clustered index to a spatial, XML, or columnstore index) that does not support this conversion path. This typically happens because the target index type has structural requirements—such as specific key column data types or a required clustered index base—that are incompatible with a direct DROP_EXISTING rebuild. The underlying cause is a mismatch between the source relational index’s metadata and the destination index’s storage engine requirements.

Step-by-Step Resolution Guide

Step 1: Identify the Conflicting Index Definition

• Run sp_helpindex 'YourTableName' or query sys.indexes to review the current index structure.
• Check if the index you are trying to create (e.g., Spatial, XML, or Columnstore) is being incorrectly chained with DROP_EXISTING against an incompatible relational index.
• Note the exact index name and type flagged in the error message (%S_MSG).

Step 2: Drop the Index Manually Instead of Using DROP_EXISTING

• Rather than using CREATE INDEX ... WITH (DROP_EXISTING = ON), explicitly drop the old index first:

  DROP INDEX IX_YourIndexName ON dbo.YourTableName;

• Once removed, create the new index type separately using a standard CREATE INDEX statement without the DROP_EXISTING clause.

Step 3: Verify Index Type Compatibility

• Confirm that the data types of the key columns support the new index type (e.g., geography or geometry data types are required for Spatial indexes).
• If converting to a Columnstore index, ensure the table meets the requirements for Clustered Columnstore structures in SQL Server 2025, as these cannot simply overwrite a standard B-tree index via DROP_EXISTING.
• Update your T-SQL scripts or SSMS Index Wizard configurations to reflect the two-step (Drop, then Create) process for these specific index type changes.

Step 4: Rebuild Statistics and Validate Schema

• After creating the new index, update statistics to ensure the Query Optimizer recognizes the new structure:

  UPDATE STATISTICS dbo.YourTableName;

• Check the Windows Server 2025 Event Viewer (Application Log) to ensure no residual SQL Server engine errors (MSSQLSERVER source) persist regarding the schema change.

Alternative or Verification Steps

• Verification: Re-run the original query or deployment script that triggered Error 10682. If the index now builds successfully, query sys.indexes again to confirm the type_desc column matches your intended index type (e.g., CLUSTERED COLUMNSTORE or SPATIAL).
• Alternative: If the two-step Drop/Create process fails due to dependencies (such as Foreign Keys), use SQL Server Management Studio (SSMS) to script the table dependencies, drop the constraints temporarily, perform the index conversion, and then re-apply the constraints.

Troubleshooting Guide: SQL Server Error 10681 – Row Size Exceeds Maximum Allowable Limit

Why This Error Happens

This error occurs because SQL Server enforces an 8,060-byte limit for a single row within a standard data page (8 KB page size minus header overhead), and the table schema you’re attempting to populate or alter exceeds this threshold through the combination of fixed-length and variable-length columns. This typically happens during bulk inserts, index rebuilds, or ALTER TABLE operations where the cumulative byte size of all columns—especially multiple VARCHAR(MAX), NVARCHAR, or CHAR columns—surpasses the page boundary. Because the operation was flagged as RESUMABLE, SQL Server halts and demands an explicit retry without resumability, since resumable DDL operations cannot process this specific overflow condition mid-transaction.

Step-by-Step Resolution Guide

Step 1: Identify the Offending Table and Column Sizes

• Run sp_help 'YourTableName' in SQL Server Management Studio (SSMS) to list all column data types and their byte lengths.
• Sum the max byte sizes of all fixed-length columns (INT, CHAR, DATETIME, etc.) to see how close you are to the 8,060-byte ceiling.
• Pay special attention to multiple large VARCHAR, NVARCHAR, or CHAR columns defined with high fixed lengths (e.g., VARCHAR(4000) x 3).

Step 2: Retry the Operation with RESUMABLE = OFF

• Locate the failing script, typically an ALTER TABLE or bulk load command.
• Modify the command syntax to explicitly disable resumable mode:
• ALTER TABLE YourTableName REBUILD WITH (RESUMABLE = OFF);
• Execute the command again in a new query window under a low-traffic maintenance period, as this operation may lock the table briefly.

Step 3: Redesign the Schema to Use Row-Overflow Storage

• Convert large fixed-length CHAR or VARCHAR(n) columns to VARCHAR(MAX) or NVARCHAR(MAX) where possible, since SQL Server automatically pushes overflow data off-row (into LOB pages) rather than counting the full length against the 8,060-byte row limit.
• Navigate to Table Properties in SSMS Object Explorer, right-click the table, select “Design,” and update data types for oversized columns.
• Alternatively, run a manual script: ALTER TABLE YourTableName ALTER COLUMN ColumnName VARCHAR(MAX);

Step 4: Enable Large Value Types Out of Row (If Using MAX Types Already)

• If the table already uses VARCHAR(MAX)/NVARCHAR(MAX) but still throws error 10681, check the large value types out of row table option.
• Run EXEC sp_tableoption 'YourTableName', 'large value types out of row', '1'; to force overflow columns off the primary data page.
• Rebuild the table’s clustered index afterward to apply the storage change: ALTER INDEX ALL ON YourTableName REBUILD;

Step 5: Split the Table Vertically (If Redesign Isn’t Feasible)

• If business logic prevents changing data types, split the wide table into two related tables using a one-to-one relationship via a shared primary key.
• Move less frequently accessed or oversized columns into the secondary table to bring the primary table under the 8,060-byte threshold.
• Update application queries and stored procedures to JOIN the two tables as needed.

Alternative or Verification Steps

• Verify the fix by re-running the original failing INSERT, ALTER, or index rebuild statement; if it completes without error 10681, check sp_spaceused 'YourTableName' to confirm the row size now fits within page constraints.
• If the error persists after schema changes, use DBCC CHECKTABLE('YourTableName') WITH ALL_ERRORMSGS to rule out underlying page corruption that might be inflating effective row size calculations.

Troubleshooting Guide: Error 10680 – “ALTER INDEX ALL … There is no pending resumable index operation”

Why This Error Happens

This error occurs when SQL Server 2025 attempts to RESUME, PAUSE, or ABORT a resumable index operation that does not actually exist in a paused or pending state for the specified index or table. This typically happens because the original resumable index build/rebuild completed successfully, was already aborted, or the operation’s internal state in sys.index_resumable_operations was cleared due to a server restart, failover, or manual cleanup. It can also occur if a script attempts to resume an index operation on the wrong database context or if the index name/table reference in the ALTER INDEX ALL statement doesn’t match an index that genuinely has a suspended build in progress.

Step-by-Step Resolution Guide

Step 1: Verify the Current State of Resumable Index Operations

• Query the system view to confirm whether a resumable operation actually exists:

  SELECT * FROM sys.index_resumable_operations;

• Check the state_desc column — valid pending states are typically PAUSED or RUNNING. If no rows return for your target table/index, the operation has already completed or was never properly initiated.

Step 2: Confirm You Are in the Correct Database Context

• Run SELECT DB_NAME(); to ensure you’re connected to the intended database before executing ALTER INDEX ALL.
• Resumable operation states are database-scoped, so running the command against the wrong database will always trigger Error 10680.
• Re-issue the command only after confirming context with USE [YourDatabaseName];.

Step 3: Remove Stale or Orphaned Resume Attempts

• If the index was already rebuilt or aborted in a prior session, do not use RESUME. Instead, issue a fresh rebuild:

  ALTER INDEX ALL ON [Schema].[TableName] REBUILD;

• If you specifically need to clear a hung reference, explicitly abort it first (only if a row exists in Step 1):

  ALTER INDEX [IndexName] ON [Schema].[TableName] ABORT;

Step 4: Check for Failover or Restart Interference (AlwaysOn/Clustered Environments)

• On Windows Server 2025 failover clusters, review the SQL Server Error Log located at C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG for unexpected restarts during the index operation window.
• If a failover occurred mid-operation, the resumable state metadata may have been dropped; a full rebuild is required instead of a resume.

Alternative or Verification Steps

• After rebuilding, re-run SELECT * FROM sys.index_resumable_operations WHERE object_id = OBJECT_ID('Schema.TableName'); — it should return zero rows if the index operation completed successfully and cleanly.
• As an alternative, use SQL Server Management Studio (SSMS) 21’s “Index Properties” GUI on the target table to visually confirm no resumable rebuild is flagged as in-progress before scripting further ALTER INDEX commands.

Troubleshooting Guide: Error 10679 – Online Columnstore Index Not Supported with ORDER Clause

Why This Error Happens

This error occurs because SQL Server 2025 does not permit creating or rebuilding a columnstore index with the ORDER clause while the ONLINE = ON option is also specified. The ORDER clause (used to define key-ordered columnstore indexes for improved segment elimination) requires an offline build process because it needs exclusive control over row-group data layout. When a Transact-SQL script explicitly combines ONLINE = ON with ORDER (column_list) in the same CREATE INDEX or ALTER INDEX statement, the Query Optimizer rejects the operation and raises Error 10679.

Step-by-Step Resolution Guide

Step 1: Identify the Conflicting Index Statement

• Open the T-SQL script or stored procedure that triggered the error.
• Locate the CREATE COLUMNSTORE INDEX or ALTER INDEX ... REBUILD statement.
• Check the WITH clause for simultaneous use of ONLINE = ON and ORDER (column_name).

Step 2: Remove or Modify the ONLINE Option

• Edit the statement to set ONLINE = OFF if you need to retain the ORDER clause for key-ordered columnstore benefits.
• Example corrected syntax:

  ALTER INDEX CCI_SalesData ON dbo.SalesFact
  REBUILD WITH (ONLINE = OFF, ORDER (OrderDate, CustomerID));

• Schedule this operation during a maintenance window, since offline rebuilds lock the table.

Step 3: Alternatively, Remove the ORDER Clause

• If online availability is a higher priority than key-ordering, drop the ORDER clause instead.
• Example:

  ALTER INDEX CCI_SalesData ON dbo.SalesFact
  REBUILD WITH (ONLINE = ON);

• This allows the rebuild to proceed online but without the optimized row-group ordering.

Step 4: Validate Resource Governor and Compatibility Level

• Confirm the database compatibility level is set to 170 (SQL Server 2025) using:

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

• Ensure no legacy scripts or ORM-generated migrations (Entity Framework, SSDT) are auto-injecting incompatible ONLINE/ORDER combinations during deployment.

Step 5: Re-run the Index Operation

• Execute the corrected script in SQL Server Management Studio (SSMS) 21 or Azure Data Studio.
• Monitor the Messages tab to confirm the operation completes without Error 10679.

Alternative or Verification Steps

• Verify the index was created successfully by querying sys.indexes and sys.index_columns to confirm the ORDER metadata (is_ordered column) matches your intended configuration.
• If online rebuilds are mandatory for uptime (e.g., OLTP production environments), consider partitioning the table and rebuilding partitions individually offline during low-traffic periods instead of using ORDER on the entire index.

Troubleshooting Guide: SQL Server Error 10678 – Computed Column Not Supported in Columnstore Index ORDER List

Why This Error Happens

This error occurs because SQL Server 2025 does not allow computed columns to be explicitly included in the ORDER clause when creating an ordered clustered or nonclustered columnstore index. The ORDER list requires physically stored, deterministic column values to properly sort and segment data during index build, and computed columns are evaluated at query time rather than being physically persisted (unless explicitly marked as PERSISTED). As a result, the Storage Engine rejects the DDL statement outright since it cannot guarantee a stable sort order on a value that isn’t materialized on disk.

Step-by-Step Resolution Guide

Step 1: Identify the Offending Computed Column

• Run sp_help 'YourTableName' or query sys.computed_columns to confirm which column referenced in your CREATE COLUMNSTORE INDEX ... ORDER (...) statement is computed.
• Check the column definition using: SELECT name, definition, is_persisted FROM sys.computed_columns WHERE object_id = OBJECT_ID('YourTableName');
• Note whether the is_persisted flag is currently set to 0 (False).

Step 2: Persist the Computed Column (If Logically Possible)

• If the computation is deterministic (e.g., does not rely on non-deterministic functions like GETDATE() or NEWID()), alter the table to mark the column as PERSISTED.
• Execute: ALTER TABLE YourTableName ALTER COLUMN YourColumnName AS (Expression) PERSISTED;
• Note: Even if persisted, SQL Server 2025 may still restrict this specific column from the ORDER list depending on the underlying base columns; proceed to Step 3 if the error persists.

Step 3: Remove the Computed Column from the ORDER Clause

• Rewrite the CREATE INDEX T-SQL script to exclude the computed column from the parenthetical ORDER (...) list.
• Instead, order the index strictly by the underlying base (physical) columns that the computed column relies on.
• Example fix: Change ORDER (ComputedCol, Col2) to ORDER (BaseCol1, Col2).

Step 4: Rebuild the Columnstore Index

• Drop any partially created or failed index artifacts using DROP INDEX IF EXISTS IX_YourIndexName ON YourTableName;.
• Re-run the corrected CREATE CLUSTERED COLUMNSTORE INDEX or CREATE NONCLUSTERED COLUMNSTORE INDEX script via SQL Server Management Studio (SSMS) 21 or Azure Data Studio.
• Monitor the “Messages” tab for Event ID 10678 to ensure it no longer triggers.

Alternative or Verification Steps

• Verification: After the index is created successfully, run SELECT * FROM sys.indexes WHERE name = 'IX_YourIndexName'; to confirm the type_desc shows CLUSTERED COLUMNSTORE or NONCLUSTERED COLUMNSTORE with a status of “Active.”
• Alternative: If the computed column value is required for sorting logic, consider creating a standard (non-computed) persisted integer/date column that is updated via a trigger or application logic, then use that physical column in the ORDER list instead.

Troubleshooting Guide: Error 10677 – Index Rebuilt with Null Stats

Why This Error Happens

Error 10677 occurs when SQL Server 2025 successfully rebuilds an index and populates it with valid data rows, but the automatic statistics update process fails to generate or persist the corresponding statistics blob. This typically happens due to corruption in the statistics metadata (sys.sysindexes or sys.stats), insufficient memory during the stats computation phase of the rebuild, or a race condition when AUTO_UPDATE_STATISTICS runs concurrently with the index rebuild operation. In some cases, this is also triggered by underlying corruption in the system catalog views on Windows Server 2025 instances where disk I/O was interrupted mid-operation.

Step-by-Step Resolution Guide

Step 1: Run a Database Consistency Check

• Open SQL Server Management Studio (SSMS) or sqlcmd and connect to the affected instance.
• Execute DBCC CHECKDB ('YourDatabaseName') WITH NO_INFOMSGS, ALL_ERRORMSGS; to confirm whether corruption extends beyond statistics.
• Review output for any Msg 8928 or Msg 2576 errors that indicate deeper page-level corruption alongside the stats issue.

Step 2: Manually Rebuild Statistics for the Affected Object

• Identify the exact index and table referenced in the error message (the %.*ls placeholders will show real names in the SQL Server Error Log at C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG).
• Run UPDATE STATISTICS [SchemaName].[TableName] ([IndexName]) WITH FULLSCAN; to force a complete recalculation rather than relying on the sampled rebuild.
• If the command fails or hangs, drop and recreate the statistics object explicitly using DROP STATISTICS followed by CREATE STATISTICS ... WITH FULLSCAN.

Step 3: Rebuild the Index with Statistics Disabled, Then Re-enable

• Temporarily disable auto-stats on the table to avoid the race condition: ALTER DATABASE [YourDatabaseName] SET AUTO_UPDATE_STATISTICS OFF;
• Rebuild the index again using ALTER INDEX [IndexName] ON [SchemaName].[TableName] REBUILD WITH (ONLINE = ON, SORT_IN_TEMPDB = ON);
• Re-enable auto-stats with ALTER DATABASE [YourDatabaseName] SET AUTO_UPDATE_STATISTICS ON; once the rebuild completes cleanly.

Step 4: Check TempDB and Memory Allocation

• Verify TempDB has sufficient free space on disk, as stats generation during large rebuilds spills to TempDB (C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Data\).
• In SSMS, check Server Properties > Memory to ensure “Maximum Server Memory” isn’t configured too restrictively, which can cause stats computation to abort silently under Windows Server 2025 memory pressure.

Alternative or Verification Steps

• After applying fixes, run DBCC SHOW_STATISTICS ('TableName', 'IndexName'); to confirm the statistics histogram and header now populate with valid row/step data instead of returning null.
• If the error persists, restore the affected table/filegroup from the last known-good backup and re-apply transaction logs, as this indicates physical corruption beyond what statistics rebuilding can resolve.

Troubleshooting Guide: Error 10676 – Resumable Index Operation Auto-Aborted

Why This Error Happens

This error occurs when a resumable index operation (CREATE, REBUILD, or ALTER INDEX with RESUME capability) is paused and remains inactive longer than the threshold defined by the PAUSED_RESUMABLE_INDEX_ABORT_DURATION_MINUTES database-scoped configuration. SQL Server automatically aborts the operation to reclaim system resources, release locks, and prevent indefinite consumption of version store space in tempdb. This is typically caused by an administrator pausing an index operation and forgetting to resume it, or by a maintenance window/script failing to resume the operation within the expected timeframe.

Step-by-Step Resolution Guide

Step 1: Identify the Affected Index and Database

• Query sys.index_resumable_operations to find the paused operation, its state, and last pause time.
• Run the following T-SQL:

  SELECT name, object_id, database_id, state_desc, percent_complete, start_time, last_pause_time
  FROM sys.index_resumable_operations;

• Note the database name and the PAUSED_RESUMABLE_INDEX_ABORT_DURATION_MINUTES currently configured.

Step 2: Check and Adjust the Abort Duration Setting

• Verify the current threshold value using:

  SELECT name, value
  FROM sys.database_scoped_configurations
  WHERE name = 'PAUSED_RESUMABLE_INDEX_ABORT_DURATION_MINUTES';

• If maintenance windows regularly exceed the default, increase the threshold:

  ALTER DATABASE SCOPED CONFIGURATION
  SET PAUSED_RESUMABLE_INDEX_ABORT_DURATION_MINUTES = 240;

• Avoid setting this value too high, as it can prolong tempdb version store bloat.

Step 3: Resume or Abort the Index Operation Manually

• If the operation is still relevant, resume it before the timeout expires:

  ALTER INDEX [YourIndexName] ON [YourSchema].[YourTable] RESUME;

• If the operation is no longer needed, abort it cleanly to avoid resource locking:

  ALTER INDEX [YourIndexName] ON [YourSchema].[YourTable] ABORT;

• Restart the index build from scratch if required.

Step 4: Review SQL Server Agent Jobs and Maintenance Scripts

• Check any automated maintenance plans or scripts (via SQL Server Agent or Windows Task Scheduler on Windows Server 2025) that pause/resume index operations.
• Ensure these jobs have proper error handling and do not leave operations paused indefinitely.
• Validate job history in SSMS under SQL Server Agent > Jobs > View History.

Alternative or Verification Steps

• To verify the fix, re-run the query against sys.index_resumable_operations and confirm the operation no longer appears or shows a completed/aborted state.
• If the issue recurs frequently, consider disabling resumable index pausing for non-critical maintenance windows and instead schedule rebuilds during low-traffic periods to avoid manual pause/resume dependency.

Troubleshooting Guide: Error 10675 – RESUMABLE Option Not Supported for Index Build

Why This Error Happens

This error occurs because the RESUMABLE = ON option is being applied to an index operation type that does not support resumability in SQL Server 2025, such as building indexes on system tables, memory-optimized tables, columnstore indexes with certain configurations, or indexes containing specific data types (like XML, spatial, or certain LOB types) that are incompatible with the resumable architecture. The query optimizer detects this conflict during plan compilation and throws an internal error rather than silently ignoring the unsupported option. This is by design, not database corruption, and typically surfaces during maintenance scripts, automated index rebuild jobs, or migration scripts carried over from configurations that previously allowed broader RESUMABLE support.

Step-by-Step Resolution Guide

Step 1: Identify the Offending Statement and Target Object

• Capture the exact T-SQL statement from the SQL Server Error Log or Extended Events session that triggered Error 10675.
• Check the target table/index definition using sp_help 'YourTableName' or query sys.indexes and sys.columns to identify data types like xml, geography, geometry, varchar(max) with certain collations, or image/text/ntext.
• Confirm whether the table is memory-optimized by checking sys.tables for is_memory_optimized = 1.

Step 2: Remove or Conditionally Disable the RESUMABLE Option

• Edit the script to remove WITH (RESUMABLE = ON) from the CREATE INDEX, ALTER INDEX ... REBUILD, or CREATE INDEX ... ALTER statement.
• If the script is dynamically generated (common in maintenance plans like Ola Hallengren’s scripts), update the stored procedure parameter (e.g., @Resumable = 'N') at the call site.
• For maintenance plans configured via SSMS GUI, open the Index Rebuild Task, go to the “Options” tab, and uncheck “Enable resumable index operations.”

Step 3: Validate Index Type Compatibility Before Rebuilding

• Run the following query to check if the index is a columnstore or contains unsupported types:

SELECT i.name, i.type_desc, c.name AS column_name, ty.name AS data_type
FROM sys.indexes i
JOIN sys.index_columns ic ON i.object_id = ic.object_id AND i.index_id = ic.index_id
JOIN sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id
JOIN sys.types ty ON c.user_type_id = ty.user_type_id
WHERE i.object_id = OBJECT_ID('YourTableName');

• If LOB or spatial types are present, you must perform a standard (non-resumable) rebuild instead.

Step 4: Re-run the Statement Without RESUMABLE

• Execute the corrected statement, for example:

ALTER INDEX IX_YourIndex ON dbo.YourTable REBUILD WITH (ONLINE = ON, MAXDOP = 4);

• Monitor execution in SSMS Activity Monitor or via sys.dm_exec_requests to ensure the operation completes without reverting to the error.

Alternative or Verification Steps

• Verify the fix by querying sys.index_resumable_operations after running a rebuild on a supported index elsewhere in the same database—confirming the RESUMABLE feature itself still functions correctly outside this specific object.
• If you require resumability for compliance/downtime reasons, consider restructuring the table to remove unsupported column types into a separate linked table, allowing the primary large table to use RESUMABLE = ON successfully.

Troubleshooting Guide: SQL Server Error 10674

Why This Error Happens

This error occurs because SQL Server 2025 does not allow a table to simultaneously support a traditional clustered (B-tree) index and a nonclustered columnstore index when an unsupported %S_MSG option (such as certain ONLINE, SORT_IN_TEMPDB, or DATA_COMPRESSION combinations) is specified in the CREATE INDEX statement. The storage engine treats the nonclustered columnstore index as a distinct physical structure, and certain clustered index creation options are incompatible with the columnstore’s segment-based storage format. This typically happens during index maintenance scripts or migrations where options valid for standard rowstore indexes are reused without modification for columnstore-enabled tables.

Step-by-Step Resolution Guide

Step 1: Identify the Conflicting Index Option

• Open SQL Server Management Studio (SSMS) and review the exact CREATE CLUSTERED INDEX statement that triggered Error 10674.
• Check the error message for the specific %S_MSG placeholder value (e.g., ONLINE = ON, DROP_EXISTING, or SORT_IN_TEMPDB) that is unsupported.
• Cross-reference the statement against Microsoft’s documentation for “Columnstore Index Limitations” in SQL Server 2025.

Step 2: Remove or Modify the Unsupported Option

• Edit the T-SQL script to strip out the incompatible option from the WITH (...) clause.
• Example fix:

CREATE CLUSTERED INDEX IX_Clustered
ON dbo.YourTable (ColumnName)
WITH (DROP_EXISTING = ON); -- Remove ONLINE or SORT_IN_TEMPDB if causing conflict

• If the option is required for performance reasons, consider rebuilding the index in two separate steps instead of a single combined statement.

Step 3: Drop and Recreate the Nonclustered Columnstore Index Separately

• Drop the existing nonclustered columnstore index using DROP INDEX [IndexName] ON [SchemaName].[TableName];.
• Create the clustered index first using the corrected syntax from Step 2.
• Recreate the nonclustered columnstore index afterward using a standalone CREATE NONCLUSTERED COLUMNSTORE INDEX statement without conflicting options.

Step 4: Validate Compatibility Level and Database Settings

• Confirm the database compatibility level is set to 170 (SQL Server 2025) by running:

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

• Ensure ALTER DATABASE SCOPED CONFIGURATION settings do not force legacy cardinality estimation or indexing behaviors that might reintroduce option conflicts.

Alternative or Verification Steps

• Run DBCC CHECKTABLE('YourTableName') after index creation to confirm there is no structural corruption and that both indexes coexist correctly.
• If the error persists, use sp_helpindex 'YourTableName' to list all current indexes and verify no duplicate or overlapping columnstore/clustered definitions remain on the table.

Troubleshooting Guide: Error 10673 – Cannot Resume Index Creation Due to Incorrect Persisted State

Why This Error Happens

Error 10673 occurs when SQL Server 2025 attempts to resume a paused online index creation or rebuild operation, but the persisted state metadata in the RESUMABLE_INDEX internal tracking structures does not match the current state of the index or the underlying table schema. This typically happens when the table schema was altered while the index operation was paused, the temporary statistics or state information became corrupted due to an unexpected service interruption, or the index build was paused across incompatible SQL Server builds or patch versions. Because the resume operation relies on consistent persisted checkpoints, any mismatch forces SQL Server to reject the resume request and demand a full abort instead of risking a corrupted index.

Step-by-Step Resolution Guide

Step 1: Abort the Resumable Index Operation

• Connect to the affected instance via SQL Server Management Studio (SSMS) or sqlcmd.
• Run the following command to safely terminate the stuck operation:

ALTER INDEX [YourIndexName] ON [Schema].[TableName] ABORT;

• Confirm the operation is cleared by querying sys.index_resumable_operations to ensure no residual entries remain for that index.

Step 2: Validate Schema and Table Consistency

• Check whether any ALTER TABLE or ALTER INDEX DDL statements were executed on the table while the index build was paused.
• Run DBCC CHECKTABLE('TableName') WITH NO_INFOMSGS, ALL_ERRORMSGS; to confirm there is no underlying page-level or allocation corruption contributing to the mismatch.
• If corruption is found, address it using standard DBCC CHECKDB repair procedures before proceeding.

Step 3: Clear Cached Plan and Temporary State Data

• Clear the procedure cache for the affected database to eliminate any stale execution plans referencing the broken resumable state:

DBCC FREEPROCCACHE;

• Restart the SQL Server service via Windows Server 2025 Services Manager (services.msc) or PowerShell:

Restart-Service -Name 'MSSQLSERVER' -Force

• This ensures in-memory resumable index metadata is fully flushed rather than relying on a stale cached state.

Step 4: Recreate the Index From Scratch

• Once the operation is aborted and the cache is cleared, rebuild the index as a fresh, non-resumed operation:

CREATE INDEX [YourIndexName] ON [Schema].[TableName] ([ColumnName])
WITH (ONLINE = ON, RESUMABLE = ON, MAXDOP = 4);

• Monitor progress using sys.dm_exec_requests and percent_complete to confirm the build is progressing normally without state errors.

Alternative or Verification Steps

• Verify the fix by re-running SELECT * FROM sys.index_resumable_operations; — the table should show the index state as either actively running or completed, with no “paused” entries referencing corrupted state.
• If the error persists after a clean abort and rebuild, check for mixed SQL Server patch levels in an Always On Availability Group or cluster setup, as resumable index state is not guaranteed to be compatible across mismatched Cumulative Update (CU) versions — apply the latest CU uniformly across all nodes and retry.

Troubleshooting Guide: SQL Server Error 10672 — Unsupported Option for Clustered Index on Filtered Index

Why This Error Happens

This error occurs because SQL Server does not allow certain index-creation options (such as IGNORE_DUP_KEY, ONLINE, or other incompatible flags represented by %S_MSG) to be applied when building a clustered index on a table that already contains a filtered index. Filtered indexes rely on a specific predicate and metadata structure that conflicts with the rebuild/sort behavior triggered by the unsupported option. This typically happens when a DBA or an automated deployment script (e.g., a schema migration tool) attempts to recreate or convert an index without first checking for existing filtered indexes on the target table.

Step-by-Step Resolution Guide

Step 1: Identify the Conflicting Filtered Index

• Open SQL Server Management Studio (SSMS) and connect to the instance running SQL Server 2025.
• Run the following query to locate filtered indexes on the target table:

  SELECT name, filter_definition 
  FROM sys.indexes 
  WHERE object_id = OBJECT_ID('YourSchema.YourTable') 
  AND filter_definition IS NOT NULL;

• Note the index name(s) returned, as these must be addressed before proceeding.

Step 2: Drop or Temporarily Disable the Filtered Index

• Script out the filtered index definition first so it can be recreated later:

  SELECT OBJECT_DEFINITION(object_id) FROM sys.indexes WHERE name = 'YourFilteredIndexName';

• Drop the filtered index using:

  DROP INDEX YourFilteredIndexName ON YourSchema.YourTable;

• Avoid using DISABLE on the index, as disabled filtered indexes can still trigger Error 10672 in some SQL Server 2025 builds.

Step 3: Recreate the Clustered Index Without the Unsupported Option

• Remove the unsupported flag (e.g., IGNORE_DUP_KEY = ON or ONLINE = ON, depending on which triggered %S_MSG) from your CREATE CLUSTERED INDEX statement.
• Example corrected syntax:

  CREATE CLUSTERED INDEX IX_YourTable_Clustered
  ON YourSchema.YourTable (YourColumn);

• Execute the statement and confirm it completes without Error 10672.

Step 4: Recreate the Filtered Index

• Once the clustered index is successfully built, recreate the filtered index using the definition captured in Step 2:

  CREATE NONCLUSTERED INDEX YourFilteredIndexName
  ON YourSchema.YourTable (YourColumn)
  WHERE YourFilterColumn = 'SomeValue';

• Confirm there are no dependent constraints or statistics objects that reference the dropped index before finalizing.

Step 5: Update Deployment Scripts

• If this table is managed via DACPAC, SSDT, or a CI/CD pipeline, update the deployment script to drop filtered indexes before clustered index operations and recreate them afterward.
• Add a pre-deployment check using sys.indexes to prevent future recurrences.

Alternative or Verification Steps

• Verify the fix by re-running sp_helpindex 'YourSchema.YourTable' to confirm both the clustered index and filtered index exist without errors.
• If dropping the filtered index is not feasible (e.g., due to application dependencies), consider creating the clustered index on a staging table first, then switching the filtered index logic to a indexed view as an alternative design.

Troubleshooting Guide: SQL Server Error 10671 — Filtered Index Option Not Supported

Why This Error Happens

Error 10671 occurs when a CREATE INDEX statement includes a WHERE clause (filtered index) combined with an index option that SQL Server does not permit for filtered indexes, such as IGNORE_DUP_KEY, ONLINE in certain legacy modes, or specific compatibility-level restricted options. This typically happens because the T-SQL script was written for a general index and later modified to include a filter predicate without removing incompatible options. It can also surface after a database compatibility level change (e.g., migrating to SQL Server 2025) where previously ignored options are now strictly validated.

Step-by-Step Resolution Guide

Step 1: Identify the Offending CREATE INDEX Statement

• Locate the exact script or stored procedure generating the error by checking SQL Server Management Studio (SSMS) output or the SQL Server error log at C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG.
• Run the query manually in SSMS to confirm the error reproduces with the exact %S_MSG option named in the message.

Step 2: Remove or Replace the Unsupported Option

• Open the script and locate the WITH (...) clause attached to the CREATE INDEX statement.
• Remove unsupported options like IGNORE_DUP_KEY = ON, as filtered indexes do not support duplicate key ignoring.
• Example corrected syntax:

CREATE NONCLUSTERED INDEX IX_FilteredExample
ON dbo.Orders(OrderStatus)
WHERE OrderStatus = 'Pending'
WITH (DROP_EXISTING = ON, ONLINE = ON);

• Cross-reference supported options in the current Microsoft documentation for SQL Server 2025, since supported filtered index options can change between major versions.

Step 3: Check Database Compatibility Level

• Run SELECT name, compatibility_level FROM sys.databases WHERE name = 'YourDatabaseName'; to confirm the current level.
• If the database was recently upgraded, test whether lowering or raising compatibility level (e.g., ALTER DATABASE YourDatabaseName SET COMPATIBILITY_LEVEL = 160;) changes the validation behavior, then adjust your script rather than the compatibility level permanently.

Step 4: Rebuild the Index Using Supported Syntax

• Drop the failed index attempt if partially created: DROP INDEX IF EXISTS IX_FilteredExample ON dbo.Orders;
• Re-run the corrected CREATE INDEX script from Step 2.
• Monitor execution in SSMS “Messages” tab to confirm no errors return.

Step 5: Update Automation Scripts and Deployment Pipelines

• Search source control (e.g., Git repositories, DACPAC projects) for other instances of filtered indexes using the same unsupported option.
• Update CI/CD deployment scripts (SSDT, Flyway, or Liquibase) to prevent redeployment of the broken syntax.

Alternative or Verification Steps

• Verify the fix by querying sys.indexes and sys.filter_definitions to confirm the index was created successfully with the correct filter predicate and no error was thrown.
• If the issue persists across multiple scripts, run sp_helpindex or review sys.dm_db_index_usage_stats after deployment to confirm the index is active and being used as expected by the query optimizer.

Troubleshooting Guide: SQL Server Error 10670 – Resumable Index Creation Fails on LOB Columns

Why This Error Happens

Error 10670 occurs because Resumable Online Index Create (RIC) in SQL Server 2025 does not support indexes that include Large Object (LOB) data types such as varchar(max), nvarchar(max), varbinary(max), xml, or text/image/ntext. The resumable indexing engine relies on internal checkpointing of the build process, and SQL Server cannot persist or roll forward a partial build state when LOB pages are involved due to how LOB allocation units are versioned. This is a design limitation rather than a corruption or permissions issue, so the operation fails immediately at the CREATE INDEX ... WITH (RESUMABLE = ON) statement.

Step-by-Step Resolution Guide

Step 1: Identify the Offending Column

• Review the error message text, which specifies the exact column name (%.*ls) causing the failure.
• Run the following query to confirm LOB column types on the target table:

  SELECT c.name, t.name AS data_type
  FROM sys.columns c
  JOIN sys.types t ON c.user_type_id = t.user_type_id
  WHERE c.object_id = OBJECT_ID('YourSchema.YourTable');

• Confirm whether the column is actually needed in the index key or can be moved to an INCLUDE list (note: LOB columns in INCLUDE also block resumability).

Step 2: Disable the RESUMABLE Option for This Specific Index

• Modify your CREATE INDEX or ALTER INDEX ... REBUILD statement to remove the resumable clause:

  CREATE INDEX IX_YourIndex
  ON YourSchema.YourTable (YourColumn)
  WITH (ONLINE = ON, RESUMABLE = OFF);

• If using Azure Data Studio or SSMS Index Wizard, uncheck “Resumable” in the Advanced Options dialog before execution.

Step 3: Evaluate Schema Redesign (If Resumability Is Required)

• If resumable builds are mandatory for your maintenance window (e.g., large VLDB environments), consider separating LOB data into a secondary table linked by a foreign key, keeping the indexed table LOB-free.
• Apply DATA_COMPRESSION = PAGE or COLUMNSTORE only after confirming LOB exclusion, as this does not bypass the restriction but improves build speed for the non-resumable job.

Step 4: Apply the Change During a Maintenance Window

• Since the operation will run as a standard (non-resumable) online or offline index operation, schedule it during low-traffic hours to avoid blocking, especially if ONLINE = OFF is required due to Enterprise/Standard edition licensing constraints in SQL Server 2025.
• Monitor progress using:

  SELECT percent_complete, start_time, status_desc
  FROM sys.dm_exec_requests
  WHERE command LIKE 'CREATE INDEX%';

Alternative or Verification Steps

• Verify the fix: Re-run the CREATE INDEX statement without the RESUMABLE = ON option and confirm it completes with “Command(s) completed successfully” in SSMS or Azure Data Studio.
• Alternative workaround: If you must retain resumability for other indexes on the same table, isolate the LOB column into its own non-clustered index built with RESUMABLE = OFF, while keeping all other non-LOB indexes resumable as needed.

Troubleshooting Guide: Error 10669 – Unsupported Option for Nonclustered Index on Clustered Columnstore Index

Why This Error Happens

This error occurs because a specific index option (such as DROP_EXISTING, ONLINE, or a compression/sort-related clause represented by %S_MSG) was included in a CREATE INDEX statement targeting a nonclustered index built on top of a clustered columnstore index (CCI). SQL Server 2025 restricts certain T-SQL syntax options when creating nonclustered indexes (including nonclustered B-tree or nonclustered columnstore indexes) on a table that already has a clustered columnstore index, because the underlying storage engine for columnstore data does not support the same metadata operations as rowstore indexes. This is a syntax/feature compatibility limitation rather than a permissions or connectivity issue.

Step-by-Step Resolution Guide

Step 1: Identify the Unsupported Option in Your Script

• Review the exact CREATE INDEX or CREATE NONCLUSTERED INDEX statement that triggered Error 10669.
• Look for options like DROP_EXISTING = ON, ONLINE = ON, SORT_IN_TEMPDB, or DATA_COMPRESSION clauses placed in the WITH (...) block.
• Check SQL Server Management Studio (SSMS) “Messages” tab or the Windows Event Viewer (Applications and Services Logs > MSSQLSERVER) to capture the full error text, since %S_MSG is replaced with the actual unsupported keyword at runtime.

Step 2: Rewrite the Index Creation Statement

• Remove the unsupported option entirely from the WITH clause.
• Example of a corrected syntax:

CREATE NONCLUSTERED INDEX IX_Sales_CustomerID
ON dbo.FactSales (CustomerID)
WITH (MAXDOP = 4);

• If you need DROP_EXISTING, first drop the index manually using DROP INDEX IX_Name ON dbo.TableName; and then recreate it without that option.
• Avoid combining ONLINE = ON with nonclustered indexes on CCI tables, as online rebuild operations are only partially supported depending on edition and index type.

Step 3: Validate Table and Index Compatibility

• Confirm the table’s current index structure using:

SELECT name, type_desc FROM sys.indexes WHERE object_id = OBJECT_ID('dbo.FactSales');

• Ensure you are not attempting to apply rowstore-only options to a columnstore-based nonclustered index.
• If the index is meant to be a nonclustered columnstore index (NCCI) instead, use CREATE COLUMNSTORE INDEX syntax rather than standard B-tree syntax.

Step 4: Apply Latest Cumulative Update

• Open SQL Server Configuration Manager or check version via SELECT @@VERSION;.
• Compare against the latest Cumulative Update (CU) for SQL Server 2025 on the Microsoft Update Catalog.
• Install the latest CU on Windows Server 2025 or Windows 11, since certain columnstore index option restrictions are refined/patched in updates.
• Restart the SQL Server (MSSQLSERVER) service after patching via services.msc or net stop MSSQLSERVER && net start MSSQLSERVER.

Alternative or Verification Steps

• Re-run the corrected CREATE INDEX statement and confirm it completes without Error 10669; then verify the index exists via sys.indexes or SSMS Object Explorer under Indexes.
• If the error persists, drop and fully recreate the clustered columnstore index first (DROP INDEX then CREATE CLUSTERED COLUMNSTORE INDEX), then add the nonclustered index as a separate, minimal statement without extra WITH options.

Troubleshooting Guide: Error 10668 — “%S_MSG option is not supported for creating an index on a heap”

Why This Error Happens

This error occurs when a CREATE INDEX statement includes an option (such as ONLINE, SORT_IN_TEMPDB combined with certain flags, or a clustered-only property) that SQL Server 2025 does not support for tables without a clustered index (heaps). Heaps lack the underlying B-tree structure required to apply certain index-build options, so the Database Engine rejects the operation. This is a syntax/compatibility issue tied to table schema design rather than a permissions or connectivity problem.

Step-by-Step Resolution Guide

Step 1: Identify the Target Table Structure

• Run the following query to confirm whether the table is a heap:

  SELECT OBJECT_NAME(object_id) AS TableName, index_id, type_desc
  FROM sys.indexes
  WHERE object_id = OBJECT_ID('YourSchema.YourTable');

• A result showing index_id = 0 and type_desc = HEAP confirms the table has no clustered index.
• Note the unsupported option mentioned in the original error message (e.g., ONLINE = ON, RESUMABLE = ON, etc.).

Step 2: Modify the CREATE INDEX Statement

• Remove the unsupported option from your script. For example, change:

  CREATE NONCLUSTERED INDEX IX_Test ON dbo.YourTable (ColumnName)
  WITH (ONLINE = ON);

to:

  CREATE NONCLUSTERED INDEX IX_Test ON dbo.YourTable (ColumnName);

• If the option is required for your workload (e.g., minimal downtime), consider adding a clustered index first instead.

Step 3: Convert the Heap to a Clustered Table (If Required)

• If the business requirement needs advanced indexing options like ONLINE rebuilds, create a clustered index on a suitable key column:

  CREATE CLUSTERED INDEX IX_Clustered ON dbo.YourTable (PrimaryKeyColumn);

• This converts the heap into a clustered table, after which most indexing options (including ONLINE) become supported.

Step 4: Re-run the Index Creation Script

• Execute the corrected CREATE INDEX statement in SQL Server Management Studio (SSMS) or via sqlcmd.
• Confirm no further Error 10668 messages appear in the Messages tab or command output.

Alternative or Verification Steps

• Verify success by re-querying sys.indexes to confirm the new index now appears with the expected type_desc (e.g., NONCLUSTERED or CLUSTERED).
• If converting to a clustered table isn’t feasible, simply omit the unsupported WITH option — the index will still be created successfully on the heap, just without that specific build behavior.

Troubleshooting Guide: SQL Server Error 10667 – CREATE INDEX MAXDOP Resumption Warning

Why This Error Happens

This warning occurs when a CREATE INDEX or ALTER INDEX operation is started with a specified MAXDOP value that exceeds the limit allowed by Resource Governor’s workload group settings, the server’s configured max degree of parallelism, or the licensed CPU core limit for the SQL Server edition. SQL Server does not fail the operation outright; instead, it silently downgrades the parallelism level and resumes execution with a lower MAXDOP value. This typically happens after a server migration, a Resource Governor policy change, or when running on hardware with more cores than the SQL Server edition (e.g., Standard Edition) is licensed to use.

Step-by-Step Resolution Guide

Step 1: Check the Instance-Level MAXDOP Configuration

• Open SQL Server Management Studio (SSMS) and connect to the instance reporting the error.
• Run EXEC sp_configure 'max degree of parallelism'; to view the current server-wide value.
• Compare this value against the MAXDOP explicitly specified in your CREATE INDEX ... WITH (MAXDOP = n) statement.
• If the index statement’s value is higher than the server configuration, this mismatch is likely the trigger.

Step 2: Inspect Resource Governor Workload Group Limits

• Run SELECT * FROM sys.resource_governor_workload_groups; to view the max_dop column for the active workload group.
• Resource Governor settings override session-level MAXDOP hints, so a restrictive workload group (e.g., max_dop = 2) will force a downgrade even if you requested MAXDOP 8.
• Adjust the workload group using ALTER WORKLOAD GROUP [GroupName] WITH (MAX_DOP = n); followed by ALTER RESOURCE GOVERNOR RECONFIGURE;.

Step 3: Verify CPU Core Licensing and Affinity Settings

• Navigate to SQL Server Configuration Manager on Windows Server 2025 / Windows 11 and check SQL Server Properties > Advanced for core visibility.
• Confirm the edition (Standard vs. Enterprise) isn’t silently capping usable cores, which indirectly limits effective MAXDOP during index builds.
• Check sys.dm_os_schedulers to confirm the number of schedulers matches expected physical/logical core counts.

Step 4: Align the Index Statement’s MAXDOP Hint

• Edit the T-SQL script issuing the CREATE INDEX command so the MAXDOP hint matches or stays below the server and Resource Governor limits.
• Example: CREATE INDEX IX_Test ON dbo.Table1(Col1) WITH (MAXDOP = 4); where 4 is confirmed as the maximum allowed value.
• Save and re-run the corrected script to confirm the warning no longer appears.

Alternative or Verification Steps

• Re-run the index creation script and check the Messages tab in SSMS; the warning should no longer appear if the MAXDOP values now align across all layers (query hint, instance config, and Resource Governor).
• As an alternative, use sys.dm_exec_query_stats or Extended Events to trace actual DOP usage during index rebuilds, confirming the operation is now executing at the intended parallelism level without silent downgrades.

Troubleshooting Guide: SQL Server Error 10666 — Resumable Index Build DOP Unavailable

Why This Error Happens

This error occurs when a paused resumable index operation attempts to resume using the same degree of parallelism (DOP) it was originally started with, but the SQL Server instance cannot currently allocate that many worker threads due to resource governor limits, concurrent workload demand, or a lowered max degree of parallelism configuration. It frequently happens after a server reboot, a failover, or a configuration change that reduces available schedulers/CPUs relative to when the index operation was initially launched. Essentially, SQL Server enforces that a resumed operation must match its original parallelism footprint, and if the current environment cannot satisfy that footprint, the resume request is rejected.

Step-by-Step Resolution Guide

Step 1: Check Current Server DOP Configuration

• Open SQL Server Management Studio (SSMS) and connect to the affected instance.
• Run EXEC sp_configure 'max degree of parallelism'; to see the configured MAXDOP value.
• Compare this value against the DOP reported in the Error 10666 message to confirm a mismatch.

Step 2: Resume the Index Operation with an Explicit Lower DOP

• Instead of relying on the default resume behavior, explicitly specify a DOP value that matches current resource availability.
• Run a query similar to:

  ALTER INDEX [YourIndexName] ON [Schema].[Table]
  RESUME WITH (MAXDOP = 2);

• Adjust the number based on how many schedulers/CPUs are actually free, which you can check via sys.dm_os_schedulers.

Step 3: Review Resource Governor and Workload Group Limits

• Navigate to Object Explorer > Management > Resource Governor in SSMS.
• Check if the workload group handling this session has a MAX_DOP cap lower than what the index operation originally requested.
• If needed, run:

  ALTER WORKLOAD GROUP [YourGroupName] WITH (MAX_DOP = 4);
  ALTER RESOURCE GOVERNOR RECONFIGURE;

Step 4: Abort and Restart the Index Operation If Resume Fails

• If the DOP mismatch cannot be resolved quickly, abort the stalled operation:

  ALTER INDEX [YourIndexName] ON [Schema].[Table] ABORT;

• Restart the index build fresh with a DOP value appropriate for current server capacity:

  CREATE INDEX [YourIndexName] ON [Schema].[Table] (Column)
  WITH (ONLINE = ON, MAXDOP = 2, RESUMABLE = ON);

Step 5: Validate Hardware/VM Resource Allocation

• On Windows Server 2025 or Windows 11 hosts, confirm no recent changes reduced the VM’s vCPU count or NUMA node allocation (check Hyper-V Manager > Settings > Processor).
• Ensure no other high-priority SQL Server instances or services are consuming all available schedulers at the time of resume.

Alternative or Verification Steps

• After resuming or recreating the index, run SELECT * FROM sys.index_resumable_operations; to confirm the operation state shows percent_complete progressing normally without errors.
• If the issue persists, consider setting RESUMABLE = ON with a conservative fixed MAXDOP value at the start of all large index builds going forward, to avoid DOP mismatches during future resume attempts.

Troubleshooting Guide: SQL Server Error 10665 (DROP_EXISTING Option Conflict)

Why This Error Happens

This error occurs when a CREATE INDEX statement attempts to combine the DROP_EXISTING option with another incompatible index option, such as those used for filtered indexes, columnstore rebuilds, or certain online indexing operations. SQL Server 2025’s query optimizer explicitly blocks this combination because recreating an index with DROP_EXISTING while simultaneously applying conflicting metadata options could result in an inconsistent index structure. Essentially, this is a T-SQL syntax validation failure rather than a system or permissions issue—the engine is preventing you from executing a statement it cannot safely process.

Step-by-Step Resolution Guide

Step 1: Identify the Conflicting T-SQL Statement

• Open SQL Server Management Studio (SSMS) and locate the exact query from the error log or job history that triggered Error 10665.
• Check the SQL Server Error Log via Object Explorer > Management > SQL Server Logs or query sys.fn_xe_file_target_read_file if using Extended Events for a precise timestamp match.
• Copy the full CREATE INDEX or ALTER INDEX statement into a new query window for editing.

Step 2: Remove or Separate the Incompatible Option

• Review the statement for options paired alongside DROP_EXISTING, such as ONLINE = ON (in specific edge cases), DATA_COMPRESSION on unsupported column types, or filtered index WHERE clauses combined with existing non-filtered indexes.
• Split the operation into two distinct steps instead of one combined statement:
• First, run DROP INDEX [IndexName] ON [Schema].[TableName];
• Second, run a separate CREATE INDEX statement without the DROP_EXISTING flag, applying your desired options fresh.
• Example fix:

  -- Instead of this (causes Error 10665):
  -- CREATE INDEX IX_Test ON dbo.Orders(OrderID) WITH (DROP_EXISTING = ON, <Conflicting_Option>);

  -- Use this:
  DROP INDEX IX_Test ON dbo.Orders;
  CREATE INDEX IX_Test ON dbo.Orders(OrderID) WITH (<Desired_Option>);

Step 3: Update Maintenance Plans and Scripts

• If the error originates from an automated maintenance plan or a third-party deployment script (e.g., SSDT or a DACPAC), check the underlying .sql script files for auto-generated DROP_EXISTING syntax.
• Navigate to the script repository (commonly found in C:\Program Files\Microsoft SQL Server\160\DTS\Packages\ or your CI/CD pipeline’s artifact folder) and manually patch the offending index creation logic.
• Redeploy the corrected script to the Windows Server 2025 instance.

Step 4: Validate Index Compatibility Settings

• Confirm that the database compatibility level supports the specific index options you are trying to use.
• Run the following query to check current compatibility:

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

• If necessary, adjust using ALTER DATABASE YourDatabaseName SET COMPATIBILITY_LEVEL = 160; (adjust level based on your specific SQL 2025 build requirements).

Alternative or Verification Steps

• Verify the Fix: Re-run the corrected CREATE INDEX script in a non-production environment first. If the index builds successfully without throwing Error 10665, check sys.indexes to confirm the index_id and properties match your intended configuration.
• Alternative Approach: If you are relying on an ORM or migration tool (like Entity Framework) that auto-generates this faulty syntax, update the tool/provider to the latest version compatible with SQL Server 2025, as older drivers may not recognize the new restricted option combinations.

Troubleshooting Guide: Error 10664 – Cannot Specify Included Columns for Indexes on Memory-Optimized Tables

Why This Error Happens

Error 10664 occurs because memory-optimized tables in SQL Server use a fundamentally different indexing engine (Hekaton) than disk-based tables, and this engine does not support the INCLUDE clause for nonclustered or columnstore indexes. This limitation is by design, not a bug, since memory-optimized indexes (hash and range) are structured in-memory data structures that index only key columns rather than relying on leaf-level page storage. Attempting to run CREATE INDEX or ALTER TABLE statements with an INCLUDE (column_name) clause against a table defined WITH (MEMORY_OPTIMIZED = ON) will trigger this error every time.

Step-by-Step Resolution Guide

Step 1: Identify the Offending T-SQL Statement

• Locate the exact CREATE INDEX or inline index definition inside your CREATE TABLE statement that contains the INCLUDE (...) clause.
• Check SQL Server Management Studio (SSMS) Query Results/Messages pane for the specific index name referenced in the Error 10664 message.
• Confirm the target table has MEMORY_OPTIMIZED = ON by running:

  SELECT name, is_memory_optimized FROM sys.tables WHERE name = 'YourTableName';

Step 2: Remove the INCLUDE Clause from the Index Definition

• Rewrite the index to remove the INCLUDE columns entirely, since memory-optimized nonclustered indexes only support key columns.
• Example of an invalid definition to fix:

  INDEX ix_Example NONCLUSTERED (OrderID) INCLUDE (CustomerName)

• Corrected version (key columns only):

  INDEX ix_Example NONCLUSTERED (OrderID, CustomerName)

• If you need CustomerName searchable/sortable, add it directly into the key column list instead of as an included column.

Step 3: Evaluate Index Type (Hash vs. Nonclustered Range)

• If your workload requires covering-index-style performance (common reason for using INCLUDE), consider whether a nonclustered range index fits better than a hash index, since range indexes support ordered scans across multiple key columns.
• Modify the index definition in SSMS or your deployment script:

  ALTER TABLE dbo.YourTable 
  ADD INDEX ix_Range NONCLUSTERED (OrderID, CustomerName, OrderDate);

• Test query plans using SET STATISTICS XML ON to confirm the new key-only index satisfies your query’s SARGable predicates.

Step 4: Redeploy and Validate Schema Changes

• Drop and recreate the table (or use ALTER TABLE where supported) with the corrected index syntax, since memory-optimized index changes often require table recreation in older compatibility levels.
• Run the deployment script in a non-production environment first:

  DROP TABLE IF EXISTS dbo.YourTable;
  -- Re-run corrected CREATE TABLE script with fixed index definitions

• Confirm compatibility level is set to 160 or higher for SQL Server 2025 memory-optimized feature support:

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

Alternative or Verification Steps

• Verify the fix by querying sys.indexes and sys.index_columns to confirm no is_included_column = 1 entries exist for indexes on the memory-optimized table.
• If covering-index performance is critical, consider migrating the specific table back to a disk-based table (removing MEMORY_OPTIMIZED = ON) where full INCLUDE column support is available.

Troubleshooting Guide: Error 10663 – Snapshot View Refresh Conflict (SQL Server 2025)

Why This Error Happens

Error 10663 occurs when SQL Server’s internal scheduler detects that a system-wide refresh operation for snapshot views is already active, and a second refresh request (either for the same view or another snapshot view) is submitted before the first one completes. This is typically caused by overlapping scheduled jobs, a long-running refresh on a large dataset blocking the queue, or a previous refresh session that failed to release its internal lock due to an abrupt connection termination. In some cases, corrupted metadata in the sys.snapshot_views catalog can also cause the engine to falsely report an in-progress state even after the original job has finished.

Step-by-Step Resolution Guide

Step 1: Identify Concurrent Refresh Sessions

• Open SQL Server Management Studio (SSMS) and connect to the instance via a new query window.
• Run SELECT * FROM sys.dm_exec_requests WHERE command LIKE '%REFRESH%'; to check for active refresh threads.
• Note the session_id of any long-running or stalled snapshot refresh commands.

Step 2: Terminate Stalled Processes and Clear Locks

• If a stalled session is identified, terminate it safely using KILL [session_id]; in the query window.
• Navigate to the SQL Server Error Log via Object Explorer > Management > SQL Server Logs to confirm the lock was released.
• Restart the SQL Server (MSSQLSERVER) service via services.msc on Windows Server 2025 if the KILL command does not release the internal snapshot scheduler lock.

Step 3: Adjust Scheduling to Prevent Overlap

• Open SQL Server Agent and expand the Jobs folder to review all jobs tied to snapshot view maintenance.
• Right-click the specific job (e.g., SnapshotView_AutoRefresh) and select Properties > Schedules.
• Stagger the execution times by at least 15-30 minutes to ensure no two snapshot refresh jobs attempt to run concurrently.

Step 4: Rebuild Snapshot View Metadata (If Error Persists)

• If the lock remains after a service restart, the view metadata may be corrupted.
• Execute ALTER VIEW [ViewName] WITH SCHEMABINDING AS ... (re-running the original view definition script) to force a metadata refresh.
• Alternatively, drop and recreate the specific snapshot view using DROP VIEW followed by CREATE VIEW.

Alternative or Verification Steps

• Verification: Run the refresh command manually via EXEC sp_refreshsnapshotview '[ViewName]'; immediately after restarting the service. If the command executes without returning Error 10663, the lock has been successfully cleared.
• Alternative: If the error persists across multiple views, apply the latest Cumulative Update (CU) for SQL Server 2025, as this specific locking mechanism may require a hotfix to resolve a race condition in the scheduler.

Troubleshooting Guide: Error 10662 – Snapshot View Refresh Already in Progress

Why This Error Happens

Error 10662 occurs when SQL Server 2025 attempts to initiate a refresh on a snapshot view while a prior refresh operation on that same view has not yet completed or released its lock. This is typically caused by overlapping scheduled jobs, long-running transactions holding metadata locks, or an orphaned session that failed to clean up after a previous refresh crashed or timed out. In some cases, corrupted snapshot metadata in the system catalog can also prevent SQL Server from correctly recognizing that the prior operation has finished.

Step-by-Step Resolution Guide

Step 1: Identify the Blocking Session

• Open SQL Server Management Studio (SSMS) and connect to the instance reporting the error.
• Run the following query to locate active sessions tied to the snapshot view refresh:

  SELECT session_id, status, blocking_session_id, wait_type, wait_resource
  FROM sys.dm_exec_requests
  WHERE command LIKE '%REFRESH%';

• Note the session_id of any long-running or stalled refresh process.

Step 2: Terminate the Orphaned or Stuck Process

• If a session is confirmed stalled (no progress for an extended period), safely terminate it:

  KILL <session_id>;

• Avoid killing sessions that are actively progressing, as this may corrupt the snapshot view state further.

Step 3: Clear Snapshot Metadata Locks

• Navigate to C:\Program Files\Microsoft SQL Server\160\MSSQL\Log\ (adjust path for SQL Server 2025 build) and review the ERRORLOG for related deadlock or lock-timeout entries.
• Run DBCC CHECKDB against the affected database to rule out metadata corruption:

  DBCC CHECKDB (YourDatabaseName) WITH NO_INFOMSGS, ALL_ERRORMSGS;

• If corruption is found in the snapshot view catalog, rebuild the view using DROP and CREATE statements rather than attempting an in-place repair.

Step 4: Reschedule Conflicting Jobs

• Open SQL Server Agent and check the Job Activity Monitor for overlapping schedules targeting the same snapshot view.
• Adjust job schedules in SQL Server Agent > Jobs > Properties > Schedules to ensure a minimum buffer window (e.g., 10–15 minutes) between refresh attempts.

Alternative or Verification Steps

• To verify the fix, manually trigger the snapshot view refresh again and confirm Error 10662 no longer appears in the SQL Server ERRORLOG or Windows Event Viewer (under Applications and Services Logs > MSSQLSERVER).
• If the issue persists, drop and recreate the snapshot view entirely using CREATE VIEW ... WITH SCHEMABINDING, then re-establish any dependent indexed views to eliminate residual locking artifacts.

Troubleshooting Guide: Error 10661 – Snapshot View Refresh Conflict

Why This Error Happens

Error 10661 occurs when Microsoft SQL Server 2025 attempts to initiate a bulk refresh of all snapshot views while another refresh process—either a global refresh or a single-view refresh—is already holding a lock on the metadata. This is typically caused by overlapping scheduled jobs (via SQL Server Agent) or a previous refresh transaction that failed to release its lock due to an abrupt termination or long-running query. In some cases, this is exacerbated by corrupted session states in the msdb system database following an unexpected server restart on Windows Server 2025.

Step-by-Step Resolution Guide

Step 1: Identify Active Refresh Sessions

• Open SQL Server Management Studio (SSMS) and connect to the target instance.
• Run sp_who2 or query sys.dm_exec_requests to look for sessions with the command REFRESH VIEW or SNAPSHOT SYNC.
• Note the Session ID (SPID) of any long-running or “suspended” processes related to snapshot synchronization.

Step 2: Terminate Conflicting Sessions

• If a stuck session is identified, terminate it manually using the Kill command: KILL [SPID] (replace [SPID] with the actual session number).
• Wait for the rollback to complete; monitor progress via sys.dm_exec_requests checking the percent_complete column.
• Avoid force-stopping the SQL Server service, as this can lead to further metadata corruption in Windows Server 2025 environments.

Step 3: Clear the Snapshot Lock Cache

• Navigate to the SQL Server configuration directory, typically located at C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Data.
• Restart the SQL Server VSS Writer service via the Windows Services console (services.msc) to clear any residual snapshot locks.
• Execute DBCC DROPCLEANBUFFERS (in a test environment first) to ensure no stale cache data is causing a false-positive lock detection.

Step 4: Serialize the Refresh Schedule

• Open SQL Server Agent and locate the job responsible for the “Refresh All” operation.
• Edit the job schedule to ensure it does not overlap with individual view refresh triggers.
• Add a WAITFOR DELAY '00:00:30' buffer between dependent job steps to prevent race conditions in future executions.

Alternative or Verification Steps

• Verification: Re-run the “Refresh All” command via SSMS or your reporting tool; if successful, check the SQL Server Error Log (via xp_readerrorlog) to confirm no new 10661 entries have been generated.
• Alternative: If the error persists, restart the SQL Server (MSSQLSERVER) service during a maintenance window via Windows Server 2025’s Server Manager to forcibly clear all in-memory session locks.

Troubleshooting Guide: Error 10660 — Online Index Build Chosen as Deadlock Victim

Why This Error Happens

This error occurs when an online index rebuild operation repeatedly conflicts with concurrent transactions attempting to modify the same row(s), causing SQL Server’s lock manager to resolve the deadlock by killing the index build process. It typically happens when long-running or frequent DML operations (INSERT/UPDATE/DELETE) hold row-level or page-level locks on the underlying table while the online index operation attempts to acquire a schema modification (Sch-M) or update lock during its final phase. High-concurrency OLTP workloads with poor transaction isolation settings or excessively long-running transactions are the most common triggers.

Step-by-Step Resolution Guide

Step 1: Identify the Blocking Session

• Open SQL Server Management Studio (SSMS) and run sp_whoisactive or query sys.dm_exec_requests joined with sys.dm_os_waiting_tasks to find sessions blocking the index operation.
• Look specifically for sessions with wait_type values like LCK_M_SCH_M or LCK_M_U tied to the table being indexed.
• Note the blocking_session_id and the SPID of the online index build.

Step 2: Reduce Lock Contention During the Build

• Re-run the index operation with a lower concurrency footprint using the MAX_DURATION and WAIT_AT_LOW_PRIORITY options (available in SQL Server 2025):

  ALTER INDEX IX_YourIndex ON dbo.YourTable
  REBUILD WITH (ONLINE = ON (WAIT_AT_LOW_PRIORITY (MAX_DURATION = 5 MINUTES, ABORT_AFTER_WAIT = SELF)));

• This forces the operation to wait briefly for a low-priority lock slot instead of forcibly deadlocking out other transactions.

Step 3: Schedule the Rebuild During Low-Traffic Windows

• Use SQL Server Agent (via SSMS or sqlcmd) to schedule the index rebuild job during off-peak hours when transaction volume is lower.
• Navigate to SQL Server Agent > Jobs > New Job in SSMS and set the schedule under the Schedules tab.
• Confirm the Windows Server 2025 task scheduler isn’t running conflicting maintenance plans at the same time via Task Scheduler > Task Scheduler Library.

Step 4: Adjust Transaction Isolation Level on Competing Queries

• Check the application code or stored procedures running concurrently and consider setting READ_COMMITTED_SNAPSHOT ON at the database level to reduce blocking:

  ALTER DATABASE YourDatabase SET READ_COMMITTED_SNAPSHOT ON;

• This shifts read operations to use row versioning instead of shared locks, reducing contention with the online index build.

Step 5: Shorten Long-Running Transactions

• Audit application logic for transactions that remain open longer than necessary (e.g., missing COMMIT statements or long-held explicit transactions).
• Use DBCC OPENTRAN to check for any old open transactions holding locks on the target table before retrying the index operation.

Alternative or Verification Steps

• Re-run the ALTER INDEX ... REBUILD command and monitor sys.dm_exec_requests for the operation completing without hitting Error 10660 again.
• If the issue persists, consider rebuilding the index offline during a maintenance window (ONLINE = OFF) as a fallback, since offline rebuilds are not subject to the same row-level deadlock victim selection.

Troubleshooting Guide: SQL Server Error 10659

Why This Error Happens

Error 10659 is actually an informational message, not a true failure condition — it is raised by SQL Server’s auditing/notification mechanism whenever an ALTER INDEX operation (such as REBUILD, REORGANIZE, DISABLE, or a column-store conversion) completes successfully on a given object. It typically surfaces in the SQL Server Error Log or Application Event Log because a DDL trigger, Extended Event, SQL Trace, or third-party monitoring tool is configured to capture and log schema-level changes for compliance or auditing purposes. Administrators often mistake it for an error because it appears alongside genuine error codes, but its presence usually indicates verbose logging, audit trails, or alerting rules are intercepting routine maintenance (index rebuild jobs, Maintenance Plans, or Ola Hallengren scripts) rather than an actual malfunction.

Step-by-Step Resolution Guide

Step 1: Confirm the Message is Informational, Not a Failure

• Open SQL Server Management Studio (SSMS) and check SQL Server Logs under Management > SQL Server Logs.
• Cross-reference the timestamp of Event ID 10659 with your index maintenance job (Agent Job history or Maintenance Plan logs) to confirm it aligns with a scheduled ALTER INDEX execution.
• Verify there is no accompanying severity level 16+ error in the same log entry batch.

Step 2: Review Auditing and Extended Events Configuration

• Navigate to SQL Server Management Studio > Management > Extended Events > Sessions and check if a session is capturing object_altered or audit_schema_object_management_event.
• If using SQL Server Audit, run the following to list active audits:

SELECT * FROM sys.server_audits;
SELECT * FROM sys.server_audit_specifications;

• Disable or scope down the audit specification if the logging volume is unnecessary for your compliance requirements.

Step 3: Check for DDL Triggers Generating the Message

• Query for server- or database-level DDL triggers that may be printing or raising this message explicitly:

SELECT name, is_disabled FROM sys.triggers WHERE parent_class = 0;
SELECT name, is_disabled FROM sys.server_triggers;

• If a custom trigger uses PRINT or RAISERROR referencing %S_MSG, review the trigger definition in sys.sql_modules and adjust its logging verbosity.

Step 4: Adjust Windows Event Log / SQL Server Log Verbosity

• Open Event Viewer (eventvwr.msc) on Windows Server 2025 / Windows 11 and navigate to Applications and Services Logs > MSSQLSERVER.
• If the volume of 10659 messages is flooding the log and causing performance or storage concerns, adjust the SQL Server error log retention via SSMS > Management > SQL Server Logs > Configure > Limit the number of error log files.
• Restart the SQL Server service (services.msc → SQL Server (MSSQLSERVER) → Restart) only if log configuration changes require it.

Step 5: Validate Index Operation Completed Successfully

• Run DBCC CHECKDB or check sys.dm_db_index_physical_stats to confirm the altered index is healthy and not fragmented or corrupted.
• Review the Agent Job step history to confirm no actual errors (severity 16+) were thrown alongside the 10659 notice.

Alternative or Verification Steps

• Verify the fix by re-running the index maintenance job manually and confirming that Event ID 10659 appears in the log only as an informational entry with no correlating error severity.
• If the message volume remains disruptive, consider filtering it out at the monitoring/alerting tool level (e.g., SCOM, SolarWinds, or Redgate Monitor) by excluding Event ID 10659 from alert rules, since it does not indicate an actionable failure.

Troubleshooting Guide: SQL Server Error 10658 – “Stored Procedure is Currently Disabled”

Why This Error Happens

This error occurs when a stored procedure has been explicitly disabled using the DISABLE TRIGGER equivalent mechanism for procedures, or more commonly, when a feature-dependent extended stored procedure has its parent feature turned off via sp_configure. In SQL Server 2025, this is frequently tied to surface area configuration settings where advanced options (such as CLR integration, OLE Automation, or xp_cmdshell-related procedures) are disabled at the instance level. It can also occur if the procedure was deliberately marked as disabled by an administrator or a security policy template applied through Windows Server 2025 Group Policy integration with SQL Server.

Step-by-Step Resolution Guide

Step 1: Identify Which Feature Governs the Procedure

• Open SQL Server Management Studio (SSMS) and connect to the instance throwing Error 10658.
• Run SELECT * FROM sys.configurations WHERE name LIKE '%<feature_name>%'; to check if the related feature is disabled.
• Cross-reference the procedure name (%ls in the error) with Microsoft’s list of configurable extended procedures (e.g., xp_cmdshell, sp_OACreate, xp_sendmail).

Step 2: Enable the Required Configuration Option

• Execute sp_configure 'show advanced options', 1; RECONFIGURE; to unlock advanced settings.
• Run sp_configure '<feature_name>', 1; RECONFIGURE; substituting the correct feature (e.g., 'Ole Automation Procedures' or 'xp_cmdshell').
• Restart the SQL Server service via services.msc or PowerShell: Restart-Service -Name 'MSSQLSERVER'.

Step 3: Check Group Policy and Security Baseline Overrides

• On Windows Server 2025, open gpedit.msc and navigate to Computer Configuration > Administrative Templates > SQL Server Security Baseline.
• Verify no policy is forcibly re-disabling the procedure on service restart or system reboot.
• If a policy conflict exists, create an exception in the Local Security Policy or request an update to the domain-level GPO from your security team.

Step 4: Validate Procedure-Level Permissions and Ownership

• Confirm the procedure has not been manually disabled via a custom flag in a sys.sql_modules wrapper or third-party security tool.
• Run EXEC sp_helprotect '<procedure_name>'; to confirm the current execution permissions.
• Grant EXECUTE permission explicitly if missing: GRANT EXECUTE ON <procedure_name> TO <user_or_role>;.

Alternative or Verification Steps

• After applying changes, re-run the original query or application call that triggered Error 10658 to confirm the procedure executes without the disabled-state message.
• If the issue persists, check the SQL Server Error Log (Management > SQL Server Logs in SSMS) for correlating Event ID entries, and compare against the Windows Event Viewer (Application log) for any blocked execution events from Windows Defender Application Control (WDAC), which is more aggressive in Windows Server 2025.

Troubleshooting Guide: Error 10657 – Resumable Index Operation Paused

Why This Error Happens

This informational error is raised by SQL Server when a resumable online index create or rebuild operation remains in a paused state, typically because the operation was manually paused and never resumed, or an active session was terminated (connection timeout, failover, or client disconnect) before the operation completed. SQL Server retains the partial index metadata and allocated resources so the operation can be resumed later, but if left paused too long, it can hold schema locks, consume tempdb space, and block subsequent DDL operations on the same object. The underlying root cause is almost always an interrupted maintenance job or an administrator intentionally pausing the rebuild without a follow-up resume/abort action.

Step-by-Step Resolution Guide

Step 1: Identify the Paused Resumable Index Operation

• Run the following query to list all paused resumable operations:
• SELECT name, object_id, percent_complete, last_pause_time, state_desc FROM sys.index_resumable_operations;
• Note the object_id, index name, and last_pause_time to assess how long it has been paused.

Step 2: Decide Whether to Resume or Abort the Operation

• If the maintenance window allows, resume the operation using:
• ALTER INDEX [IndexName] ON [Schema].[TableName] RESUME;
• If the operation is no longer needed or has been paused excessively long, abort it instead:
• ALTER INDEX [IndexName] ON [Schema].[TableName] ABORT;
• Check tempdb free space first via sys.dm_db_file_space_usage, since resuming a long-paused rebuild can spike tempdb usage.

Step 3: Check for Blocking Sessions or Failed Jobs

• Open SQL Server Management Studio (SSMS) and run sp_whoisactive or query sys.dm_exec_requests to confirm no blocking session is preventing the resume.
• Review SQL Server Agent Job History (in SSMS under SQL Server Agent > Jobs) for the maintenance job that initiated the rebuild to identify why it was interrupted (timeout, failover, manual cancel).
• On Windows Server 2025 / Windows 11, check Event Viewer under Applications and Services Logs > Microsoft > SQL Server for correlated disconnect or failover events around the last_pause_time.

Step 4: Adjust Maintenance Job Timeout and Retry Settings

• Edit the SQL Server Agent Job step to increase the timeout value or add automatic retry logic.
• Ensure the job uses WITH (RESUMABLE = ON, MAX_DURATION = X MINUTES, PAUSED_RESUME_WAIT_AT_LOW_PRIORITY) syntax appropriately so future interruptions pause gracefully instead of failing outright.

Alternative or Verification Steps

• Verify the fix by re-running SELECT * FROM sys.index_resumable_operations; — it should return no rows for the previously paused index, confirming it completed or was aborted successfully.
• If resuming repeatedly fails due to resource constraints, drop and recreate the index using a standard (non-resumable) CREATE INDEX ... WITH (ONLINE = ON) operation during a low-traffic maintenance window instead.

Troubleshooting Guide: Error 10656 – Resumable Index Operation Paused

Why This Error Happens

Error 10656 occurs when SQL Server automatically pauses a resumable index creation or rebuild operation, typically due to a failover, lock timeout, user-initiated pause, or loss of connection to the database engine during the operation. This behavior is by design in SQL Server’s resumable index feature, but it can also be triggered by insufficient transaction log space, memory pressure, or an unexpected session termination. In some cases, underlying disk I/O issues or corrupted index metadata on Windows Server 2025 can prevent the operation from completing normally, forcing SQL Server to pause it as a safety mechanism.

Step-by-Step Resolution Guide

Step 1: Verify the Current State of the Paused Index Operation

• Run the following query to check index operation status:

  SELECT * FROM sys.index_resumable_operations;

• Confirm the state_desc column shows PAUSED and note the percent_complete value.
• Check SQL Server Error Log via SSMS or C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\Log\ERRORLOG for related entries.

Step 2: Check System Resources and Transaction Log Space

• Open Task Manager or Performance Monitor on Windows Server 2025/Windows 11 to check for memory and CPU pressure.
• Verify transaction log free space:

  DBCC SQLPERF(LOGSPACE);

• If log space is low, expand the log file via SSMS: Right-click database → Properties → Files → increase file size or enable autogrowth.
• Ensure the drive hosting .ldf files has sufficient free disk space (recommended minimum 20% free).

Step 3: Resume the Index Operation

• Execute the resume command once resources are confirmed stable:

  ALTER INDEX [YourIndexName] ON [YourSchema].[YourTable] RESUME;

• If the operation fails again immediately, abort and restart cleanly:

  ALTER INDEX [YourIndexName] ON [YourSchema].[YourTable] ABORT;

• Rebuild using the resumable option explicitly:

  ALTER INDEX [YourIndexName] ON [YourSchema].[YourTable] REBUILD WITH (ONLINE = ON, RESUMABLE = ON, MAX_DURATION = 60 MINUTES);

Step 4: Check for Failover or Connectivity Interruptions

• If using an Always On Availability Group, confirm there were no recent failovers in Failover Cluster Manager on Windows Server 2025.
• Review the Windows Event Viewer (Applications and Services Logs > MSSQLSERVER) for connectivity drops correlating with the pause timestamp.
• Stabilize network connectivity between the client/application server and the SQL Server instance before retrying.

Alternative or Verification Steps

• After resuming, re-run SELECT * FROM sys.index_resumable_operations; and confirm percent_complete reaches 100 and the row disappears upon completion.
• If the issue persists, drop the resumable operation entirely with ALTER INDEX ... ABORT, then recreate the index using CREATE INDEX ... WITH (ONLINE = ON) as a non-resumable fallback.

Troubleshooting Guide: Error 10655 – Snapshot View Refresh Failure

Why This Error Happens

Error 10655 occurs when SQL Server’s replication snapshot agent encounters a view reference that is defined using a one-part or three/four-part naming convention instead of the required two-part format (schema.view_name). This typically happens when articles are added to a publication with fully qualified names (including server or database name) or when the schema prefix is omitted entirely, causing the snapshot generation process to fail validation. Corrupted or outdated replication metadata in the distribution database can also trigger this mismatch during the refresh operation.

Step-by-Step Resolution Guide

Step 1: Identify the Problematic View Definition

• Open SQL Server Management Studio (SSMS) and connect to the Publisher instance.
• Run the following query against the publication database to list all article object names:

  SELECT article, objid, dest_object, dest_owner 
  FROM dbo.sysarticles;

• Look for entries where dest_owner is NULL or where the object name contains embedded database/server prefixes (e.g., MyServer.MyDB.dbo.MyView).

Step 2: Correct the View Naming Format

• Drop the problematic article from the publication using sp_droparticle in SSMS or via the Publication Properties GUI.
• Re-add the article ensuring the name strictly follows the two-part format schema.viewname (e.g., dbo.vw_SalesSummary), not database.schema.viewname.
• Use this command to re-add the article correctly:

  EXEC sp_addarticle 
      @publication = 'YourPubName', 
      @article = 'vw_SalesSummary', 
      @source_owner = 'dbo', 
      @source_object = 'vw_SalesSummary';

Step 3: Clear and Regenerate Replication Metadata

• Navigate to the Snapshot Agent properties in SSMS under Replication > Local Publications.
• Right-click the publication and select Reinitialize All Subscriptions to clear stale metadata.
• On Windows Server 2025, ensure the SQL Server Agent service account has read/write access to the snapshot folder path (default: C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\repldata).

Step 4: Restart the Snapshot Agent Job

• Open SQL Server Agent > Jobs and locate the Snapshot Agent job for the affected publication.
• Right-click and select Start Job at Step to manually trigger a fresh snapshot generation.
• Monitor the job history log for successful completion without Error 10655 reappearing.

Alternative or Verification Steps

• Verify the fix by querying sysarticles again to confirm all dest_object values now show clean two-part names, then check the Snapshot Agent job history for a “Succeeded” status.
• If the error persists, drop and recreate the entire publication from scratch, ensuring all source views are referenced using only schema.object syntax before re-adding subscribers.

Troubleshooting Guide: SQL Server Error 10654 – Snapshot View Refresh Lock Failure

Why This Error Happens

Error 10654 occurs when SQL Server’s snapshot refresh process cannot acquire the necessary schema or exclusive lock on the target database because another session, transaction, or replication process is actively holding a conflicting lock. This is commonly caused by long-running transactions, blocking sessions from reporting tools, or a backup/maintenance job executing concurrently with the scheduled snapshot refresh. In some cases, this also stems from the Snapshot Agent and Distribution Agent attempting to access the same database object simultaneously.

Step-by-Step Resolution Guide

Step 1: Identify Blocking Sessions

• Open SQL Server Management Studio (SSMS) and connect to the instance reporting the error.
• Run sp_who2 or query sys.dm_exec_requests to locate the blocking_session_id associated with the snapshot database.
• Note the session_id of the process holding the lock for further action.

Step 2: Terminate or Reschedule Conflicting Processes

• If the blocking session is a non-critical query or an idle transaction, terminate it using KILL <session_id>; in a new query window.
• If the conflict is caused by a scheduled backup or maintenance plan, reschedule the snapshot refresh job in SQL Server Agent > Jobs to avoid the overlapping time window.
• Check the Replication Monitor to ensure the Snapshot Agent and Log Reader Agent are not running simultaneously on the same publication.

Step 3: Adjust Lock Timeout and Retry Settings

• Navigate to the Publication Properties in SSMS under Replication > Local Publications.
• Increase the -QueryTimeout parameter for the Snapshot Agent profile (default is often too low for large databases).
• Update the registry or agent profile to allow for a longer LockTimeout value, typically located in HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL17.MSSQLSERVER\Replication on Windows Server 2025.

Step 4: Restart the SQL Server Agent Service

• Open Services.msc on Windows Server 2025 or Windows 11.
• Locate SQL Server Agent (MSSQLSERVER), right-click, and select Restart to clear any stale agent threads holding residual locks.
• Manually re-run the Snapshot Agent job immediately after the service restarts to generate a fresh snapshot.

Alternative or Verification Steps

• Verify the Fix: Re-run the subscription synchronization manually via the Replication Monitor; if the snapshot generates successfully without Error 10654 appearing in the Job History, the lock conflict has been resolved.
• Alternative Solution: If the error persists, consider converting the publication to use a Concurrent Snapshot processing option instead of the default, as this allows the snapshot to be generated without blocking or being blocked by other transactions in the source database.

Troubleshooting Guide: Error 10653 – Refresh of Snapshot View Failed

Why This Error Happens

Error 10653 typically occurs when SQL Server 2025 is unable to rebuild or synchronize a snapshot view due to insufficient permissions on the underlying schema or an inconsistency between the view’s metadata and the base table structure. This can also arise from a corrupted or stale query plan cache that prevents the view refresh from resolving correctly. In some cases, the issue stems from the snapshot isolation configuration not being properly enabled on the database, causing the refresh operation to fail silently before completion.

Step-by-Step Resolution Guide

Step 1: Verify Database Permissions and Ownership

• Confirm the service account running SQL Server 2025 has db_ddladmin or ALTER ANY SCHEMA rights on the target database.
• Open SQL Server Management Studio (SSMS) and run:

  SELECT * FROM sys.fn_my_permissions('dbo.YourViewName', 'OBJECT');

• Grant missing permissions using:

  GRANT ALTER ON SCHEMA::dbo TO [YourServiceAccount];

Step 2: Enable or Repair Snapshot Isolation

• Check if snapshot isolation is active on the database:

  SELECT snapshot_isolation_state_desc FROM sys.databases WHERE name = 'YourDatabase';

• If disabled, enable it manually:

  ALTER DATABASE YourDatabase SET ALLOW_SNAPSHOT_ISOLATION ON;

• Restart the SQL Server service via Services.msc (Windows Server 2025) or services.msc on Windows 11.

Step 3: Clear Corrupted Query Plan Cache

• Flush the plan cache associated with the problematic view:

  DBCC FREEPROCCACHE;

• Rebuild the view definition using:

  EXEC sp_refreshview 'dbo.YourViewName';

• Check the SQL Server error log located at:
  C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG

Step 4: Validate Windows Server Resource Access

• Ensure Windows Server 2025 / Windows 11 firewall or antivirus is not blocking SQL Server’s access to the data file directory.
• Navigate to C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\DATA\ and confirm the SQL Server service account has Full Control NTFS permissions.

Alternative or Verification Steps

• Re-run the snapshot view refresh manually using EXEC sp_refreshview and confirm no errors appear in SSMS or the ERRORLOG file.
• If the issue persists, drop and recreate the snapshot view entirely using the original CREATE VIEW script to eliminate any residual metadata corruption.

Troubleshooting Guide: Error 10652 – Refresh of Snapshot View Failed

Why This Error Happens

Error 10652 occurs when SQL Server attempts to call sp_refreshview or a similar refresh operation on an object that is not actually registered as an indexed (snapshot) view. This typically happens because the view definition does not include the SCHEMABINDING option, or because the underlying unique clustered index required for a snapshot view was dropped or was never created. In some cases, this error also surfaces when a standard view is mistakenly referenced in a maintenance script intended for materialized/indexed views.

Step-by-Step Resolution Guide

Step 1: Verify the View Type in SQL Server Management Studio (SSMS)

• Open SSMS and connect to your SQL Server 2025 instance.
• Navigate to Databases > [Your Database] > Views.
• Right-click the specific view (e.g., dbo.YourViewName) and select Script View as > CREATE To > New Query Editor Window.
• Check the script for the WITH SCHEMABINDING clause. If it is missing, the view cannot be a snapshot (indexed) view.

Step 2: Check for an Existing Unique Clustered Index

• Run the following T-SQL query to check if an index exists on the view:

SELECT i.name AS IndexName, i.type_desc 
FROM sys.indexes i 
JOIN sys.objects o ON i.object_id = o.object_id 
WHERE o.name = 'YourViewName';

• If the result set is empty, the view is a standard view, not a snapshot view, which is the root cause of the Error 10652.
• Ensure the query includes schema_binding checks to rule out system cache corruption.

Step 3: Recreate the View with Schemabinding and Index

• Drop the existing standard view using DROP VIEW dbo.YourViewName;.
• Recreate the view using the WITH SCHEMABINDING option:

CREATE VIEW dbo.YourViewName 
WITH SCHEMABINDING 
AS 
SELECT col1, col2 FROM dbo.BaseTable WHERE col1 IS NOT NULL;

• Create the required unique clustered index to convert it into a true snapshot/indexed view:

CREATE UNIQUE CLUSTERED INDEX IDX_View_Snapshot 
ON dbo.YourViewName (col1);

Step 4: Update the Refresh Script

• Modify your maintenance plan or Agent Job to ensure it is not calling REFRESH VIEW logic on standard, non-indexed views.
• Isolate the specific object names causing the error based on the %.*ls.%.*ls placeholder in the error log found in C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG.

Alternative or Verification Steps

• Verification: Re-run the specific stored procedure or Agent Job that triggered the error. If the index was created correctly in Step 3, the refresh should complete without throwing Error 10652.
• Alternative: If schema binding is not feasible for this view due to underlying table constraints, remove the view from the automated “snapshot refresh” job entirely and treat it as a standard view requiring no indexed maintenance.

Troubleshooting Guide: Error 10651 – Snapshot View Refresh Failure

Why This Error Happens

Error 10651 occurs when SQL Server’s indexed view or snapshot replication mechanism attempts to refresh a view that has been dropped, renamed, or never fully committed to the schema. This typically happens when the underlying view definition was removed manually via a DROP VIEW statement without updating the dependent replication or snapshot metadata, or when schema changes were replicated out of sync between the publisher and subscriber. In some cases, this is also caused by case-sensitivity mismatches or schema name changes that leave orphaned references in the sysobjects or snapshot agent history tables.

Step-by-Step Resolution Guide

Step 1: Verify the View Exists in the Database

• Open SQL Server Management Studio (SSMS) and connect to the target instance on your Windows Server 2025 or Windows 11 machine.
• Run the following query to check if the view is actually present:

SELECT * FROM sys.views WHERE name = 'YourViewName' AND SCHEMA_NAME(schema_id) = 'YourSchemaName';

• If no rows return, the view has been dropped or was never created successfully.

Step 2: Recreate the Missing View or Repair Schema Binding

• If the view is confirmed missing, restore it from your source control script or a recent backup.
• Navigate to your deployment scripts folder, typically located at C:\SQLScripts\Views\ or wherever your DBA team stores DDL scripts.
• Re-run the CREATE VIEW statement, ensuring the schema name matches exactly (SQL Server is case-sensitive in this context when collation settings enforce it):

CREATE VIEW [dbo].[YourViewName] AS SELECT ... ;

• If the view exists but replication metadata is stale, drop and reinitialize the subscription using the Replication Monitor.

Step 3: Reinitialize the Snapshot Agent

• Open SQL Server Management Studio and go to Replication > Local Publications.
• Right-click the affected publication and select Reinitialize All Subscriptions.
• Alternatively, run this from a query window to force a new snapshot:

EXEC sp_reinitsubscription;

• Restart the Snapshot Agent job via SQL Server Agent to regenerate the schema and data files.

Step 4: Validate Permissions on the View and Schema

• Confirm the SQL Server service account (often NT Service\MSSQLSERVER on Windows Server 2025) has SELECT and VIEW DEFINITION permissions on the schema.
• Use the following command to grant necessary rights if missing:

GRANT VIEW DEFINITION ON OBJECT::[dbo].[YourViewName] TO [YourServiceAccount];

Alternative or Verification Steps

• After applying the fix, manually trigger a snapshot refresh via SSMS and confirm no errors appear in the SQL Server Agent job history or the Windows Event Viewer under Applications and Services Logs > MSSQLSERVER.
• If the error persists, drop the entire publication and subscription, then rebuild replication from scratch using the Replication Wizard to eliminate any corrupted metadata references.

Troubleshooting Guide: Error 10650 – Snapshot Isolation Required for Indexed/Snapshot Views

Why This Error Happens

This error occurs because the database housing the snapshot view does not have the ALLOW_SNAPSHOT_ISOLATION database option enabled. SQL Server 2025 requires this row-versioning framework to be explicitly turned on at the database level before it can refresh or query views that rely on snapshot semantics. Without it, the engine has no mechanism to generate the consistent, point-in-time row versions needed to satisfy the refresh operation, causing the request to fail immediately.

Step-by-Step Resolution Guide

Step 1: Verify Current Database Isolation Settings

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

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

• Confirm that snapshot_isolation_state_desc returns OFF.

Step 2: Enable Snapshot Isolation on the Database

• Ensure no active transactions are blocking the schema change (snapshot isolation changes require an exclusive lock briefly).
• Execute the following T-SQL command, replacing the database name as needed:

ALTER DATABASE YourDatabaseName
SET ALLOW_SNAPSHOT_ISOLATION ON;

• If the command hangs, check for open transactions using sp_who2 or sys.dm_tran_active_transactions and resolve blocking sessions.

Step 3: Refresh the Snapshot View

• Navigate back to the view in question within SSMS Object Explorer.
• Right-click the view and select Refresh, or re-run the application process that triggered the original query.
• If using indexed views specifically, verify the view was created with WITH SCHEMABINDING and that the base tables also support snapshot isolation.

Step 4: Restart Dependent Services (If Applicable)

• If the application layer (e.g., a .NET service or reporting tool) cached the previous connection state, restart the relevant application pool or service on Windows Server 2025/Windows 11.
• Use services.msc to restart “SQL Server Agent” if scheduled jobs were failing due to this same isolation error.

Alternative or Verification Steps

• Verification: Re-run the sys.databases query from Step 1; the snapshot_isolation_state_desc column should now display ON. Attempt the view refresh again to confirm the error no longer appears.
• Alternative: If enabling ALLOW_SNAPSHOT_ISOLATION is not permitted due to performance policies on a high-transaction production server, consider rewriting the view logic to avoid snapshot-dependent operators, or isolate the refresh to a reporting replica/secondary database where snapshot isolation can be safely enabled.

Troubleshooting Guide: Error 10649 – Nonclustered Index Creation Conflict with SNAPSHOT_MATERIALIZATION

Why This Error Happens

This error occurs because SQL Server 2025 introduces SNAPSHOTMATERIALIZATION as a clustered index property designed to maintain point-in-time materialized row versions directly within the index structure. When this property is enabled, SQL Server restricts the creation of additional nonclustered indexes on the same table because the internal row-versioning metadata used by SNAPSHOTMATERIALIZATION is incompatible with standard nonclustered index pointer structures. Essentially, the engine cannot guarantee referential consistency between the materialized snapshot data and a nonclustered index’s key-lookup mechanism, so it blocks the operation outright to prevent silent data corruption.

Step-by-Step Resolution Guide

Step 1: Confirm the Clustered Index Configuration

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

  SELECT name, type_desc, is_snapshot_materialization_on 
  FROM sys.indexes 
  WHERE object_id = OBJECT_ID('YourSchema.YourTable');

• Verify that is_snapshot_materialization_on returns 1, confirming this is the root cause.

Step 2: Evaluate Whether SNAPSHOT_MATERIALIZATION Is Required

• Determine if the table genuinely needs point-in-time materialized versioning (common in temporal analytics or CDC-heavy workloads) or if it was enabled by default during a recent migration/upgrade.
• If not required, disable it using:

  ALTER INDEX [YourClusteredIndexName] ON [YourSchema.YourTable] 
  SET (SNAPSHOT_MATERIALIZATION = OFF);

• This operation may require an exclusive schema lock, so schedule it during a maintenance window on production systems.

Step 3: Recreate the Nonclustered Index

• Once SNAPSHOT_MATERIALIZATION is disabled, attempt the nonclustered index creation again:

  CREATE NONCLUSTERED INDEX [IX_YourIndexName] 
  ON [YourSchema.YourTable] (ColumnName);

• If the table is large, consider adding WITH (ONLINE = ON, MAXDOP = 4) to minimize blocking during creation on Windows Server 2025 environments with high concurrency.

Step 4: If Materialization Must Remain Enabled, Use a Filtered or Indexed View Alternative

• Create a filtered nonclustered index on a separate non-materialized table copy, or use an indexed view to replicate the lookup performance without touching the clustered index directly.
• Example:

  CREATE VIEW vw_YourTable_Snapshot WITH SCHEMABINDING AS 
  SELECT ColumnName, ID FROM YourSchema.YourTable;
  GO
  CREATE UNIQUE CLUSTERED INDEX IX_View_Snapshot ON vw_YourTable_Snapshot (ID);

Alternative or Verification Steps

• After applying the fix, run DBCC CHECKTABLE('YourTable') to confirm structural integrity and ensure no residual versioning metadata conflicts remain.
• If disabling SNAPSHOT_MATERIALIZATION is not permitted due to compliance/retention policies, escalate to redesign the table using partitioned views or a secondary reporting table that mirrors data without the clustered snapshot property.

Troubleshooting Guide: Error 10648 – SNAPSHOT_MATERIALIZATION Cannot Be Set for Partitioned Index

Why This Error Happens

This error occurs because SQL Server 2025 does not support the SNAPSHOT_MATERIALIZATION option on indexes that are partitioned across multiple filegroups. The feature requires a single, contiguous storage structure to manage versioned snapshot data, and partitioning breaks that contiguity requirement. Attempting to apply this setting via a CREATE INDEX or ALTER INDEX statement on a partitioned table/index will cause the engine to reject the operation and raise Error 10648.

Step-by-Step Resolution Guide

Step 1: Identify the Partitioned Index Causing the Conflict

• Run the following query to confirm the index in question is partitioned:
• SELECT i.name, p.partition_number, p.index_id FROM sys.indexes i JOIN sys.partitions p ON i.object_id = p.object_id WHERE i.name = 'YourIndexName';
• If partition_number returns more than 1, the index is partitioned and incompatible with SNAPSHOT_MATERIALIZATION.
• Check the original DDL script for the WITH (SNAPSHOT_MATERIALIZATION = ON) clause.

Step 2: Remove or Modify the SNAPSHOT_MATERIALIZATION Clause

• Edit the CREATE INDEX or ALTER INDEX T-SQL statement to remove the SNAPSHOT_MATERIALIZATION = ON argument entirely.
• If snapshot-style read consistency is still required, consider enabling READ_COMMITTED_SNAPSHOT or ALLOW_SNAPSHOT_ISOLATION at the database level instead, as these do not conflict with partitioning.
• Re-run the modified script against the target database.

Step 3: Rebuild the Index Without Partitioning (If Materialization Is Mandatory)

• If SNAPSHOT_MATERIALIZATION is a hard requirement for this specific index, you must remove partitioning.
• Drop the existing partition scheme association for the index: DROP INDEX YourIndexName ON YourTable;
• Recreate the index on a single filegroup: CREATE INDEX YourIndexName ON YourTable(YourColumn) ON [PRIMARY] WITH (SNAPSHOT_MATERIALIZATION = ON);
• Note: This action consolidates data into one filegroup, so ensure you have sufficient disk space allocated prior to execution.

Step 4: Validate Server and Compatibility Settings

• Confirm the database compatibility level supports this feature using: SELECT compatibility_level FROM sys.databases WHERE name = 'YourDBName';
• Ensure the compatibility level is set to 170 (SQL Server 2025) or higher.
• Restart the SQL Server service via services.msc on Windows Server 2025 if settings were recently changed and are not taking effect.

Alternative or Verification Steps

• To verify the fix, re-run the CREATE INDEX statement; a successful execution with no Error 10648 confirms the resolution.
• As an alternative, use sys.dm_db_index_physical_stats to check the fragmentation and partition status of the index to ensure the structure aligns with non-partitioned requirements before finalizing the script.

Troubleshooting Guide: Error 10647 – Index with SNAPSHOT_MATERIALIZATION Cannot Be Altered

Why This Error Happens

This error occurs because SQL Server 2025 blocks direct ALTER INDEX operations on indexes that are tied to SNAPSHOT_MATERIALIZATION, a feature reserved for system-managed materialized snapshot views and temporal consistency structures. The engine protects these indexes from manual schema changes because altering them outside of the materialization pipeline would corrupt the internal versioning metadata the engine relies on for point-in-time reads. This typically happens when a DBA attempts to run a standard ALTER INDEX ... REBUILD or ALTER INDEX ... DISABLE statement against an index that was auto-generated by a snapshot materialization process rather than created manually.

Step-by-Step Resolution Guide

Step 1: Identify the Index and Its Materialization Status

• Run the following query to confirm the index is flagged for snapshot materialization:

  SELECT i.name, i.type_desc, i.is_snapshot_materialized 
  FROM sys.indexes i 
  WHERE i.object_id = OBJECT_ID('YourTableName');

• Check the is_snapshot_materialized column; a value of 1 confirms this is the root cause.
• Note the exact index name and parent table reported in the Error 10647 message text.

Step 2: Drop and Recreate via the Materialization Pipeline Instead of ALTER

• Do not attempt ALTER INDEX directly; instead, disable the snapshot materialization feature on the object first.
• Execute: EXEC sp_disable_snapshot_materialization @object_name = 'YourTableName';
• Once disabled, the index converts to a standard index and becomes eligible for normal ALTER INDEX ... REBUILD or ALTER INDEX ... REORGANIZE commands.
• Re-enable materialization afterward if required using EXEC sp_enable_snapshot_materialization @object_name = 'YourTableName'; to regenerate the index automatically with your schema changes applied.

Step 3: Apply Changes Through Database Engine Tuning Advisor or T-SQL Wrapper

• If direct disabling is not permitted due to replication or Always On Availability Group constraints, use SQL Server Management Studio (SSMS) 21’s built-in “Materialized Index Maintenance” wizard instead of raw T-SQL.
• Navigate to Object Explorer > Databases > YourDB > Tables > YourTable > Indexes, right-click the flagged index, and select Manage Snapshot Materialization.
• This GUI path invokes the correct internal stored procedures that safely rebuild the index without violating the SNAPSHOT_MATERIALIZATION lock.

Step 4: Restart the SQL Server Service to Clear Cached Metadata

• After making changes, restart the instance to ensure the query optimizer and metadata cache fully refresh, which is a known requirement in SQL Server 2025 builds on Windows Server 2025.
• Open PowerShell as Administrator and run:

  Restart-Service -Name 'MSSQLSERVER' -Force

• For named instances, substitute MSSQLSERVER with MSSQL$InstanceName.

Alternative or Verification Steps

• Re-run the original ALTER INDEX statement after completing Step 2; it should now execute without raising Error 10647.
• If the error persists, check Event Viewer > Applications and Services Logs > MSSQLSERVER for Event ID 10647 details, and verify no active Always On Availability Group replica is holding a lock on the materialized index by running sys.dm_hadr_database_replica_states.

Troubleshooting Guide: Error 10646 – Index Cannot Be Dropped Using DROPEXISTING on SNAPSHOTMATERIALIZATION View

Why This Error Happens

This error occurs because SQL Server 2025 prohibits using the DROP_EXISTING clause to rebuild an index on an indexed view that has SNAPSHOT_MATERIALIZATION enabled. The restriction exists because dropping and recreating the clustered index in a single atomic operation could invalidate the snapshot’s internal consistency metadata, which SQL Server relies on to serve temporally consistent reads. Essentially, the engine blocks this shortcut to prevent corruption of the materialized snapshot state, forcing an explicit drop-then-create sequence instead.

Step-by-Step Resolution Guide

Step 1: Identify the Affected Index and View

• Run the following query to confirm the index and view names from the error message:
• SELECT i.name AS IndexName, v.name AS ViewName FROM sys.indexes i JOIN sys.views v ON i.object_id = v.object_id WHERE v.name = 'YourViewName';
• Confirm SNAPSHOT_MATERIALIZATION is active by checking sys.views or the view’s definition with sp_helptext.

Step 2: Explicitly Drop the Existing Index

• Instead of using CREATE INDEX ... WITH (DROP_EXISTING = ON), issue a standalone DROP INDEX statement first.
• Example: DROP INDEX [IX_YourIndexName] ON [dbo].[YourViewName];
• Verify the index no longer appears in sys.indexes for that object.

Step 3: Recreate the Index Without DROP_EXISTING

• Create the new (or modified) index as a separate statement, omitting the DROP_EXISTING option entirely.
• Example: CREATE UNIQUE CLUSTERED INDEX [IX_YourIndexName] ON [dbo].[YourViewName] (ColumnA, ColumnB);
• Ensure SET ARITHABORT ON, SET NUMERIC_ROUNDABORT OFF, and other required session settings for indexed views are configured before running this step.

Step 4: Script and Automate Future Maintenance

• Update any deployment scripts, DACPAC definitions, or SSDT projects that previously used DROP_EXISTING = ON for this view’s index.
• Replace them with the two-step drop/create pattern to prevent recurrence during future deployments or index maintenance jobs.

Alternative or Verification Steps

• Verify the fix by querying sys.indexes and sys.dm_db_index_physical_stats to confirm the index exists, is healthy, and matches the expected definition.
• If the view no longer requires snapshot consistency, consider disabling SNAPSHOT_MATERIALIZATION on the view (via ALTER VIEW) before performing index maintenance, then re-enable it afterward if needed.

Troubleshooting Guide: SQL Server Error 10645 — Resumable Operation Already in Progress

Why This Error Happens

This warning occurs because SQL Server detects that a resumable index operation (such as CREATE INDEX, ALTER INDEX REBUILD, or ALTER INDEX REORGANIZE) with identical options was already initiated on the same index and is still in a paused or incomplete state. Rather than starting a new operation, SQL Server’s resumable DDL engine automatically resumes the existing one to prevent duplicate metadata entries and index corruption. This typically happens when a previous session was terminated, disconnected, or failed without explicitly pausing or aborting the operation, leaving it in limbo within sys.index_resumable_operations.

Step-by-Step Resolution Guide

Step 1: Identify the Existing Resumable Operation

• Open SQL Server Management Studio (SSMS) or Azure Data Studio connected to the target instance.
• Run the following query to list all active resumable operations:

SELECT name, object_id, index_id, state_desc, percent_complete, start_time, last_pause_time
FROM sys.index_resumable_operations;

• Note the index_id, object_id, and state_desc (should show PAUSED or RUNNING) for the conflicting index.

Step 2: Decide Whether to Resume or Abort the Operation

• If the original operation needs to be completed, explicitly resume it rather than issuing a new command:

ALTER INDEX [IndexName] ON [SchemaName].[TableName] RESUME;

• If the operation is no longer needed or was left in a bad state, abort it cleanly:

ALTER INDEX [IndexName] ON [SchemaName].[TableName] ABORT;

• Verify no other sessions or jobs (via SQL Server Agent) are concurrently targeting the same index.

Step 3: Clear Orphaned Resumable Index Metadata

• If ABORT does not clear the entry, check for blocking sessions using:

SELECT * FROM sys.dm_exec_requests WHERE command LIKE '%INDEX%';

• Kill any orphaned session IDs (SPIDs) holding locks on the index using KILL <SPID>.
• Re-run the sys.index_resumable_operations query to confirm the entry has been removed.

Step 4: Restart the Index Operation with Correct Options

• Once the old operation is cleared, re-issue the original CREATE INDEX or ALTER INDEX statement with the RESUMABLE = ON option and matching MAXDOP and ONLINE settings to avoid future mismatches.
• Example:

ALTER INDEX [IX_Example] ON [dbo].[LargeTable]
REBUILD WITH (ONLINE = ON, RESUMABLE = ON, MAX_DURATION = 60 MINUTES);

Alternative or Verification Steps

• Confirm resolution by re-querying sys.index_resumable_operations — the table should return no rows for the affected index, and the rebuild/create operation should complete without the warning.
• If the issue persists after aborting, restart the SQL Server service (via Services.msc on Windows Server 2025/Windows 11) to clear any cached resumable operation metadata that failed to release properly.

Troubleshooting Guide: SQL Server Error 10644 – Resumable Index Operation Fails on Computed Column

Why This Error Happens

This error occurs because resumable online index operations (ALTER INDEX ... RESUME or CREATE INDEX ... WITH (RESUMABLE = ON)) do not support indexes where a computed column is used as a key column or as the partitioning column. SQL Server’s resumable DDL engine relies on persisting intermediate row states, and computed columns—especially non-persisted ones—cannot be reliably checkpointed mid-operation because their values are derived at query time rather than stored. This is a design limitation in SQL Server 2025, not a corruption or permissions issue, and it will consistently trigger Error 10644 whenever a resumable build/rebuild is attempted against such a schema.

Step-by-Step Resolution Guide

Step 1: Confirm the Index Definition and Computed Column Involvement

• Run sp_helpindex 'YourTableName' or query sys.index_columns joined with sys.computed_columns to identify which index key or partitioning column is computed.
• Check whether the computed column is marked PERSISTED using:
  SELECT name, is_persisted, is_computed FROM sys.computed_columns WHERE object_id = OBJECT_ID('YourTableName');
• Note the exact index name referenced in the error message (%.*ls).

Step 2: Rebuild the Index Without the RESUMABLE Option

• Drop and recreate, or rebuild, the index using a standard (non-resumable) operation since resumable DDL is unsupported for this schema pattern:
  ALTER INDEX [YourIndexName] ON [dbo].[YourTableName] REBUILD WITH (ONLINE = ON);
• Omit the RESUMABLE = ON clause entirely from your CREATE INDEX or ALTER INDEX statement.
• If the operation was already in a paused/failed resumable state, clean it up first with:
  ALTER INDEX [YourIndexName] ON [dbo].[YourTableName] ABORT;

Step 3: Redesign the Index to Avoid Computed Columns (If Resumability Is Required)

• If resumable rebuilds are a hard requirement (e.g., for minimizing downtime on large tables), persist the computed column’s value into a regular physical column via a trigger or scheduled update, then index the physical column instead.
• Alternatively, remove the computed column from the key/partitioning definition and instead use it only as an included (non-key) column, since resumable operations can tolerate computed columns in the INCLUDE clause in most cases.
• For partitioned tables, repartition using a non-computed column (e.g., a persisted/materialized integer or date column) as the partitioning key.

Step 4: Validate SQL Server Version and Cumulative Updates

• Check your build number with SELECT @@VERSION; and compare against Microsoft’s latest Cumulative Update (CU) release notes for SQL Server 2025, as resumable DDL behavior around computed columns has been refined in past CUs.
• Apply the latest CU via Windows Server 2025 Update management or manually from the Microsoft Download Center if your build is outdated.

Alternative or Verification Steps

• After rebuilding, verify success by querying sys.index_resumable_operations—the operation should no longer appear in a paused or failed state, and no Error 10644 should be logged in SQL Server Error Log or Windows Event Viewer.
• As a quick alternative check, run the rebuild in a test/staging environment first with WITH (ONLINE = ON, RESUMABLE = OFF) to confirm the index builds cleanly before promoting the change to production.

Troubleshooting Guide: Error 10643 – SNAPSHOT_MATERIALIZATION Applicability Conflict

Why This Error Happens

This error occurs because the SNAPSHOT_MATERIALIZATION option is being applied to an index on a base table rather than a clustered index on an indexed view. SQL Server 2025 restricts this specific option strictly to clustered indexes defined on views, as it relies on the materialized row data structure unique to indexed views to manage snapshot consistency. Attempting to set this property via a CREATE INDEX or ALTER INDEX statement on a standard table index triggers this validation failure.

Step-by-Step Resolution Guide

Step 1: Identify the Target Object Type

• Run a query against sys.objects to check the type_desc of the object referenced in the error message.
• Confirm whether the target is a USER_TABLE or a VIEW.
• Use the following T-SQL to verify: SELECT type_desc FROM sys.objects WHERE name = 'YourObjectName';

Step 2: Correct the Syntax for Base Tables

• If the target is a standard table, remove the SNAPSHOT_MATERIALIZATION clause entirely from your CREATE INDEX or ALTER INDEX script.
• Replace it with standard indexing options such as ONLINE = ON or DATA_COMPRESSION if performance tuning was the original goal.
• Save the modified script in SQL Server Management Studio (SSMS) 21 before re-execution.

Step 3: Apply the Option to an Indexed View (If Required)

• If snapshot materialization is actually required, create a VIEW first using the WITH SCHEMABINDING option.
• Ensure the view’s SELECT statement meets indexed view requirements (deterministic functions, no outer joins).
• Create the unique clustered index on the view, then apply the materialization flag: CREATE UNIQUE CLUSTERED INDEX IDX_View1 ON dbo.YourView (Column1) WITH (SNAPSHOT_MATERIALIZATION = ON);

Step 4: Validate Database Compatibility Level

• Ensure the database compatibility level supports this syntax by checking sys.databases.
• Run: SELECT compatibility_level FROM sys.databases WHERE name = 'YourDBName';
• If necessary, update it to the latest level supported by SQL Server 2025 using ALTER DATABASE YourDBName SET COMPATIBILITY_LEVEL = 170;

Alternative or Verification Steps

• Verification: Re-run the CREATE INDEX statement after moving the logic to a schema-bound view; the command should complete without Error 10643 appearing in the SSMS 21 “Messages” tab.
• Alternative: If materialization is not strictly necessary for your workload, omit the parameter entirely and rely on standard indexed view creation to achieve similar read-performance benefits.

Troubleshooting Guide: Error 10642 – SNAPSHOT_MATERIALIZATION Not Applicable

Why This Error Happens

This error occurs when the SNAPSHOT_MATERIALIZATION option is mistakenly applied to an index on a regular table rather than an indexed view. SQL Server 2025 restricts this specific option to indexes defined on views because the snapshot materialization feature relies on the deterministic, pre-computed nature of indexed views to maintain consistent point-in-time data snapshots. Attempting to set this property on a table index triggers a metadata validation failure during the CREATE INDEX or ALTER INDEX statement execution.

Step-by-Step Resolution Guide

Step 1: Identify the Target Object Type

• Run SELECT o.type_desc, o.name FROM sys.objects o JOIN sys.indexes i ON o.object_id = i.object_id WHERE i.name = 'YourIndexName'; to confirm whether the object is a USER_TABLE or a VIEW.
• Check the Transact-SQL script that generated the error to see if the SNAPSHOT_MATERIALIZATION = ON clause was copy-pasted from a view-indexing template.

Step 2: Remove the Invalid Syntax from Table Indexes

• Open SQL Server Management Studio (SSMS) 21 or Azure Data Studio and locate the specific CREATE INDEX statement causing the Event ID 10642 error.
• Strip the WITH (SNAPSHOT_MATERIALIZATION = ON) clause from the syntax if the target is a standard table.
• Re-run the script to create a standard clustered or non-clustered index without the materialization property.

Step 3: Correctly Apply Materialization to an Indexed View

• If the goal was actually to create a materialized snapshot, first create a standard VIEW using the WITH SCHEMABINDING option, as this is a mandatory prerequisite for indexed views in SQL Server 2025.
• Create a unique clustered index on the view itself (not the base table) using the syntax: CREATE UNIQUE CLUSTERED INDEX IX_View ON dbo.YourView (Column1) WITH (SNAPSHOT_MATERIALIZATION = ON);
• Ensure the underlying base tables referenced in the view do not use non-deterministic functions, as this will cause a secondary schema-binding error.

Step 4: Validate Database Compatibility Level

• Verify that the database compatibility level is set to 170 (or higher) for SQL Server 2025, as older compatibility levels might handle materialization syntax differently.
• Use the query ALTER DATABASE YourDatabaseName SET COMPATIBILITY_LEVEL = 170; if an update is required.

Alternative or Verification Steps

• Verify the fix by querying sys.indexes and checking the is_hypothetical and type_desc columns to confirm the index now exists without errors on the correct object type.
• If the error persists specifically on views, use DBCC CHECKCONSTRAINTS to ensure no underlying schema corruption is preventing the materialization property from binding correctly.

Troubleshooting Guide: Error 10641 – ALTER INDEX PAUSE Failed

Why This Error Happens

This error occurs when SQL Server cannot find an actively running resumable index operation to pause, typically because the operation already completed, failed, or was never started in a resumable state. It can also happen if the index operation was previously paused, aborted, or resumed by another session before your command executed, or if the index name/object name specified does not match an operation currently tracked in sys.index_resumable_operations. Additionally, this can occur due to a race condition in scripts where a PAUSE command is issued immediately after a CREATE/REBUILD/ALTER INDEX statement without confirming the operation is actually in a resumable, running state.

Step-by-Step Resolution Guide

Step 1: Verify the Index Operation Actually Exists

• Query sys.index_resumable_operations to confirm there is a row for the target index and table:

  SELECT name, object_id, index_id, state, state_desc, last_max_dop_used, start_time, percent_complete
  FROM sys.index_resumable_operations;

• Confirm the state_desc column shows RUNNING and not PAUSED, COMPLETED, or FAILED.
• If no rows are returned, the resumable operation does not exist or has already ended.

Step 2: Check for Premature or Duplicate Script Execution

• Review any automation, deployment pipelines, or maintenance scripts that issue ALTER INDEX ... RESUME or ALTER INDEX ... PAUSE back-to-back.
• Ensure the original CREATE INDEX ... WITH (RESUMABLE = ON) or ALTER INDEX ... REBUILD WITH (RESUMABLE = ON) statement completed initialization before issuing a PAUSE command.
• Add a short delay or a polling loop using WAITFOR DELAY '00:00:02' combined with the sys.index_resumable_operations check before attempting the PAUSE.

Step 3: Confirm the Correct Object and Index Names

• Double-check that the schema, table, and index names in your ALTER INDEX <index_name> ON <table_name> PAUSE statement exactly match the names returned in sys.index_resumable_operations.
• Watch for case sensitivity issues if your instance is using a case-sensitive collation.
• Re-run the query from Step 1 immediately before executing PAUSE to avoid acting on stale metadata.

Step 4: Check SQL Server Error Log and Extended Events

• Open SQL Server Management Studio (SSMS) and navigate to Management > SQL Server Logs to check if the resumable operation was aborted due to an error (e.g., disk space, lock timeout, or transaction log full).
• On Windows Server 2025/Windows 11, you can also check logs directly at:

  C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG

• Look for entries indicating the resumable index build completed, was cancelled, or encountered a fatal error prior to your PAUSE attempt.

Step 5: Restart the Resumable Operation if Necessary

• If the operation has already completed or failed, simply re-issue the original CREATE INDEX or ALTER INDEX ... REBUILD WITH (RESUMABLE = ON) command to start a fresh resumable operation.
• Only attempt PAUSE once you’ve confirmed via Step 1 that the state is RUNNING.

Alternative or Verification Steps

• After applying the fix, re-run SELECT * FROM sys.index_resumable_operations; and confirm the state_desc shows RUNNING before issuing the PAUSE command again — it should now succeed without Error 10641.
• As an alternative, if resumable pause/resume is not strictly required, consider letting the index operation run to completion or use ALTER INDEX ... REBUILD WITH (ONLINE = ON) without the resumable clause to avoid managing pause state manually.

Troubleshooting Guide: SQL Server Error 10640 — “Ignore in Optimization” Index Property Restriction

Why This Error Happens

This error occurs because the IGNORE_DUP_KEY or related “Ignore in Optimization” index property is being applied to an index on a table type that does not support it, such as a memory-optimized table, a system table, or an external/temporal table variant. In SQL Server 2025, this property is strictly validated against the storage engine type during index creation or alteration, and it is only compatible with indexes on user-defined, disk-based tables. The root cause is almost always a Transact-SQL script, migration tool, or ORM-generated schema that attempts to apply disk-based indexing semantics to a memory-optimized (In-Memory OLTP) table or another unsupported object type.

Step-by-Step Resolution Guide

Step 1: Identify the Target Table and Index Type

• Run the following query to determine if the table is memory-optimized:
• SELECT name, is_memory_optimized FROM sys.tables WHERE name = 'YourTableName';
• Check the value returned in is_memory_optimized. A value of 1 confirms the table is memory-optimized and is the source of the conflict.
• Cross-reference the specific index mentioned in the error message using sys.indexes to confirm which index property is misconfigured.

Step 2: Remove the Unsupported Index Property from the Script

• Open the DDL script (CREATE INDEX or ALTER INDEX statement) that is triggering Error 10640.
• Locate the WITH (IGNORE_DUP_KEY = ON) or equivalent “Ignore in Optimization” clause.
• Delete or comment out this clause specifically for indexes tied to memory-optimized tables, since the In-Memory OLTP engine handles duplicate key checking differently and does not support this legacy disk-based optimization flag.
• Re-run the corrected script against the target database.

Step 3: Correct Settings via SQL Server Management Studio (SSMS)

• Navigate to Object Explorer > Databases > [Your Database] > Tables > [Your Table] > Indexes.
• Right-click the specific index referenced in the error and select Properties.
• Go to the Options page and locate the Ignore duplicate keys setting.
• Ensure this is set to False if the underlying table is memory-optimized, then click OK to apply changes.

Step 4: Validate Migration or ORM Scripts

• If this error surfaced during a migration (e.g., from SQL Server 2019/2022 to 2025) or via an Entity Framework/ORM code-first migration, inspect the auto-generated migration file.
• Search for index definitions attached to models that map to memory-optimized tables and strip out the IGNORE_DUP_KEY attribute or fluent API configuration before re-applying the migration.

Alternative or Verification Steps

• Verify the fix: Re-execute the original CREATE INDEX or ALTER INDEX statement; if Error 10640 no longer appears and the index builds successfully, query sys.indexes to confirm the ignore_dup_key column now reads 0.
• Alternative solution: If the duplicate-key prevention logic is still required for the memory-optimized table, enforce it using a UNIQUE constraint at the table definition level instead, as this is natively supported by In-Memory OLTP and does not rely on the disk-based “Ignore in Optimization” engine feature.

Troubleshooting Guide: Error 10639 – Resumable Index Operation Fails on Timestamp Column

Why This Error Happens

This error occurs because resumable online index operations in SQL Server 2025 do not support indexes that contain a timestamp (also known as rowversion) data type column as a key column. The resumable DDL feature requires the ability to track and persist operation state across pause/resume cycles, and since timestamp values auto-increment on every row modification, SQL Server cannot guarantee a consistent checkpoint for the index build. This is a design limitation rather than a corruption or configuration issue, and it typically surfaces when a DBA attempts to run CREATE INDEX or ALTER INDEX ... REBUILD with the RESUMABLE = ON option against a table schema that was likely migrated from an older SQL Server version still using legacy timestamp columns.

Step-by-Step Resolution Guide

Step 1: Identify the Offending Column and Index

• Run the following query to locate the specific timestamp/rowversion column referenced in the error message:

  SELECT c.name AS ColumnName, t.name AS DataType, i.name AS IndexName
  FROM sys.columns c
  JOIN sys.types t ON c.user_type_id = t.user_type_id
  JOIN sys.index_columns ic ON ic.object_id = c.object_id AND ic.column_id = c.column_id
  JOIN sys.indexes i ON i.object_id = ic.object_id AND i.index_id = ic.index_id
  WHERE t.name = 'timestamp';

• Confirm whether the column is being used as a key column (not just an included column) in the index definition.

Step 2: Disable the RESUMABLE Option for This Specific Index

• Rewrite the T-SQL statement to remove RESUMABLE = ON from the WITH clause.
• Execute the operation as a standard, non-resumable rebuild instead:

  ALTER INDEX [YourIndexName] ON [dbo].[YourTable]
  REBUILD WITH (ONLINE = ON, MAXDOP = 4);

• Note: ONLINE = ON is still supported for timestamp columns; only the RESUMABLE flag triggers Error 10639.

Step 3: Modify the Index Schema (If Resumability is Mandatory)

• If you specifically need resumable capability for this maintenance window, remove the timestamp column from the key column list.
• Move the timestamp column to the INCLUDE clause instead, since included columns are not subject to this restriction:

  CREATE INDEX [IX_NewIndex] ON [dbo].[YourTable] (OrderID, CustomerID)
  INCLUDE (RowVersionCol)
  WITH (RESUMABLE = ON);

• Validate this change does not break application logic that relies on the sort order of the timestamp column.

Step 4: Check for Legacy Compatibility Mode Conflicts

• Navigate to SQL Server Management Studio (SSMS) and check the Database Properties under the Options tab.
• Verify the Compatibility Level is set appropriately for SQL Server 2025 features; ensure it is not forcing legacy syntax parsing that might obscure the resumable logic:
• Path: Database > Right-click > Properties > Options > Compatibility Level.

Alternative or Verification Steps

• Verification: After applying the fix, re-run the ALTER INDEX command and check the sys.index_resumable_operations dynamic management view to confirm the operation state changes from running to completed without throwing Error 10639.
• Alternative: If modifying the index schema is not feasible, consider migrating the timestamp column to the newer rowversion syntax explicitly (functionally identical but clarifies intent) or evaluate if a datetime2 audit column can replace the timestamp dependency entirely for future-proofing.

Troubleshooting Guide: Error 10638 – No Pending Resumable Index Operation

Why This Error Happens

Error 10638 occurs when SQL Server 2025 attempts to RESUME, PAUSE, or ABORT a resumable index operation that does not actually exist in a paused or suspended state for the specified index. This typically happens because the original ALTER INDEX ... RESUMABLE = ON operation already completed successfully, was previously aborted, or never started due to a prior connection failure. It can also occur if the index metadata was altered (rebuilt, dropped, or recreated) between the time the resumable operation was initiated and when the resume command was issued, causing SQL Server to lose track of the operation’s state in sys.index_resumable_operations.

Step-by-Step Resolution Guide

Step 1: Verify the Current State of Resumable Operations

• Run the following query to check if any resumable index operations are actually pending on the target database:
• SELECT name, object_id, index_id, state_desc, percent_complete FROM sys.index_resumable_operations;
• Confirm whether the index name in the error message matches an entry with state_desc = 'PAUSED'.
• If no rows are returned, the operation has already completed, failed silently, or was never properly registered.

Step 2: Check SQL Server Error Logs for Related Events

• Navigate to the SQL Server log directory, typically located at:
• C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG
• Search for entries referencing the specific index name and any prior ALTER INDEX statements to confirm whether the resumable job completed or crashed mid-operation.
• Cross-reference timestamps with your maintenance job history in SQL Server Agent (msdb.dbo.sysjobhistory) if the index operation was triggered by an automated job.

Step 3: Re-initiate the Index Operation Correctly

• Since no pending operation exists, do not attempt to RESUME the index again.
• Instead, restart the operation fresh using the RESUMABLE = ON option:
• ALTER INDEX [YourIndexName] ON [dbo].[YourTableName] REBUILD WITH (ONLINE = ON, RESUMABLE = ON, MAX_DURATION = 60 MINUTES);
• If you intended to abort a stuck operation instead, use ALTER INDEX ... ABORT only after confirming via Step 1 that a state actually exists.

Step 4: Clear Orphaned Metadata (If Applicable)

• If sys.index_resumable_operations shows a stale or corrupted entry that won’t clear, restart the SQL Server service to flush in-memory operation tracking:
• Open Services.msc on Windows Server 2025, locate SQL Server (MSSQLSERVER), and select Restart.
• Re-run the Step 1 query after the service restarts to confirm the stale entry is gone before retrying the ALTER INDEX command.

Alternative or Verification Steps

• Confirm the fix by re-running SELECT * FROM sys.index_resumable_operations; immediately after starting a new resumable rebuild — a valid IN_PROGRESS or PAUSED state should now appear.
• If the error persists despite no visible pending operations, use DBCC CHECKDB on the affected database to rule out underlying index or metadata corruption as a root cause.

Troubleshooting Guide: SQL Server Error 10637 – Resumable Index Rebuild Conflict

Why This Error Happens

This error occurs when you attempt to perform a schema-altering operation (such as DROP INDEX, ALTER TABLE, or a new ALTER INDEX REBUILD) on a table or index that already has a paused or active resumable index rebuild operation in progress. SQL Server 2025 locks the metadata state of the index to preserve the resumable operation’s progress, preventing conflicting DDL changes until that operation is explicitly resumed, completed, or aborted. This is a safeguard mechanism and not a corruption issue, but it will block your transaction until the stale operation is cleared from sys.index_resumable_operations.

Step-by-Step Resolution Guide

Step 1: Identify the Blocking Resumable Operation

• Open SQL Server Management Studio (SSMS) or Azure Data Studio connected to the target instance.
• Run the following query to find the conflicting operation:
  SELECT name, object_id, index_id, state_desc, percent_complete FROM sys.index_resumable_operations;
• Note the object_id and index_id matching the error message you received.

Step 2: Decide to Resume or Abort the Operation

• If the rebuild should continue, resume it using: ALTER INDEX [IndexName] ON [SchemaName].[TableName] RESUME;
• If the operation is stale, abandoned, or no longer needed, abort it instead using: ALTER INDEX [IndexName] ON [SchemaName].[TableName] ABORT;
• Verify there are no active transactions holding locks on the tempdb version store, as resumable rebuilds rely on this for row versioning.

Step 3: Clear Orphaned Metadata (If Resume/Abort Fails)

• If the standard RESUME or ABORT commands fail due to a crashed session, restart the SQL Server service via services.msc or PowerShell: Restart-Service -Name 'MSSQLSERVER' -Force
• Upon restart, SQL Server 2025 will automatically roll back or mark the resumable index state as PAUSED, allowing you to issue the ABORT command cleanly.
• Re-run your original DDL command (e.g., the DROP or ALTER statement) once the index state is cleared.

Step 4: Check for Disk Space and I/O Constraints

• Resumable rebuilds often pause automatically if the underlying volume runs out of space.
• Check the drive hosting your .mdf and .ldf files in File Explorer or via Get-PSDrive in PowerShell to ensure adequate free space (minimum 20% recommended) before resuming large rebuilds.

Alternative or Verification Steps

• Verification: Re-query sys.index_resumable_operations. The table should return zero rows for the specific object_id that previously threw the error, confirming the lock has been released.
• Alternative: If the index is non-critical and heavily fragmented, simply drop and recreate it using a non-resumable, offline rebuild (ALTER INDEX ALL ON TableName REBUILD WITH (ONLINE = OFF)) to bypass the resumable metadata entirely.

Troubleshooting Guide: SQL Server Error 10636 — “Ignore in Optimization” Setting Conflict

Why This Error Happens

This error occurs because the IGNORE_IN_OPTIMIZATION property is being applied to an index type that does not support it, such as a clustered index, XML index, spatial index, or full-text index. SQL Server 2025 restricts this specific optimization hint to non-clustered B-tree indexes and columnstore indexes only, as these are the only structures where the query optimizer can safely disregard the index during cost-based plan selection. The conflict typically arises from outdated T-SQL scripts, migration tooling, or ORM-generated DDL that attempts to apply this property universally across all index types without checking compatibility.

Step-by-Step Resolution Guide

Step 1: Identify the Offending Index and Its Type

• Run the following query to pull index metadata for the affected table:

  SELECT name, type_desc, is_hypothetical 
  FROM sys.indexes 
  WHERE object_id = OBJECT_ID('YourSchema.YourTable');

• Confirm whether the type_desc returns CLUSTERED, XML, SPATIAL, or FULLTEXT — these are the types that will trigger Error 10636.
• Cross-reference the index name from the error message with the output to pinpoint the exact object.

Step 2: Correct the T-SQL Script or ORM Mapping

• Remove or comment out the IGNORE_IN_OPTIMIZATION = ON clause from the CREATE INDEX or ALTER INDEX statement targeting the incompatible index.
• If using Entity Framework, Dapper, or a similar ORM, check the migration file (e.g., Migrations/202505_AddIndex.cs) for auto-generated index attributes and manually override the fluent API configuration.
• For manual scripts, locate the statement in SSMS (SQL Server Management Studio 21) under Object Explorer > Databases > YourDB > Tables > Indexes, right-click the index, and select Script Index as > ALTER To > New Query Editor Window to inspect the current definition.

Step 3: Recreate the Index with a Supported Type (If Optimization Hint Is Required)

• If you need the IGNORE_IN_OPTIMIZATION behavior, drop the unsupported index and recreate it as a non-clustered B-tree index instead:

  DROP INDEX IX_YourIndex ON YourSchema.YourTable;
  CREATE NONCLUSTERED INDEX IX_YourIndex 
  ON YourSchema.YourTable (ColumnName)
  WITH (IGNORE_IN_OPTIMIZATION = ON);

• Ensure this change doesn’t break existing clustering keys or foreign key dependencies by checking sys.foreign_keys first.

Step 4: Apply the Corrected Script and Monitor Execution

• Execute the revised script in SSMS or via sqlcmd from PowerShell:

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

• Watch the Messages tab in SSMS for confirmation of successful execution with no Error 10636 recurrence.

Alternative or Verification Steps

• Verify the fix by querying sys.indexes again and confirming the ignore_in_optimization column (new in SQL Server 2025 catalog views) returns 1 only for non-clustered B-tree or columnstore index types.
• If the error persists after recreating the index, check Windows Event Viewer (Event Viewer > Applications and Services Logs > MSSQLSERVER) for deeper engine-level conflicts, and consider running DBCC CHECKDB to rule out metadata corruption as a contributing factor.

Troubleshooting Guide: SQL Server Error 10635 – Online Index Operation Failure on LOB Columns

Why This Error Happens

This error occurs because SQL Server’s online index operation (ONLINE = ON) feature has architectural limitations when dealing with Large Object (LOB) data types such as VARCHAR(MAX), NVARCHAR(MAX), VARBINARY(MAX), XML, or legacy types like TEXT, NTEXT, and IMAGE. The database engine cannot maintain row versioning and concurrent read/write access for LOB pointers during an online rebuild, as the row-versioning mechanism used for concurrency does not fully cover the pointer structures for these large data chunks. This is a hard-coded engine restriction rather than a configuration or permissions error, meaning it occurs regardless of server health or user privileges.

Step-by-Step Resolution Guide

Step 1: Identify the Offending Column and Index

• Open SSMS (SQL Server Management Studio) 21 connected to your SQL Server 2025 instance.
• Run the following query to find which index and column is triggering the conflict:
  SELECT i.name AS IndexName, c.name AS ColumnName, t.name AS DataType FROM sys.indexes i JOIN sys.index_columns ic ON i.object_id = ic.object_id AND i.index_id = ic.index_id JOIN sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id JOIN sys.types t ON c.user_type_id = t.user_type_id WHERE t.name IN ('text', 'ntext', 'image', 'xml', 'varchar', 'nvarchar', 'varbinary') AND i.object_id = OBJECT_ID('YourTableName');
• Confirm if the data type listed matches a MAX or LOB-based type.

Step 2: Switch the Operation to OFFLINE Mode

• Since LOB columns are not fully supported in online mode, you must modify your T-SQL script to disable the online flag.
• Navigate to your index creation or rebuild script, typically found in your deployment .sql files or SSMS query window.
• Change the syntax from WITH (ONLINE = ON) to WITH (ONLINE = OFF).
• Example command: ALTER INDEX [YourIndexName] ON [dbo].[YourTableName] REBUILD WITH (ONLINE = OFF, SORT_IN_TEMPDB = ON);

Step 3: Schedule the Maintenance During Low-Traffic Windows

• Since the operation must now run offline, it will acquire a Schema Modification (Sch-M) lock, blocking user access to the table.
• Use SQL Server Agent on Windows Server 2025 to schedule this job during a maintenance window (e.g., 2:00 AM – 4:00 AM).
• Navigate to SQL Server Agent > Jobs > New Job in SSMS to automate this specific ALTER INDEX script.

Step 4: Evaluate Data Type Compatibility (Optional Long-Term Fix)

• If frequent online rebuilds are a business requirement, evaluate if the LOB data can be refactored.
• Check if VARCHAR(MAX) data is actually small enough to fit into a standard VARCHAR(8000) or NVARCHAR(4000), which supports online operations.
• Modify the schema using ALTER TABLE YourTableName ALTER COLUMN YourColumnName VARCHAR(8000); if business logic allows for the data truncation/change.

Alternative or Verification Steps

• Verification: After running the index rebuild with ONLINE = OFF, check the sys.indexes catalog view to ensure the is_disabled flag is set to 0 and verify the operation completed without further 10635 errors in the SQL Server Error Log located at C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\.
• Alternative: If downtime is strictly prohibited, consider using the RESUMABLE = ON option in conjunction with ONLINE = OFF for very large tables; this allows you to pause and resume the offline index build without restarting the entire process, minimizing the impact of the required lock.

Why This Error Happens

Error 10634 is raised by SQL Server’s auditing and Extended Events subsystem when a DDL (Data Definition Language) statement—such as CREATE, ALTER, or DROP—is captured and logged against a database object. This is typically not a fatal error but an informational audit trace message triggered because a Server Audit Specification, DDL Trigger, or SQL Trace is actively monitoring schema-level changes. It commonly surfaces unexpectedly when audit configurations are duplicated, orphaned, or when audit logs are filling faster than expected due to verbose tracking policies on Windows Server 2025 / Windows 11 hosted instances.

Step-by-Step Resolution Guide

Step 1: Identify the Source Audit Specification

• Open SQL Server Management Studio (SSMS) and connect to the SQL Server 2025 instance.
• Run the following query to locate active audits capturing DDL events:

  SELECT * FROM sys.server_audits;
  SELECT * FROM sys.server_audit_specifications;
  SELECT * FROM sys.database_audit_specifications;

• Note the audit_guid and name columns tied to DDL-related audit_action_id values (e.g., SCHEMA_OBJECT_CHANGE_GROUP).

Step 2: Review and Adjust Audit or Trigger Scope

• Navigate to Object Explorer > Security > Audits in SSMS to inspect configured audit targets.
• If the message is coming from a DDL trigger instead, check for custom triggers via:

  SELECT * FROM sys.triggers WHERE parent_class = 0;

• Disable or scope down overly broad triggers/audits using:

  ALTER SERVER AUDIT [AuditName] WITH (STATE = OFF);

Step 3: Check File and Log Path Permissions

• Verify the audit log destination (File, Security Log, or Application Log) has proper write permissions.
• On Windows Server 2025, confirm the SQL Server service account has “Write” and “Modify” NTFS permissions on the audit folder, typically located at:
  C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\
• Restart the SQL Server service via services.msc or:

  Restart-Service -Name 'MSSQLSERVER'

Step 4: Clear and Reconfigure Audit Settings

• If the audit specification is corrupted or duplicated, drop and recreate it cleanly:

  DROP SERVER AUDIT SPECIFICATION [SpecName];
  DROP SERVER AUDIT [AuditName];

• Recreate using the SQL Server Configuration Manager or a fresh T-SQL script with properly scoped AUDIT_ACTION_GROUPS.

Alternative or Verification Steps

• After applying changes, generate a test DDL statement (e.g., CREATE TABLE TestAudit (ID INT);) and confirm Error 10634 no longer appears unexpectedly in the SQL Server Error Log or Windows Event Viewer.
• If the issue persists, use Extended Events (XEvents) instead of legacy SQL Trace/Audit objects, as it is the recommended, more stable auditing mechanism in SQL Server 2025 on Windows Server 2025/Windows 11 environments.

Troubleshooting Guide: SQL Server Error 10633

Why This Error Happens

Error 10633 occurs when SQL Server attempts an online index creation (or rebuild) operation on an index definition that is structurally invalid for online processing—most commonly because the index includes a computed column that references a user-defined CLR function, a LOB data type (XML, VARCHAR(MAX), NVARCHAR(MAX), VARBINARY(MAX)) in a version prior to full online support, or a spatial/full-text based column that cannot be maintained online. This can also surface when the table has concurrent schema-altering operations queued, or when the index is partitioned in a way incompatible with the ONLINE = ON option. Essentially, the SQL Server engine detects a mismatch between the requested online operation mode and the underlying index metadata constraints enforced by the Storage Engine.

Step-by-Step Resolution Guide

Step 1: Identify the Problematic Index Definition

• Run sp_helpindex 'YourTableName' or query sys.indexes and sys.index_columns to inspect the exact columns involved.
• Check if any column in the index is a computed column: SELECT name, is_computed FROM sys.columns WHERE object_id = OBJECT_ID('YourTableName');
• Confirm whether the computed column depends on a CLR function by checking sys.assembly_modules.

Step 2: Rebuild the Index Using OFFLINE Mode

• Open SQL Server Management Studio (SSMS) or use sqlcmd on Windows Server 2025.
• Execute the index creation with ONLINE = OFF as a workaround:

  CREATE INDEX IX_YourIndex ON dbo.YourTable (ColumnName) WITH (ONLINE = OFF);

• Schedule this operation during a maintenance window since OFFLINE mode locks the table.

Step 3: Modify or Remove Incompatible Computed Columns

• If the index relies on a CLR-based computed column, either persist the column (ALTER TABLE ... ADD ColumnName AS (...) PERSISTED) or remove it from the index key.
• For LOB-type columns, move them to an INCLUDE clause instead of the key columns list, if business logic permits.
• Re-test the online index creation after modification:

  CREATE INDEX IX_YourIndex ON dbo.YourTable (ColumnName) WITH (ONLINE = ON);

Step 4: Update SQL Server 2025 to the Latest Cumulative Update

• Navigate to Microsoft Update Catalog or use SQL Server Installation Center on Windows Server 2025.
• Install the latest Cumulative Update (CU) since several online indexing edge cases are patched incrementally.
• Restart the SQL Server service via services.msc or net stop MSSQLSERVER && net start MSSQLSERVER.

Alternative or Verification Steps

• Verify the fix by querying sys.dm_exec_requests during index creation to confirm no blocking or error is thrown, and check sys.indexes to confirm the index status is is_disabled = 0.
• If online creation still fails, use OFFLINE index creation permanently for this specific index and document it as a known limitation in your database schema change log.

Why This Error Happens

Error 10629 occurs when you attempt to use a distribution key column (for a hash-distributed or round-robin table in SQL Server’s scale-out/partitioned architecture) with a data type that is not supported for hashing, such as text, ntext, image, XML, CLR user-defined types, or large object (LOB) types like varchar(max), nvarchar(max), or varbinary(max). The SQL Server query optimizer cannot compute a deterministic hash value for these variable, unbounded, or non-comparable data types, which is a strict requirement for distributing rows across nodes. This is a schema-design limitation rather than a permissions or connectivity issue.

Step-by-Step Resolution Guide

Step 1: Identify the Offending Column and Data Type

• Open SQL Server Management Studio (SSMS) and run sp_help 'YourTableName' to list all column data types.
• Cross-reference the column named in the error message (%.*ls) against the list of unsupported types: text, ntext, image, varchar(max), nvarchar(max), varbinary(max), xml, geography, geometry, and CLR types.
• Confirm this column is currently assigned as part of the DISTRIBUTION = HASH(...) clause in your CREATE TABLE or CREATE INDEX statement.

Step 2: Modify the Table Schema to Use a Supported Key

• Choose a different, already-supported integer or fixed-length column (e.g., INT, BIGINT, UNIQUEIDENTIFIER, or fixed CHAR(n)) as your new distribution key.
• If no suitable column exists, create a new surrogate key column:

  ALTER TABLE YourTableName ADD DistKeyID INT NOT NULL DEFAULT 0;

• Rebuild the table using CREATE TABLE AS SELECT (CTAS) to redefine the distribution:

  CREATE TABLE YourTableName_New
  WITH (DISTRIBUTION = HASH(DistKeyID))
  AS SELECT * FROM YourTableName;

Step 3: Convert or Truncate the Incompatible Data Type

• If the column must remain the distribution key, cast the LOB type down to a supported bounded type, such as converting VARCHAR(MAX) to VARCHAR(900) or smaller.
• Use the CONVERT() or CAST() function during the CTAS operation:

  SELECT CAST(LargeTextColumn AS VARCHAR(100)) AS LargeTextColumn_Short

• Validate that the truncated length still provides sufficient uniqueness for even data distribution across nodes.

Step 4: Apply the Corrected Index Definition

• Drop the old, failing index or table definition using DROP TABLE or DROP INDEX.
• Re-run the CREATE INDEX or CREATE TABLE command with the updated distribution key column.
• Rename the new table back to the original name using sp_rename once validated.

Alternative or Verification Steps

• Verify the fix by querying sys.pdw_table_distribution_properties (or the relevant DMV in SQL Server 2025) to confirm the new distribution column is a supported data type.
• If restructuring the table is not immediately feasible, use a ROUND_ROBIN distribution strategy instead of HASH as a temporary workaround, since it does not require a key column evaluation.

Troubleshooting Guide: SQL Server Error 10623 – Filtered Statistics Creation Failure

Why This Error Happens

This error occurs when CREATE STATISTICS with a WHERE clause (filtered statistics) is attempted against an object that SQL Server 2025 does not classify as a standard user table, such as a view, system table, external table, or a memory-optimized table in certain configurations. Filtered statistics rely on a physical row-level filter predicate that can only be evaluated and persisted against base user tables, so the Query Optimizer rejects the operation on any other object type. This frequently happens when scripts are auto-generated or migrated from another database and the target object type was changed (e.g., a table was converted to a view) without updating the statistics creation scripts.

Step-by-Step Resolution Guide

Step 1: Identify the Target Object Type

• Run SELECT type, type_desc FROM sys.objects WHERE object_id = OBJECT_ID('YourObjectName') to confirm whether the object is a U (user table), V (view), or another type.
• Check if the object is a synonym pointing to a view or external table using sys.synonyms.
• Confirm you are not targeting a system table under the sys schema, as these are explicitly excluded.

Step 2: Correct the CREATE STATISTICS Statement

• If the target is a view, remove the filtered predicate (WHERE clause) and create standard statistics instead, or redirect the statistics to the underlying base table(s) referenced by the view.
• Update the Transact-SQL script: CREATE STATISTICS stat_name ON dbo.BaseTable(ColumnName) WHERE FilterColumn = 'Value', ensuring dbo.BaseTable is verified as type U.
• If working with an indexed view, create the statistics on the clustered index’s underlying table columns rather than the view object itself.

Step 3: Validate Memory-Optimized Table Compatibility

• If using In-Memory OLTP tables in SQL Server 2025, verify the table is natively compiled correctly and not accidentally referenced via a cross-database reference or linked server synonym.
• Use sys.tables joined with sys.filegroups to confirm the object resides in a standard or memory-optimized filegroup rather than being an external table pointer (type U with is_external = 1).
• Re-run the CREATE STATISTICS command directly against the base memory-optimized table name.

Step 4: Apply Cumulative Updates and Restart Services

• Navigate to Windows Update or the Microsoft Update Catalog to ensure SQL Server 2025 has the latest Cumulative Update (CU) installed, as metadata handling for filtered statistics on hybrid object types is periodically patched.
• Restart the SQL Server service via services.msc or run Restart-Service -Name 'MSSQLSERVER' in an elevated PowerShell window on Windows Server 2025.
• Re-attempt the statistics creation script after the service restarts.

Alternative or Verification Steps

• Verify the fix by querying sys.stats and sys.stats_columns for the base table to confirm the new filtered statistic now appears with the correct filter_definition.
• If the object must remain a view, use an Indexed View (materialized via CREATE UNIQUE CLUSTERED INDEX) as an alternative, then apply filtered statistics to the view’s underlying clustered index columns rather than the view itself.

Troubleshooting Guide: SQL Server Error 10622 – Compressed Index Not Supported on Sparse Column Tables

Why This Error Happens

This error occurs because SQL Server does not permit row or page compression to be applied to an index when the underlying table contains sparse columns or a column set column. Sparse columns are already optimized for storage efficiency (typically used to handle NULL values economically), and the compression engine is architecturally incompatible with the sparse storage format. This is a design limitation rather than a bug, meaning the operation will fail every time a CREATE INDEX or ALTER INDEX REBUILD statement attempts to combine these two features on the same object.

Step-by-Step Resolution Guide

Step 1: Identify the Conflicting Columns

• Run a query against sys.columns to check the is_sparse and is_column_set flags for the table in question.
• Use the following T-SQL snippet to pinpoint the exact columns causing the conflict:

SELECT name, is_sparse, is_column_set 
FROM sys.columns 
WHERE object_id = OBJECT_ID('YourTableName');

• Note which columns are flagged as 1 (True), as these are blocking the compression request.

Step 2: Remove Compression from the Index Definition

• Since compression cannot coexist with sparse columns, modify your CREATE INDEX or ALTER INDEX script to remove the DATA_COMPRESSION clause entirely.
• Locate the specific T-SQL statement in your deployment script or SSMS query window.
• Change the syntax from WITH (DATA_COMPRESSION = PAGE) or WITH (DATA_COMPRESSION = ROW) to WITH (DATA_COMPRESSION = NONE).
• Re-execute the index creation or rebuild command.

Step 3: Evaluate Schema Redesign (If Compression is Mandatory)

• If storage savings are a hard requirement, determine if the sparse column strategy is still necessary for the current dataset.
• Convert the sparse columns back to standard (non-sparse) columns using ALTER TABLE... ALTER COLUMN... [datatype] syntax, removing the SPARSE keyword.
• Once converted, you may apply DATA_COMPRESSION = PAGE to the index without triggering Error 10622.
• Caution: Only proceed with this step if you have verified that the storage overhead of standard columns is acceptable for your specific NULL-heavy dataset.

Alternative or Verification Steps

• Verification: After applying the fix, run sp_helpindex 'YourTableName' or query sys.indexes to confirm the index was created successfully with data_compression_desc set to NONE.
• Alternative: If you need both sparse storage and compression logic, consider implementing compression at the application layer or using a filtered index on a separate, non-sparse staging table to handle the compressed workload.

Troubleshooting Guide: SQL Server Error 10621 – Index Creation Failure on Column Set

Why This Error Happens

This error occurs because SQL Server does not permit a sparse column set (the XML column that represents all sparse columns in a table) to be referenced within a filtered index’s WHERE clause expression. The column set is a computed, aggregate representation of multiple underlying sparse columns, and the Query Optimizer cannot resolve a single sparse column’s value reliably through this aggregate for filtering purposes at index creation time. This is a design limitation in the SQL Server Database Engine (persisting into SQL Server 2025) rather than a permissions or configuration issue, meaning it cannot be bypassed via server settings and requires a change to the T-SQL index definition itself.

Step-by-Step Resolution Guide

Step 1: Identify the Column Set in the Table Schema

• Open SQL Server Management Studio (SSMS) and connect to the instance reporting the error.
• Run the following query to locate the column set associated with the table:

  SELECT name, is_column_set 
  FROM sys.columns 
  WHERE object_id = OBJECT_ID('YourSchema.YourTableName') 
  AND is_column_set = 1;

• Note the name of the column set (often named something like SparseColumnSet or similar based on the CREATE TABLE definition).

Step 2: Rewrite the Filtered Index Expression

• Locate the original CREATE INDEX script that is failing with Error 10621.
• Modify the WHERE clause in the index definition to reference the specific underlying sparse column (e.g., ProductColor) directly, rather than the column set wrapper (e.g., AllSparseColumns).
• Example of the incorrect syntax causing the error:

  CREATE INDEX IX_Filtered_Sparse 
  ON dbo.Products (ProductID) 
  WHERE AllSparseColumns IS NOT NULL; -- Fails with 10621

• Corrected syntax referencing the actual sparse column:

  CREATE INDEX IX_Filtered_Sparse 
  ON dbo.Products (ProductID) 
  WHERE ProductColor IS NOT NULL; -- Resolves the error

Step 3: Validate Sparse Column Attributes

• Ensure the column you are now referencing in the filter is explicitly defined with the SPARSE attribute in the table schema.
• If the column is not sparse, verify why it was included in the column set definition, as this may indicate a schema design conflict.

Step 4: Re-deploy the Index

• Execute the corrected CREATE INDEX script via SSMS or your deployment pipeline (e.g., Azure DevOps or SSDT).
• Check the “Messages” tab in SSMS to confirm the command completed successfully without Error 10621.

Alternative or Verification Steps

• Verification: Query sys.indexes and sys.index_columns to confirm the index now exists and is correctly associated with the specific sparse column rather than the column set.
• Alternative: If you specifically need to filter based on the presence of any sparse data (the aggregate), you cannot use a standard filtered index on the column set; instead, consider creating a persisted computed column that checks CASE WHEN AllSparseColumns IS NOT NULL THEN 1 ELSE 0 END and build the filtered index on that computed column instead.

Troubleshooting Guide: SQL Server Error 10620 — Filtered Index Creation Failure

Why This Error Happens

Error 10620 occurs when the WHERE clause of a filtered index or filtered statistics definition contains a direct comparison to NULL using operators such as = NULL or <> NULL. SQL Server’s query optimizer cannot evaluate these comparisons deterministically because standard comparison operators do not work with NULL under ANSI SQL semantics. This is not a corruption or permissions issue — it is a syntax validation rule enforced by the SQL Server 2025 Database Engine when parsing the filter predicate at index creation time.

Step-by-Step Resolution Guide

Step 1: Identify the Offending Filter Expression

• Open the failing CREATE INDEX or CREATE STATISTICS script in SQL Server Management Studio (SSMS) or Azure Data Studio.
• Locate the WHERE clause referenced in the error message, noting the exact table and index name provided in the %.*ls placeholders.
• Example of invalid syntax: WHERE [DeletedDate] = NULL or WHERE [Status] <> NULL.

Step 2: Rewrite the Filter Using IS [NOT] NULL

• Replace any = NULL comparisons with IS NULL.
• Replace any <> NULL or != NULL comparisons with IS NOT NULL.
• Corrected example:

  CREATE NONCLUSTERED INDEX IX_Orders_Filtered
  ON dbo.Orders (OrderID)
  WHERE [DeletedDate] IS NULL;

• If the filter includes multiple conditions, verify each one individually, as only one invalid NULL comparison is enough to trigger Error 10620.

Step 3: Re-run the Index Creation Script

• Execute the corrected CREATE INDEX statement in a query window connected to the target database.
• Confirm there are no additional syntax issues by checking the Messages tab in SSMS for a “Command(s) completed successfully” confirmation.
• If deploying via a DACPAC, SSDT project, or migration script, update the source .sql file and redeploy rather than patching the index directly on production.

Step 4: Audit Related Scripts for the Same Pattern

• Search your source control repository or deployment scripts (using grep, findstr, or SSMS “Find in Files”) for the pattern = NULL or <> NULL inside any WHERE clauses tied to CREATE INDEX or CREATE STATISTICS.
• Update all instances proactively to avoid repeated failures during future deployments or CI/CD pipeline runs on Windows Server 2025.

Alternative or Verification Steps

• Verify the fix by querying sys.indexes and sys.filter_columns (or sys.indexes.filter_definition) to confirm the index now exists and the filter definition reads correctly with IS NULL / IS NOT NULL syntax.
• If the error persists after rewriting the predicate, check for nested views, computed columns, or synonyms referenced in the filter expression that may contain their own embedded NULL comparisons — resolve those at the source object level first.

Troubleshooting Guide: SQL Server Error 10619 – Filtered Index Cannot Be Created on CLR Data Type Column

Why This Error Happens

This error occurs because SQL Server does not support creating filtered indexes with a WHERE clause expression that references a column defined using a CLR (Common Language Runtime) user-defined data type, such as hierarchyid, geometry, geography, or a custom CLR UDT. The query optimizer cannot evaluate or store statistics on CLR type comparisons within a filter predicate because these types lack the native binary ordering and comparison logic required for filtered index metadata. This is a design limitation enforced by the Database Engine, not a corruption or permissions issue, so it cannot be bypassed through configuration changes alone.

Step-by-Step Resolution Guide

Step 1: Identify the Offending Column and Filter Expression

• Open SQL Server Management Studio (SSMS) and locate the exact CREATE INDEX statement that triggered Error 10619.
• Run the following query to confirm the data type of the column referenced in your filter predicate:

  SELECT c.name AS ColumnName, t.name AS DataType
  FROM sys.columns c
  JOIN sys.types t ON c.user_type_id = t.user_type_id
  WHERE c.object_id = OBJECT_ID('YourSchema.YourTable');

• Confirm that the column type shown is a CLR type (e.g., hierarchyid, geometry, geography, or a custom assembly-based type).

Step 2: Rewrite the Filter Expression Without the CLR Column

• Remove any reference to the CLR-typed column from the WHERE clause of your filtered index definition.
• If filtering logic is required, convert the CLR value to a supported scalar type (such as varchar, int, or bit) in a persisted computed column, then filter on that computed column instead:

  ALTER TABLE YourSchema.YourTable
  ADD HierarchyLevel AS CAST(YourCLRColumn.GetLevel() AS int) PERSISTED;

• Rebuild the filtered index using the new persisted computed column in place of the CLR column:

  CREATE INDEX IX_Filtered_Example
  ON YourSchema.YourTable (OtherColumn)
  WHERE HierarchyLevel = 2;

Step 3: Validate the Computed Column is Deterministic and Persisted

• Confirm the computed column qualifies for indexing by checking sys.computed_columns:

  SELECT name, is_persisted, is_deterministic
  FROM sys.computed_columns
  WHERE object_id = OBJECT_ID('YourSchema.YourTable');

• Ensure is_persisted = 1 and is_deterministic = 1; if either is 0, adjust the expression (e.g., specify explicit schema-bound function calls) until both conditions are satisfied.

Step 4: Re-run the Index Creation Script

• Execute the corrected CREATE INDEX statement in SSMS or via your deployment pipeline (e.g., SSDT, Flyway, or DACPAC).
• Confirm no errors are returned and the object appears under Indexes in Object Explorer for the target table.

Alternative or Verification Steps

• Verify the fix by querying sys.indexes and sys.filter_columns to confirm the new filtered index exists and does not reference the original CLR column:

  SELECT i.name, fc.name AS FilterColumn
  FROM sys.indexes i
  JOIN sys.filter_columns fc ON i.object_id = fc.object_id AND i.index_id = fc.index_id
  WHERE i.object_id = OBJECT_ID('YourSchema.YourTable');

• If computed columns are not viable for your scenario, consider moving the filtering logic into the application layer or a view with an indexed computed column instead of a native filtered index.

Troubleshooting Guide: SQL Server Error 10618 — IGNOREDUPKEY on Filtered Index

Why This Error Happens

This error occurs because SQL Server does not permit the IGNORE_DUP_KEY option to be set to ON when the target table has a filtered unique index. The IGNORE_DUP_KEY option is designed to work with full-table unique indexes, and its duplicate-handling logic is incompatible with the row-subset logic that filtered indexes rely on. This is a design limitation by Microsoft, not a configuration defect, corruption issue, or permissions problem — it is triggered purely by the T-SQL statement syntax used in the INSERT or index creation operation.

Step-by-Step Resolution Guide

Step 1: Identify the Offending Statement and Index

• Review the exact error message to extract the filtered index name and table name referenced in the %.*ls placeholders.
• Run the following query to confirm the index is filtered:

  SELECT name, filter_definition 
  FROM sys.indexes 
  WHERE object_id = OBJECT_ID('YourSchema.YourTable') 
  AND is_unique = 1 
  AND filter_definition IS NOT NULL;

• Locate the specific INSERT, MERGE, or CREATE INDEX statement that is setting IGNORE_DUP_KEY = ON.

Step 2: Remove the IGNOREDUPKEY Option from the Index Definition

• If the option is set at the index level, script out the existing index using SQL Server Management Studio (SSMS) or sp_helpindex.
• Drop and recreate the index without the IGNORE_DUP_KEY clause:

  DROP INDEX IX_FilteredIndex_Name ON dbo.YourTable;

  CREATE UNIQUE INDEX IX_FilteredIndex_Name 
  ON dbo.YourTable (ColumnA) 
  WHERE ColumnB IS NOT NULL;

• Ensure the WITH (IGNORE_DUP_KEY = OFF) clause (or no clause at all, since OFF is default) is used in the WITH options block.

Step 3: Rewrite the Application-Level INSERT Logic

• Since duplicates can no longer be silently ignored by the engine, update the calling application or stored procedure to handle duplicates explicitly.
• Replace reliance on IGNORE_DUP_KEY with a MERGE statement or a pre-check using NOT EXISTS:

  INSERT INTO dbo.YourTable (ColumnA, ColumnB)
  SELECT @ColumnA, @ColumnB
  WHERE NOT EXISTS (
      SELECT 1 FROM dbo.YourTable 
      WHERE ColumnA = @ColumnA AND ColumnB IS NOT NULL
  );

• Alternatively, wrap the insert in a TRY...CATCH block to gracefully handle error 2601/2627 (duplicate key violation) instead of using the index hint.

Step 4: Validate on Windows Server 2025 / Windows 11 Environment

• Confirm the SQL Server 2025 instance has the latest Cumulative Update (CU) installed, as filtered index handling has received patches in past versions; check via:

  SELECT @@VERSION;

• Restart the SQL Server service via services.msc or Restart-Service MSSQLSERVER in PowerShell (run as Administrator) if the index rebuild requires a schema cache refresh.

Alternative or Verification Steps

• To verify the fix, re-run the original INSERT or CREATE INDEX statement — it should complete without raising Error 10618, and duplicate rows should be rejected via standard constraint violation (Error 2601) instead.
• If rewriting the application is not immediately feasible, consider using a non-filtered unique index with IGNORE_DUP_KEY = ON as a temporary workaround, provided the business logic does not strictly require filtering.

Troubleshooting Guide: SQL Server Error 10617 – Index Key Length Exceeds Vardecimal Storage Format Limit

Why This Error Happens

This error occurs because the combined size of the key columns in an index exceeds the maximum allowed byte limit (900 bytes for standard indexes) when the table uses the vardecimal storage format. Vardecimal storage compresses decimal/numeric data variably, but SQL Server still validates index key length against the fixed maximum during index creation or rebuild operations, causing a conflict when wide decimal columns are indexed. This commonly happens after migrating databases with legacy sp_db_vardecimal_storage_format settings enabled, or when adding new columns to an existing index without recalculating total key size.

Step-by-Step Resolution Guide

Step 1: Identify the Offending Index and Table

• Run the following query to locate the index and its key columns:

  SELECT i.name AS IndexName, t.name AS TableName, c.name AS ColumnName, c.max_length
  FROM sys.indexes i
  JOIN sys.index_columns ic ON i.object_id = ic.object_id AND i.index_id = ic.index_id
  JOIN sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id
  JOIN sys.tables t ON i.object_id = t.object_id
  WHERE i.name = 'YourIndexName';

• Confirm whether the table has vardecimal storage format enabled by checking sys.tables for the is_vardecimal_storage_format_on flag (or via sp_db_vardecimal_storage_format in SQL Server 2025 compatibility checks).

Step 2: Reduce the Index Key Size

• Remove unnecessary columns from the composite index definition using SQL Server Management Studio (SSMS) 21 or T-SQL:

  DROP INDEX YourIndexName ON dbo.YourTableName;

• Convert wide decimal(38,x) or numeric(38,x) columns to smaller precision where business logic allows, or move large decimal columns to INCLUDE columns instead of key columns:

  CREATE INDEX YourIndexName ON dbo.YourTableName (SmallKeyColumn)
  INCLUDE (LargeDecimalColumn);

Step 3: Migrate Away from Vardecimal Storage (If Applicable)

• Vardecimal storage format is a legacy feature; on SQL Server 2025, migrate to row/page compression instead:

  ALTER TABLE dbo.YourTableName REBUILD WITH (DATA_COMPRESSION = ROW);

• Disable vardecimal storage format on the table before rebuilding the index:

  EXEC sp_tableoption 'dbo.YourTableName', 'vardecimal storage format', 'OFF';

Step 4: Rebuild the Index

• Recreate the index with the adjusted schema:

  CREATE INDEX YourIndexName ON dbo.YourTableName (AdjustedKeyColumns);

• Monitor the SQL Server Error Log (located at C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG on Windows Server 2025) for confirmation that Error 10617 no longer appears.

Alternative or Verification Steps

• Verify the fix by querying sys.indexes and sys.dm_db_index_physical_stats to confirm the index was created successfully without errors.
• If row compression is not an option due to application dependencies, consider splitting the table vertically or using a filtered index with fewer key columns to stay under the 900-byte limit.

Troubleshooting Guide: Error 10612 — Filtered Index Creation Failure in SQL Server 2025

Why This Error Happens

Error 10612 occurs when a filtered index or filtered statistic is created with a WHERE clause that compares a column to a constant value incompatible with that column’s data type. This typically happens due to implicit conversion failures between types such as varchar vs datetime, int vs uniqueidentifier, or when comparing against NULL or non-deterministic constants in ways the Query Optimizer cannot resolve at index-creation time. SQL Server 2025’s stricter type-checking for filtered indexes (introduced to prevent runtime conversion errors) surfaces this issue more aggressively than in earlier engine versions.

Step-by-Step Resolution Guide

Step 1: Identify the Offending Filter Expression

• Open the failed T-SQL script (CREATE INDEX or CREATE STATISTICS statement) in SSMS or Azure Data Studio.
• Locate the WHERE clause on the filtered index, e.g., WHERE [StatusDate] = '2025-13-01' or WHERE [CustomerID] = 'ABC'.
• Run sp_help '<TableName>' or query sys.columns joined with sys.types to confirm the exact data type, length, precision, and scale of the filtered column.

Step 2: Correct the Data Type Mismatch

• Rewrite the constant in the filter expression to explicitly match the column’s native data type.
• Example fix: if [StatusDate] is datetime2(7), change WHERE [StatusDate] = '2025-01-13' to WHERE [StatusDate] = CAST('2025-01-13' AS datetime2(7)).
• For string columns, ensure collation and length match: WHERE [Code] = CAST('AB' AS varchar(10)).
• Avoid comparing numeric columns to quoted string literals (e.g., use WHERE [Amount] = 100 not WHERE [Amount] = '100').

Step 3: Validate Against System Catalog Metadata

• Run the following query to cross-check expected types before re-running the index creation script:

SELECT c.name, t.name AS data_type, c.max_length, c.precision, c.scale
FROM sys.columns c
JOIN sys.types t ON c.user_type_id = t.user_type_id
WHERE c.object_id = OBJECT_ID('dbo.YourTableName');

• Update the filter expression constants to align exactly with the output, including precision/scale for decimal or numeric types.

Step 4: Re-Create the Filtered Index

• Drop any partially created index object if it exists: DROP INDEX IF EXISTS [IX_FilteredIndex] ON [dbo].[YourTableName];
• Re-run the corrected CREATE INDEX ... WHERE ... statement.
• Check SQL Server Error Log (via EXEC sp_readerrorlog or Event Viewer under Applications and Services Logs > MSSQLSERVER) to confirm no further 10612 errors are logged.

Alternative or Verification Steps

• Verify the fix by querying sys.indexes and sys.filter_columns (via OBJECTPROPERTY or INDEXPROPERTY) to confirm the filtered index now exists and is enabled.
• If the error persists, as a workaround, create a computed column with an explicit CAST/CONVERT matching the target type, then build the filtered index against that computed column instead of the raw column.

Troubleshooting Guide: SQL Server Error 10611 – Filtered Index Creation Failure

Why This Error Happens

This error occurs when you attempt to create a filtered index on a table where the filter expression compares a column against a constant value that has a higher data type precedence or a different collation than the column itself. SQL Server’s Database Engine requires that filtered index predicates avoid implicit conversions, because converting the column (rather than the constant) would compromise the integrity and usability of the index. This typically happens after a schema migration, collation change, or when developers write filter predicates using literals (like Unicode strings or specific numeric types) that don’t exactly match the underlying column’s defined data type or collation.

Step-by-Step Resolution Guide

Step 1: Identify the Conflicting Column and Data Types

• Open SSMS (SQL Server Management Studio) and locate the exact CREATE INDEX statement that triggered Error 10611.
• Run sp_help 'YourTableName' or query sys.columns joined with sys.types to check the exact data type, length, and collation of the column referenced in the WHERE clause of your filtered index.
• Pay close attention to mismatches such as varchar vs nvarchar, or differing collations like SQL_Latin1_General_CP1_CI_AS vs Latin1_General_100_CI_AS_SC.

Step 2: Align the Constant’s Data Type with the Column

• Modify the filter predicate in your CREATE INDEX statement to explicitly match the column’s native data type.
• Example: If the column is varchar, ensure the constant is not prefixed with N (which forces nvarchar).

  -- Incorrect (causes 10611 due to type precedence):
  CREATE INDEX IX_Filtered ON dbo.Orders(Status) WHERE Status = N'Active';

  -- Corrected:
  CREATE INDEX IX_Filtered ON dbo.Orders(Status) WHERE Status = 'Active';

• If a collation mismatch is the root cause, explicitly cast the constant using COLLATE to match the column: WHERE Status = 'Active' COLLATE SQL_Latin1_General_CP1_CI_AS.

Step 3: Verify Collation Settings at the Database and Column Level

• Navigate to SSMS > Right-click Database > Properties > Options to check the Database-level collation.
• If the column-level collation differs from what you expect (often due to a restored database from a different server), run:

  ALTER TABLE dbo.Orders 
  ALTER COLUMN Status varchar(50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL;

• Ensure this change is tested in a staging environment first, as altering collation can lock the table and affect dependent objects.

Step 4: Re-run the Index Creation Script

• Once the data types and collations are aligned, re-execute the CREATE INDEX statement in SSMS.
• Check the “Messages” tab for a success confirmation (e.g., “Commands completed successfully”).

Alternative or Verification Steps

• Verification: Run SELECT * FROM sys.indexes WHERE name = 'IX_Filtered' to confirm the index now exists with an is_filtered = 1 flag.
• Alternative: If changing the column collation is too risky for a production environment, remove the explicit N prefix or CAST/CONVERT function from the constant in the filter predicate instead of altering the underlying table schema.

Troubleshooting Guide: SQL Server Error 10610 – Filtered Index Creation Failure

Why This Error Happens

Error 10610 occurs when a CREATE INDEX statement with a WHERE clause (filtered index) targets a database object that is not a standard user table, such as a view, system table, or external/temporal table variant. SQL Server 2025 restricts filtered indexes strictly to on-disk user tables because the query optimizer needs persistent statistics and a physical row-locator structure that non-table objects do not support. This is typically a design/syntax issue rather than a server configuration or permissions problem, often triggered when a script intended for a table is accidentally pointed at a view or synonym.

Step-by-Step Resolution Guide

Step 1: Identify the Target Object Type

• Run the following query to check the object type before creating the index:

  SELECT name, type_desc FROM sys.objects WHERE name = 'YourObjectName';

• Confirm the type_desc returns USER_TABLE. If it returns VIEW, SYSTEM_TABLE, or SYNONYM, this confirms the root cause.

Step 2: Redirect the Index to the Underlying Base Table

• If you intended to filter data on a view, locate the base table(s) referenced in the view definition using sp_helptext 'ViewName'.
• Create the filtered index directly on the underlying physical table instead of the view:

  CREATE INDEX IX_Filtered_Example 
  ON dbo.BaseTable (ColumnName) 
  WHERE ColumnName IS NOT NULL;

Step 3: Use an Indexed View as an Alternative (If Filtering on a View is Required)

• If the business logic strictly requires filtering logic on a view, convert the view into an Indexed View instead of applying a filtered index.
• First, ensure the view uses WITH SCHEMABINDING:

  ALTER VIEW dbo.YourView WITH SCHEMABINDING AS
  SELECT ColumnName FROM dbo.BaseTable WHERE ColumnName IS NOT NULL;

• Then create a standard unique clustered index on the view (Note: Filtered WHERE clauses in the index itself are still not supported here, but the view’s WHERE clause handles the filtering logic).

Step 4: Validate Script Execution Context

• Check if the script was run against the wrong database schema where an object name collision exists between a view and a table.
• Use USE [YourDatabaseName]; explicitly at the top of your script in SQL Server Management Studio (SSMS) 21 to ensure you aren’t accidentally targeting a system view in master or msdb.

Alternative or Verification Steps

• Verification: After modifying the script to target the correct base table, re-run the CREATE INDEX command. Success is confirmed if the command returns “Commands completed successfully” and the index appears in sys.indexes.
• Alternative: If performance filtering is needed on a view for reporting purposes, consider using a Non-Filtered Indexed View combined with application-side WHERE clauses, or materialize the filtered data set into a new physical table via a scheduled ETL job.

Troubleshooting Guide: SQL Server Error 10609 – Filtered Index on Computed Column

Why This Error Happens

This error occurs because SQL Server 2025 does not permit filtered indexes to reference computed columns within their filter predicate expression. The database engine requires filtered index WHERE clauses to evaluate against static, deterministic column values at index creation time, and computed columns (whether persisted or non-persisted) are excluded from this capability due to how the query optimizer evaluates filter selectivity. This is a design limitation by Microsoft rather than a configuration or permissions issue, meaning the fix must occur at the T-SQL script level.

Step-by-Step Resolution Guide

Step 1: Identify the Offending Column and Index Definition

• Open SSMS (SQL Server Management Studio) connected to your Windows Server 2025 instance.
• Run the following query to inspect the table structure and locate computed columns: SELECT name, is_computed FROM sys.columns WHERE object_id = OBJECT_ID('YourTableName');
• Note the exact computed column referenced in your failed CREATE INDEX statement’s WHERE clause.

Step 2: Rewrite the Filter Expression Using Base Columns

• Locate the underlying base columns used to generate the computed column (e.g., if TotalPrice is computed as Price * Quantity, identify Price and Quantity).
• Modify your CREATE INDEX script to filter on these base columns instead. Example:
• Instead of: WHERE TotalPrice > 100
• Use: WHERE Price > 0 AND Quantity > 0
• Ensure the rewritten logic still captures the same subset of rows your original filter intended.

Step 3: Persist the Computed Column (If Base Columns Are Not Viable)

• If the filter logic absolutely requires the computed value, alter the column to be persisted: ALTER TABLE YourTableName ALTER COLUMN TotalPrice AS (Price * Quantity) PERSISTED;
• Note: Even after persisting, this column still cannot be used in the filter predicate—persisting it only allows it to be included as an index key or included column, not referenced in the WHERE clause.
• Re-run the CREATE INDEX statement using only non-computed columns in the filter.

Step 4: Validate T-SQL Syntax and Re-deploy

• Execute the corrected CREATE INDEX script in a non-production environment first.
• Check for cascading errors in the SQL Server Error Log located at C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG to confirm no residual index creation failures.

Alternative or Verification Steps

• Verify the fix by querying sys.indexes and sys.filter_columns to confirm the filtered index was created successfully: SELECT i.name, c.name AS filter_column FROM sys.indexes i JOIN sys.filter_columns fc ON i.object_id = fc.object_id JOIN sys.columns c ON fc.column_id = c.column_id;
• If rewriting the filter is not feasible, consider creating a standard (non-filtered) index on the computed column instead, or implement the filtering logic within application-layer queries rather than at the index level.

Troubleshooting Guide: SQL Server Error 10608

Why This Error Happens

This error occurs because the clustered index you are attempting to disable is the underlying structure supporting a primary key constraint, and SQL Server’s Change Tracking feature has a hard dependency on that primary key to identify row-level changes. Since Change Tracking uses the primary key to map modified rows to the internal change tracking tables, SQL Server blocks any operation—such as disabling the index—that would effectively remove or invalidate that key structure. Essentially, the engine is preventing a structural change that would break the change tracking mechanism’s ability to sync data correctly.

Step-by-Step Resolution Guide

Step 1: Identify the Table and Index Dependency

• Open SQL Server Management Studio (SSMS) and connect to your SQL Server 2025 instance.
• Run the following query to confirm change tracking is enabled on the specific table:

SELECT * FROM sys.change_tracking_tables 
WHERE object_id = OBJECT_ID('YourSchema.YourTableName');

• Verify that the index in question (from the error message) is tied to the Primary Key constraint by checking sys.indexes where is_primary_key = 1.

Step 2: Disable Change Tracking Before Modifying the Index

• You cannot disable the index while tracking is active. You must temporarily disable Change Tracking at the table level first.
• Execute the following T-SQL command, replacing the placeholders with your specific database and table names:

ALTER TABLE YourSchema.YourTableName DISABLE CHANGE TRACKING;

• If this is a database-wide requirement (unlikely for just an index change), ensure no other dependent tables rely on this specific tracking session.

Step 3: Perform the Index Operation

• With Change Tracking removed from the table, you can now execute your original intended command to disable or rebuild the index.
• Example command to disable the index:

ALTER INDEX YourIndexName ON YourSchema.YourTableName DISABLE;

• If your goal was actually to modify the Primary Key itself, you must drop the Primary Key constraint (which will automatically handle the index) rather than trying to disable the index directly.

Step 4: Re-enable Change Tracking

• Once the index maintenance or structural change is complete, re-enable tracking to resume synchronization for applications relying on this data (e.g., Sync Framework, ETL tools).
• Run the following command to restore functionality:

ALTER TABLE YourSchema.YourTableName ENABLE CHANGE TRACKING 
WITH (TRACK_COLUMNS_UPDATED = ON);

• Note: Re-enabling tracking resets the change tracking baseline for that table, meaning any client applications will treat this as a new starting point for sync.

Alternative or Verification Steps

• Verification: Run SELECT * FROM sys.change_tracking_tables; again to confirm the is_track_columns_updated_on status is correctly set to 1 (or your desired configuration) and that no errors appear in the SQL Server Error Log (viewable via SSMS > Management > SQL Server Logs).
• Alternative: If disabling Change Tracking is not permissible due to active application dependencies, consider rebuilding the index using the ONLINE = ON option (if using Enterprise Edition) instead of disabling it, as a rebuild does not break the Primary Key dependency required by Change Tracking.

Troubleshooting Guide: SQL Server Error 10607 — Clustered Index Cannot Be Disabled Due to Change Tracking

Why This Error Happens

This error occurs because SQL Server enforces a dependency rule between change tracking and clustered indexes: a table’s clustered index physically maintains the row structure that change tracking relies on to log row-level modifications. When you attempt to disable a clustered index using ALTER INDEX ... DISABLE while change tracking is still active on that table, SQL Server blocks the operation to prevent corruption of the change tracking metadata. This is a safeguard, not a bug—the engine requires you to explicitly remove the change tracking dependency before it will allow the index to go offline.

Step-by-Step Resolution Guide

Step 1: Identify Which Tables Have Change Tracking Enabled

• Open SQL Server Management Studio (SSMS) and connect to the target instance.
• Run the following query to confirm change tracking status on the affected table:

SELECT t.name AS TableName, ct.is_track_columns_updated_on
FROM sys.change_tracking_tables ct
JOIN sys.tables t ON ct.object_id = t.object_id
WHERE t.name = 'YourTableName';

• Note the database name as well, since change tracking is enabled at both the database and table level.

Step 2: Disable Change Tracking on the Table

• Execute the following T-SQL command, replacing the schema and table name as needed:

ALTER TABLE dbo.YourTableName
DISABLE CHANGE_TRACKING;

• If change tracking is also enabled at the database level and no longer needed, you can optionally disable it there too:

ALTER DATABASE YourDatabaseName
SET CHANGE_TRACKING = OFF;

• Only disable at the database level if no other tables depend on it.

Step 3: Disable the Clustered Index

• Once change tracking is removed, retry the original operation:

ALTER INDEX YourClusteredIndexName
ON dbo.YourTableName
DISABLE;

• Confirm no error 10607 is returned in the Messages pane.

Step 4: Re-enable Change Tracking (If Required for Application Logic)

• If your application depends on change tracking for sync operations (e.g., SQL Server Change Tracking API or SSIS packages), re-enable it after completing index maintenance:

ALTER TABLE dbo.YourTableName
ENABLE CHANGE_TRACKING
WITH (TRACK_COLUMNS_UPDATED = ON);

• Rebuild the clustered index (instead of leaving it disabled) before re-enabling change tracking, since a disabled clustered index makes the table inaccessible.

Alternative or Verification Steps

• Verify the fix by querying sys.indexes for is_disabled = 0 on the target index, and confirm sys.change_tracking_tables no longer lists the table (if permanently removed).
• If you must keep change tracking active and only need to rebuild the index (not disable it long-term), use ALTER INDEX ... REBUILD instead of DISABLE—this avoids the conflict entirely since rebuild operations don’t require dropping the index structure.

Troubleshooting Guide: Error 10606 – Cannot Disable Clustered Index on Replicated Indexed View

Why This Error Happens

This error occurs because SQL Server 2025 prevents structural changes to a clustered index on an indexed view when that view is actively published as part of a replication article (snapshot, transactional, or merge replication). The clustered index is required to materialize the view’s data, so disabling it would break the underlying replication mechanism that depends on that physical row storage. SQL Server blocks the operation outright rather than risk leaving subscribers with an inconsistent or unsynchronized dataset.

Step-by-Step Resolution Guide

Step 1: Identify the Replication Dependency

• Open SQL Server Management Studio (SSMS) and connect to the publisher instance.
• Run the following query to confirm the view is published:

SELECT * FROM sys.sql_expression_dependencies 
WHERE referenced_entity_name = '<ViewName>';

• Check Replication > Local Publications in SSMS Object Explorer to locate which publication includes the view.

Step 2: Remove the View from the Publication

• Navigate to Replication > Local Publications > [Your Publication] > Properties > Articles.
• Uncheck or remove the indexed view from the list of published articles.
• Alternatively, use T-SQL to drop the article:

EXEC sp_droparticle 
    @publication = 'YourPublicationName', 
    @article = 'YourViewName';

• Run sp_reinitsubscription or generate a new snapshot if subscribers need to be resynced afterward.

Step 3: Disable the Clustered Index

• Once the view is no longer tied to an active publication, execute the disable command:

ALTER INDEX [IndexName] ON [dbo].[YourViewName] DISABLE;

• Confirm no errors are returned and the index status updates in sys.indexes.

Step 4: Re-Add the View to Replication (If Required)

• If the view must remain part of replication, rebuild the clustered index instead of disabling it:

ALTER INDEX [IndexName] ON [dbo].[YourViewName] REBUILD;

• Re-add the article to the publication via SSMS or sp_addarticle if it was previously removed.

Alternative or Verification Steps

• Verify the fix by querying sys.indexes and checking the is_disabled column equals 1 for the target index, confirming the disable operation succeeded.
• If the index must stay active while schema changes are needed, consider dropping and recreating the indexed view entirely instead of disabling its clustered index, then re-publish it as a new article.

Troubleshooting Guide: Error 10605 – Cannot Disable Primary Key Index on Published Table

Why This Error Happens

This error occurs because SQL Server 2025 enforces replication integrity by preventing structural changes to indexes that replication relies on for tracking row identity. When a table is published as part of a transactional or merge replication setup, the primary key index is used by the Log Reader Agent or Merge Agent to uniquely identify rows during synchronization. Attempting to disable this index via ALTER INDEX ... DISABLE breaks the dependency chain that replication agents need, so SQL Server blocks the operation outright to prevent silent replication failures or data corruption.

Step-by-Step Resolution Guide

Step 1: Identify the Replication Configuration

• Open SQL Server Management Studio (SSMS) and connect to the instance showing the error.
• Run the following query to confirm the table is published:

  SELECT * FROM sys.tables WHERE is_published = 1 OR is_merge_published = 1;

• Note the publication name using sp_helppublication for reference in later steps.

Step 2: Remove the Table from Replication (If Index Change Is Required)

• If disabling the primary key index is mandatory (e.g., for a bulk load operation), you must first drop the article from the publication:

  EXEC sp_droparticle @publication = 'YourPublicationName', @article = 'YourTableName';

• Perform the index disable/rebuild operation now that the dependency is removed:

  ALTER INDEX PK_YourTable ON dbo.YourTable DISABLE;

• Re-add the article back to the publication once maintenance is complete:

  EXEC sp_addarticle @publication = 'YourPublicationName', @article = 'YourTableName', @source_object = 'YourTableName';

Step 3: Use Index Rebuild Instead of Disable (Recommended Alternative)

• Rather than disabling the index, use ALTER INDEX ... REBUILD to achieve maintenance goals without breaking replication:

  ALTER INDEX PK_YourTable ON dbo.YourTable REBUILD WITH (ONLINE = ON);

• This approach avoids the need to touch the publication at all and is non-blocking on SQL Server 2025 with the ONLINE = ON option.

Step 4: Reinitialize Subscriptions (If Schema Was Altered)

• If you dropped and re-added the article in Step 2, force a reinitialization so subscribers receive the updated schema:

  EXEC sp_reinitsubscription @publication = 'YourPublicationName';

• Monitor the Replication Monitor (in SSMS under Replication > Launch Replication Monitor) to confirm the snapshot applies cleanly to all subscribers.

Alternative or Verification Steps

• To verify the fix, re-run the original ALTER INDEX DISABLE or maintenance command; it should now execute without raising Error 10605 if the article was properly removed or the rebuild method was used instead.
• As an alternative, if replication topology changes are too risky in production, consider scheduling index maintenance during a replication-paused window using sp_replicationdboption to temporarily disable publishing at the database level, then re-enable it after maintenance completes.

Why This Error Happens

Error 10604 occurs because SQL Server 2025 cannot simultaneously convert a statistics object into an index while using DROP_EXISTING when ONLINE = ON is also specified in the same statement. This is a known architectural limitation because the ONLINE rebuild mechanism requires an existing index structure to perform concurrent DML operations, but a statistic object lacks the underlying B-tree structure needed for the online operation to track. Essentially, the Storage Engine cannot “upgrade” a lightweight statistics blob into a fully indexed structure while keeping the table available for concurrent reads/writes.

Step-by-Step Resolution Guide

Step 1: Identify the Conflicting T-SQL Syntax

• Open SQL Server Management Studio (SSMS) or Azure Data Studio connected to your Windows Server 2025 instance.
• Locate the specific CREATE INDEX statement that includes both WITH (DROP_EXISTING = ON, ONLINE = ON).
• Check if the object being replaced (DROP_EXISTING target) is actually a statistic (visible via sys.stats) rather than an index (visible via sys.indexes).

Step 2: Separate the Operation into Two Distinct Transactions

• Remove the ONLINE = ON argument from the initial creation statement.
• Run the index creation as an OFFLINE operation first to successfully convert the statistic into a physical index:

  CREATE INDEX IX_YourIndex ON dbo.YourTable(YourColumn) 
  WITH (DROP_EXISTING = ON, ONLINE = OFF);

• This forces an exclusive schema lock momentarily but successfully builds the underlying index structure required for future online operations.

Step 3: Schedule the Initial Build During a Maintenance Window

• Since ONLINE = OFF will briefly lock the table (Sch-M lock), schedule this specific conversion step during a low-traffic window to avoid blocking production queries.
• Use sp_whoisactive or the Activity Monitor in SSMS to confirm no long-running blocking transactions are queued before executing the script.

Step 4: Apply Online Maintenance for Future Rebuilds

• Once the statistic has been successfully converted to a physical index (Step 2), all subsequent maintenance (rebuilds/reorganizations) can safely use ONLINE = ON.
• Update your maintenance plans or Ola Hallengren scripts to use ONLINE = ON only for existing indexes, bypassing the conflict.

Alternative or Verification Steps

• Verification: Run SELECT * FROM sys.indexes WHERE name = 'IX_YourIndex'; after Step 2 to confirm the object type has successfully changed from a statistic to a type_desc of NONCLUSTERED or CLUSTERED.
• Alternative: If downtime is strictly prohibited, manually drop the statistic using DROP STATISTICS dbo.YourTable.YourStatName; and then create the index as a brand new object using CREATE INDEX ... WITH (ONLINE = ON), bypassing the DROP_EXISTING clause entirely.

Why This Error Happens

This error occurs because SQL Server enforces a strict dependency rule between indexed (materialized) views and the clustered indexes of their underlying base tables. When a clustered index on a base table is disabled—usually as a result of a maintenance operation, a failed ALTER INDEX REBUILD, or manual intervention—SQL Server cannot validate or recompute the data for any dependent view. Since the indexed view relies on the physical row data accessible only through an active clustered index, the rebuild operation is blocked until the base table’s index is restored to an enabled, functional state.

Step-by-Step Resolution Guide

Step 1: Identify the Disabled Base Table Index

• Open SQL Server Management Studio (SSMS) and connect to the affected instance.
• Run the following query to locate disabled indexes across the database:

SELECT name, is_disabled, object_id 
FROM sys.indexes 
WHERE is_disabled = 1;

• Cross-reference the object_id with sys.objects to confirm which base table the disabled clustered index belongs to.

Step 2: Rebuild the Base Table’s Clustered Index First

• Before touching the view, re-enable and rebuild the clustered index on the base table using:

ALTER INDEX [IndexName] ON [dbo].[BaseTableName] REBUILD;

• Ensure you have sufficient space in the PRIMARY filegroup, as rebuilding a disabled clustered index requires the full data footprint to be rewritten.
• Monitor progress via Activity Monitor or sys.dm_exec_requests if the table is large.

Step 3: Rebuild the Dependent Indexed View

• Once the base table’s clustered index shows is_disabled = 0, proceed to rebuild the view’s clustered index:

ALTER INDEX [ViewIndexName] ON [dbo].[ViewName] REBUILD;

• If the view still throws Error 10603, verify there are no additional disabled indexes on other tables referenced by the same view (multi-table joins in indexed views require all underlying clustered indexes to be active).

Step 4: Check for Windows Server Resource Constraints

• On Windows Server 2025 or Windows 11, confirm the SQL Server service account has adequate disk I/O and memory headroom, as rebuild operations on large tables can silently fail or stall under resource pressure, leaving indexes in a disabled state.
• Review the Windows Event Viewer (Application log, source: MSSQLSERVER) for any correlating disk or memory warnings during the rebuild window.

Alternative or Verification Steps

• Verify the fix by re-running SELECT is_disabled FROM sys.indexes WHERE object_id = OBJECT_ID('ViewName') — a result of 0 confirms the view’s clustered index is now active and healthy.
• If rebuilding fails due to space constraints, temporarily drop the indexed view (DROP INDEX or DROP VIEW if simple), rebuild the base table index, and then recreate the view with CREATE UNIQUE CLUSTERED INDEX as a fallback approach.

Troubleshooting Guide: Error 10602 – Mixing Old and New Syntax in CREATE/ALTER/DROP INDEX Statement

Why This Error Happens

This error occurs when a single index DDL statement combines legacy syntax elements (such as old-style WITH options or deprecated clustering clauses) with new syntax introduced in later SQL Server versions, such as inline index options or updated partition syntax. SQL Server 2025’s parser enforces strict separation between syntax generations to maintain clear execution plan behavior and avoid ambiguous option resolution. This is typically caused by copy-pasted scripts from older SQL Server versions combined with newly generated code from updated tools, ORMs, or migration scripts that were not fully normalized to a single syntax standard.

Step-by-Step Resolution Guide

Step 1: Identify the Offending Statement

• Locate the exact CREATE INDEX, ALTER INDEX, or DROP INDEX statement referenced in the error stack or SQL Server error log.
• Check C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG for the exact batch and line number tied to Error 10602.
• Run the statement in isolation inside SQL Server Management Studio (SSMS) or Azure Data Studio to confirm reproduction.

Step 2: Review and Separate Syntax Elements

• Compare the statement against Microsoft’s current T-SQL reference for CREATE INDEX (2025 syntax documentation).
• Identify legacy constructs, such as old-style WITH (DROP_EXISTING) placement, deprecated ON [PRIMARY] filegroup clauses mixed with new inline column store options, or outdated SORT_IN_TEMPDB positioning.
• Rewrite the statement to use exclusively the new unified syntax format (e.g., consolidated WITH (...) option blocks placed after all column and include definitions).

Step 3: Update Source Scripts and Tooling

• If the script originates from an ORM (Entity Framework, Hibernate) or a migration tool, check for outdated database provider versions that still emit legacy T-SQL.
• Update the provider/driver to the latest version compatible with SQL Server 2025.
• Regenerate the migration script and re-validate it does not reintroduce mixed syntax.

Step 4: Apply the Corrected Script

• Execute the corrected, syntax-consistent index statement against the target database.
• Confirm no additional parser errors (10601, 10603) surface during execution.
• Document the corrected script in your source control repository to prevent regression.

Alternative or Verification Steps

• Verify the fix by querying sys.indexes and sys.index_columns to confirm the index was created successfully with the expected structure and options.
• If the error persists, use SET PARSEONLY ON before running the statement in SSMS to isolate pure syntax validation without executing the DDL, helping pinpoint the exact clause causing the conflict.

Troubleshooting Guide: Error 10601 – Cannot Specify Included Columns for a Clustered Index

Why This Error Happens

SQL Server Error 10601 occurs because the INCLUDE clause is only valid for nonclustered indexes, not clustered indexes. A clustered index already physically contains all columns of the table in its leaf level, so there is no concept of a “included” (non-key) column to add separately. This error typically surfaces when a CREATE INDEX or CREATE TABLE script incorrectly applies the INCLUDE syntax to a CLUSTERED index definition, often due to copy-pasted T-SQL templates or auto-generated scripts from migration tools.

Step-by-Step Resolution Guide

Step 1: Identify the Offending T-SQL Statement

• Locate the exact CREATE INDEX or ALTER TABLE statement that triggered Error 10601 in SSMS or your deployment log.
• Check whether the index definition contains both the CLUSTERED keyword and an INCLUDE (...) clause in the same statement.
• Example of invalid syntax:

  CREATE CLUSTERED INDEX IX_Orders_Date
  ON dbo.Orders (OrderDate)
  INCLUDE (CustomerID);

Step 2: Decide on the Correct Index Strategy

• Determine if you actually need a clustered index (defines physical row order) or a nonclustered index (separate structure with optional included columns).
• If you need included columns for query performance (covering index), switch the index type to NONCLUSTERED.
• If you truly need a clustered index, remove the INCLUDE clause entirely since all columns are already “included” by definition.

Step 3: Rewrite the Index Definition

• For a covering index with included columns, use:

  CREATE NONCLUSTERED INDEX IX_Orders_Date
  ON dbo.Orders (OrderDate)
  INCLUDE (CustomerID);

• For a pure clustered index without included columns, use:

  CREATE CLUSTERED INDEX IX_Orders_Date
  ON dbo.Orders (OrderDate);

• Save and re-run the corrected script in SSMS or your deployment pipeline (SSDT/DACPAC, Flyway, etc.).

Step 4: Audit Related Scripts and Templates

• Search your source control repository (Git, TFVC) for similar CREATE CLUSTERED INDEX ... INCLUDE patterns using a text search across .sql files.
• Update any shared index-generation templates or ORM migration scripts (Entity Framework, Liquibase) that may have produced the invalid syntax.
• Re-validate automated database build pipelines on Windows Server 2025 to ensure the corrected scripts are used going forward.

Alternative or Verification Steps

• Verify the fix by re-executing the corrected CREATE INDEX statement in SSMS connected to your SQL Server 2025 instance — it should complete without Error 10601, and you can confirm the index type via sys.indexes (type_desc column should show CLUSTERED or NONCLUSTERED as expected).
• If you are unsure whether columns should be key columns or included columns, as an alternative, create the clustered index on the key columns only, then add a separate nonclustered covering index with the INCLUDE clause for the additional columns needed by your queries.

Troubleshooting Guide: SQL Server Error 10539 — Plan Guide Creation Failure

Why This Error Happens

Error 10539 occurs when SQL Server attempts to create a plan guide from the plan cache (sp_create_plan_guide_from_handle), but the cached query plan referenced by the plan handle has already been evicted or was never fully compiled against the statement’s start offset. This typically happens because the underlying database objects (tables, views, or columns) referenced by the query did not exist at compile time, or because the plan cache was flushed/aged out between the time the plan was captured and when the plan guide creation was attempted. It can also occur if the statement text or offset values no longer match the current cached plan due to schema changes or recompilation.

Step-by-Step Resolution Guide

Step 1: Verify Database Object Existence

• Confirm that all tables, views, functions, and stored procedures referenced in the target query actually exist in the target database.
• Run the following to check object metadata:

SELECT OBJECT_ID('YourSchema.YourTableName') AS ObjectExists;

• If the object is missing, deploy the required schema objects before re-attempting plan guide creation.

Step 2: Re-Validate the Plan Cache Entry

• Query sys.dm_exec_cached_plans and sys.dm_exec_sql_text to confirm the plan handle is still valid and matches the statement offset.

SELECT plan_handle, usecounts, cacheobjtype, objtype
FROM sys.dm_exec_cached_plans
WHERE plan_handle = <your_plan_handle>;

• If the plan handle no longer appears, the cache entry has aged out — you must re-execute the original query to regenerate the plan before creating the guide.

Step 3: Recreate the Plan Guide Using Fresh Execution Context

• Execute the exact original query (with matching SET options) in the target database first so SQL Server compiles a fresh plan.
• Immediately retrieve the new plan handle and statement start offset:

SELECT deqp.query_plan, der.plan_handle, der.statement_start_offset
FROM sys.dm_exec_requests der
CROSS APPLY sys.dm_exec_query_plan(der.plan_handle) deqp;

• Use these updated values in sp_create_plan_guide_from_handle immediately, minimizing delay to avoid cache eviction.

Step 4: Check for Plan Cache Pressure or Eviction Settings

• On Windows Server 2025, open SQL Server Configuration Manager and confirm memory allocation under SQL Server service properties isn’t artificially constrained.
• Review max server memory setting via:

EXEC sp_configure 'max server memory (MB)';

• Increase memory if the plan cache is being aggressively evicted due to memory pressure, which causes plans to disappear before plan guides can reference them.

Step 5: Rebuild the Plan Guide via SQL Statement Text Instead of Handle

• If the handle-based method continues to fail, switch to sp_create_plan_guide using the literal SQL statement text and specify @type = 'SQL' rather than relying on a cached plan handle.
• This bypasses dependency on cache persistence entirely and is more reliable for objects created shortly before guide generation.

Alternative or Verification Steps

• After applying the fix, run sp_control_plan_guide 'DROP ALL' followed by recreation, then validate with SELECT * FROM sys.plan_guides WHERE is_disabled = 0; to confirm the guide is active and enabled.
• If errors persist, use Query Store (enabled via SSMS → Database Properties → Query Store) as an alternative to plan cache handles, since Query Store retains plans persistently and avoids the eviction timing issue entirely.

Troubleshooting Guide: Error 10538 – “Cannot Find the Plan Guide” in SQL Server 2025

Why This Error Happens

Error 10538 occurs when a Transact-SQL statement references a plan guide using sp_control_plan_guide with a plan_guide_id that is either NULL, has already been dropped, or never existed in the current database context. This typically happens due to a mismatch between the database where the plan guide was originally created and the database context of the current session, or because the plan guide was automatically disabled/removed during a schema change, index rebuild, or database upgrade/migration to SQL Server 2025. It can also occur if a script attempts to reference a plan guide by an ID retrieved from a different server or a stale cached value from sys.plan_guides.

Step-by-Step Resolution Guide

Step 1: Verify the Plan Guide Actually Exists

• Connect to the correct database context (plan guides are database-scoped, not server-scoped).
• Run the following query to list all active plan guides and their IDs:

  SELECT plan_guide_id, name, is_disabled, scope_type_desc 
  FROM sys.plan_guides;

• Confirm the plan_guide_id you are referencing in your script actually appears in this result set.

Step 2: Check for Database Context Mismatch

• Ensure your session is pointed to the correct database using USE [YourDatabaseName]; before executing sp_control_plan_guide.
• Plan guide IDs are not unique across databases—an ID of 5 in DB_A is unrelated to ID 5 in DB_B.
• If you migrated the database to Windows Server 2025, verify compatibility level using:

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

Step 3: Drop and Recreate the Plan Guide

• If the plan guide ID is confirmed missing (likely dropped during an ALTER INDEX REBUILD or statistics update), recreate it using sp_create_plan_guide.
• Example syntax:

  EXEC sp_create_plan_guide 
      @name = N'Guide1',
      @stmt = N'SELECT * FROM dbo.Orders WHERE OrderID = @id',
      @type = N'SQL',
      @module_or_batch = NULL,
      @params = N'@id int',
      @hints = N'OPTION (OPTIMIZE FOR (@id = 1))';

• Retrieve the new plan_guide_id immediately after creation to update any hardcoded references in your scripts or stored procedures.

Step 4: Clear Stale Plan Cache and Metadata References

• If the error persists despite the plan guide existing, flush the procedure cache to eliminate stale pointers (use with caution in production):

  DBCC FREEPROCCACHE;

• Restart the SQL Server service via Services.msc on Windows Server 2025 if cache clearing does not resolve the reference error.

Alternative or Verification Steps

• Verify the Fix: Re-run sp_control_plan_guide @operation = N'ENABLE', @name = N'Guide1'; using the name instead of the ID to bypass ID-mismatch issues entirely.
• Alternative Solution: If plan guides continue to fail intermittently after the SQL Server 2025 upgrade, consider migrating the logic to Query Store Hints (a newer feature), which replaces legacy plan guide functionality with more stable, database-engine-tracked query hints.

Troubleshooting Guide: SQL Server Error 10537 – Duplicate Plan Guide Scope Conflict

Why This Error Happens

This error occurs because SQL Server enforces a uniqueness constraint on plan guides at the same scope (database, object, or batch) and the same starting offset within a query statement. When a new plan guide is created or enabled with sp_control_plan_guide, SQL Server checks the sys.plan_guides catalog view and detects that another plan guide already active for that exact statement signature conflicts with it. This typically results from manual plan guide management, automated query tuning scripts, or deployment pipelines that re-apply plan guides without first disabling or dropping the previous version.

Step-by-Step Resolution Guide

Step 1: Identify the Conflicting Plan Guide

• Run the following query against the target database to list all active plan guides:

  SELECT plan_guide_id, name, is_disabled, scope_type_desc, statement_start_offset
  FROM sys.plan_guides;

• Note the name and plan_guide_id of the plan guide referenced in the error message (the one matching '%.*ls' for the “enabled” guide).
• Cross-reference statement_start_offset to confirm it matches the offset of the guide you are trying to enable.

Step 2: Disable the Existing Conflicting Plan Guide

• Use sp_control_plan_guide with the DISABLE parameter and the exact name captured in Step 1:

  EXEC sp_control_plan_guide N'DISABLE', N'ExistingPlanGuideName';

• Confirm the disable operation succeeded by re-running the sys.plan_guides query and checking that is_disabled = 1 for that row.
• If the old plan guide is obsolete rather than temporarily conflicting, consider dropping it instead:

  EXEC sp_control_plan_guide N'DROP', N'ExistingPlanGuideName';

Step 3: Enable the New Plan Guide

• Once the conflicting guide is disabled (or dropped), enable the intended plan guide:

  EXEC sp_control_plan_guide N'ENABLE', N'NewPlanGuideName';

• Verify no error is returned in SQL Server Management Studio (SSMS) or Azure Data Studio output pane.
• If deploying via a script or CI/CD pipeline on Windows Server 2025, ensure the deployment order explicitly disables old guides before applying new ones to avoid race conditions.

Step 4: Review Plan Guide Creation Logic

• Audit any automated tuning scripts, Query Store forced plans, or third-party optimization tools that generate plan guides programmatically.
• Ensure the logic checks for existing guides at the same scope/offset using sys.plan_guides before attempting to create or enable a new one, preventing recurrence of Error 10537.

Alternative or Verification Steps

• Verify the Fix: Query sys.plan_guides again and confirm only the intended plan guide shows is_disabled = 0 for that specific statement scope and offset; test the associated query to confirm the correct execution plan is applied via sys.dm_exec_query_plan.
• Alternative Approach: If managing multiple overlapping plan guides becomes unwieldy, consider migrating to Query Store forced plans (sp_query_store_force_plan) instead of legacy plan guides, as Query Store handles plan versioning and conflicts more gracefully in SQL Server 2025.

Troubleshooting Guide: Error 10536 in SQL Server 2025

Why This Error Happens

This error occurs because SQL Server’s sp_create_plan_guide_from_handle procedure has a hard-coded limitation of 1,000 eligible statements per batch or module when generating a plan guide directly from a plan handle. When the target stored procedure, trigger, or ad-hoc batch contains more than 1,000 statements eligible for guide creation, SQL Server cannot process the entire cached plan as a single unit. This is a design constraint rather than a permissions or corruption issue, and it typically surfaces in environments with very large, auto-generated, or heavily nested T-SQL modules.

Step-by-Step Resolution Guide

Step 1: Identify the Statement Count and Offsets

• Run DBCC FREEPROCCACHE carefully (in a test environment only) or query sys.dm_exec_query_plan and sys.dm_exec_sql_text using the problematic @plan_handle to inspect the batch.
• Use sys.dm_exec_plan_attributes or parse the XML showplan to count individual statements within the module.
• Note that each statement has a statement_start_offset and statement_end_offset value embedded in the execution plan metadata.

Step 2: Extract Statement-Level Offsets

• Query the XML plan output (via sys.dm_exec_text_query_plan) to pull out each StmtSimple or StmtCond node.
• Record the StatementStartOffset and StatementEndOffset attributes for every individual statement in the batch.
• Export this list into a working table or temporary script file (e.g., C:\SQLScripts\PlanGuideOffsets.sql) for iteration.

Step 3: Create Plan Guides Per Statement

• Instead of calling sp_create_plan_guide_from_handle on the entire batch, loop through each recorded offset pair.
• Execute sp_create_plan_guide_from_handle individually for each statement, passing the specific @statement_start_offset parameter:
• EXEC sp_create_plan_guide_from_handle @name = N'PG_Statement1', @plan_handle = @handle, @statement_start_offset = 0;
• Repeat this call for each offset value captured in Step 2, incrementing the plan guide name (e.g., PG_Statement2, PG_Statement3).

Step 4: Automate the Batch Process (Optional)

• If the module exceeds 1,000 statements, write a PowerShell or T-SQL cursor script to automate the per-statement loop to avoid manual errors.
• Save the script under C:\SQLScripts\AutoPlanGuideGenerator.ps1 and schedule it via SQL Server Agent or Windows Task Scheduler if this needs to run periodically after deployments.

Step 5: Validate Plan Guide Creation

• Query sys.plan_guides to confirm the number of newly created guides matches the number of statements processed.
• Check the is_disabled column to ensure none of the guides failed silently during creation.

Alternative or Verification Steps

• Verify success by running SELECT * FROM sys.plan_guides WHERE name LIKE 'PG_Statement%'; and confirming the row count aligns with your original statement count from Step 1.
• If per-statement creation is still impractical due to extreme complexity, consider refactoring the oversized stored procedure into smaller, modular sub-procedures, which naturally keeps each unit under the 1,000-statement threshold.

Troubleshooting Guide: SQL Server Error 10535 – Plan Guide Creation Failure

Why This Error Happens

Error 10535 occurs when the sp_create_plan_guide_from_handle procedure is called with a plan handle that no longer exists in the SQL Server plan cache. This typically happens because the cached execution plan was evicted due to memory pressure, a server restart, a manual cache clear (DBCC FREEPROCCACHE), or simply because too much time elapsed between retrieving the handle and using it. Since plan handles are ephemeral pointers to in-memory objects, any recompilation or cache flush invalidates them immediately.

Step-by-Step Resolution Guide

Step 1: Verify the Plan Handle Still Exists

• Run the following query to confirm whether the handle is currently cached:
• SELECT * FROM sys.dm_exec_cached_plans WHERE plan_handle = <your_plan_handle>;
• If this returns zero rows, the handle is stale and must be regenerated.

Step 2: Regenerate a Fresh Plan Handle

• Re-execute the original query or stored procedure in the same session/context to force SQL Server to recompile and cache a new plan.
• Immediately query sys.dm_exec_query_stats or sys.dm_exec_cached_plans to capture the new, valid plan_handle before it ages out.
• Minimize the delay between capturing the handle and running sp_create_plan_guide_from_handle.

Step 3: Check for Aggressive Cache Eviction Settings

• Inspect max server memory configuration in sp_configure to ensure SQL Server isn’t under memory pressure causing premature plan eviction.
• Review Windows Server 2025 Resource Governor or container memory limits if running SQL Server in a constrained environment (e.g., Docker/Kubernetes on Windows 11).
• Check for scheduled jobs or scripts calling DBCC FREEPROCCACHE that might be clearing the cache before your plan guide creation runs.

Step 4: Use an Alternative Plan Guide Creation Method

• If handle-based creation keeps failing due to volatility, switch to sp_create_plan_guide using a static SQL text match and OPTION XML hint instead of relying on a live cache handle.
• Path example: Use SSMS 21 (compatible with SQL Server 2025) to script out the query and manually define the plan guide via T-SQL rather than runtime handle capture.

Alternative or Verification Steps

• Confirm the fix by running SELECT * FROM sys.plan_guides WHERE name = '<your_plan_guide_name>'; — a successful row return confirms the guide was created and is enabled.
• If the issue persists under high-concurrency workloads, consider using Query Store (enabled via ALTER DATABASE <db> SET QUERY_STORE = ON;) to force plans more reliably instead of depending on transient plan cache handles.

Troubleshooting Guide: SQL Server Error 10534 – Invalid @params Value for Plan Guide

Why This Error Happens

Error 10534 occurs when the @params argument supplied to the sp_create_plan_guide stored procedure does not conform to the required Transact-SQL variable declaration syntax (e.g., N'@variable_name data_type'). This typically happens due to malformed strings, missing data type declarations, incorrect delimiters (commas vs. semicolons), or mismatched Unicode string prefixes (N). It can also occur if the parameter names listed in @params do not match the parameterized literals found in the @stmt query string exactly.

Step-by-Step Resolution Guide

Step 1: Validate the Syntax of the @params String

• Ensure the value is wrapped in a Unicode string literal using the N prefix (e.g., N'@P1 int, @P2 varchar(50)').
• Verify that each parameter definition follows the pattern @ParamName DataType and is separated by a comma.
• Check for stray quotation marks or unescaped characters that might truncate the string early.

Step 2: Cross-Reference Parameters with the @stmt Query

• Open the original query passed via @stmt and identify every literal value intended for parameterization.
• Confirm that the parameter names in @params exist in the exact same order and quantity as the placeholders in the @module_or_batch or @stmt text.
• Run the query manually in SQL Server Management Studio (SSMS) on Windows 11/Server 2025 to check for syntax errors unrelated to the plan guide itself.

Step 3: Recreate the Plan Guide via SSMS GUI

• Launch SSMS and navigate to Database > Programmability > Plan Guides.
• Right-click and select New Plan Guide to use the graphical wizard instead of raw T-SQL, which auto-formats the @params string correctly.
• Save the script generated by the wizard to compare it against your original manual script for syntax discrepancies.

Step 4: Check Compatibility Level and Permissions

• Verify the database compatibility level is supported for plan guides (SQL Server 2025 requires compatibility level 90 or higher).
• Ensure the executing user has the ALTER ANY DATABASE DDL TRIGGER or db_owner permissions required to create objects in the Query Store/Plan Guide cache.
• Restart the SQL Server service via services.msc on Windows Server 2025 if permission changes were recently applied to force a token refresh.

Alternative or Verification Steps

• Run sp_help_plan_guide after creation to confirm the guide is listed as “Enabled” without validation errors.
• If the error persists, use sp_create_plan_guide_from_handle instead of manually typing the @params string, as this automatically extracts the correct parameter syntax from the plan cache.

Troubleshooting Guide: SQL Server Error 10533 — Plan Guide Name Exceeds 124 Characters

Why This Error Happens

This error occurs because SQL Server enforces a strict 124-character limit on the name parameter used when creating a plan guide via sp_create_plan_guide. The limit exists due to internal metadata storage constraints in the sys.plan_guides system catalog view, which allocates a fixed-length field for plan guide identifiers. This is typically caused by dynamically generated or auto-concatenated plan guide names (often produced by third-party tools, ORM frameworks, or scripts that append query hashes, timestamps, or object names) that exceed the allowed length.

Step-by-Step Resolution Guide

Step 1: Identify the Offending Plan Guide Creation Statement

• Review the error message output to capture the full plan guide name that triggered Error 10533.
• Check the calling script, stored procedure, or application log (e.g., SSMS query history or SQL Profiler trace) for the exact sp_create_plan_guide call.
• Run the following to search recent query batches if using Extended Events:
• SELECT * FROM sys.fn_xe_file_target_read_file('system_health*.xel', NULL, NULL, NULL);

Step 2: Shorten the Plan Guide Name

• Manually edit the @name parameter in the sp_create_plan_guide call to be 124 characters or fewer.
• Replace verbose naming conventions (e.g., full query text hashes or long object paths) with a shortened, unique identifier such as a GUID substring or sequential counter.
• Example corrected syntax:
• EXEC sp_create_plan_guide @name = N'PG_OrderQuery_001', @stmt = N'SELECT * FROM Orders WHERE OrderID = @1', @type = N'OBJECT', @module_or_batch = N'dbo.GetOrder', @params = NULL, @hints = N'OPTION (RECOMPILE)';

Step 3: Update Automation Scripts or ORM Configuration

• If the plan guide name is generated dynamically (e.g., via Entity Framework, Hibernate, or a custom deployment script), locate the naming logic in the source code or configuration file.
• Add a truncation function or hashing mechanism (e.g., SUBSTRING(@name, 1, 124) or a SHA-1 hash converted to a short string) to enforce the 124-character cap before the sp_create_plan_guide call executes.
• Redeploy the updated script or application build to Windows Server 2025 / Windows 11 hosting the SQL Server 2025 instance.

Step 4: Clean Up Orphaned or Duplicate Plan Guides

• Query sys.plan_guides to check for existing entries with near-limit-length names that may indicate a pattern causing repeated failures:
• SELECT name, LEN(name) AS name_length FROM sys.plan_guides ORDER BY name_length DESC;
• Drop any obsolete or test plan guides using EXEC sp_control_plan_guide N'DROP', N'<plan_guide_name>'; to reduce clutter and confirm naming conventions moving forward.

Alternative or Verification Steps

• Re-run the original sp_create_plan_guide statement with the shortened name and confirm success by querying SELECT * FROM sys.plan_guides WHERE name = '<new_name>'; — the row should appear without errors.
• If the issue persists in automated pipelines, implement a pre-execution validation check (e.g., a PowerShell or T-SQL guard clause) that rejects any plan guide name over 124 characters before submission to SQL Server.

Troubleshooting Guide: SQL Server Error 10532

Why This Error Happens

Error 10532 occurs when sp_create_plan_guide_from_handle is called with a @plan_handle that points to a cached plan containing statements ineligible for plan guide creation, such as batches with multiple statements where the target statement cannot be isolated, or statements that already have a plan guide attached. This can also happen if the cached plan has aged out or been recompiled between the time you retrieved the plan handle and the time you executed the plan guide creation statement, resulting in a mismatch. Additionally, certain statement types (like DDL, cursor declarations, or control-of-flow statements) are inherently unsupported targets for plan guides regardless of how the handle was obtained.

Step-by-Step Resolution Guide

Step 1: Verify the Plan Handle Is Still Valid and Eligible

• Run SELECT * FROM sys.dm_exec_cached_plans WHERE plan_handle = @plan_handle to confirm the plan still exists in cache.
• Use sys.dm_exec_sql_text(@plan_handle) and sys.dm_exec_query_plan(@plan_handle) to inspect the actual statement text and plan XML.
• Confirm the statement is a standalone SELECT, INSERT, UPDATE, or DELETE — not a batch containing control-of-flow (IF/WHILE), DDL, or transaction control statements.

Step 2: Use @statementstartoffset and @statementendoffset Correctly

• If the cached plan is for a multi-statement batch, you must pass the exact @statement_start_offset and @statement_end_offset values pulled from sys.dm_exec_query_stats for the specific statement.
• Query: SELECT statement_start_offset, statement_end_offset, plan_handle FROM sys.dm_exec_query_stats WHERE plan_handle = @plan_handle;
• Pass these offsets explicitly into sp_create_plan_guide_from_handle rather than assuming the whole batch is targetable.

Step 3: Switch to Object-Based or SQL-Based Plan Guide Creation

• If the handle-based approach keeps failing, use sp_create_plan_guide (with @type = 'OBJECT' or @type = 'SQL') instead of sp_create_plan_guide_from_handle.
• Provide the exact literal @stmt text matching the query as it appears in the module, including matching whitespace and casing if @type = 'SQL' is used with @module_or_batch.
• For stored procedures, target the specific statement inside the procedure using @type = 'OBJECT' and the @module_or_batch parameter set to the procedure name.

Step 4: Clear and Recapture the Plan Cache If Handle Is Stale

• If the plan has likely aged out of cache (common on Windows Server 2025 under memory pressure or with max server memory misconfigured), re-run the original query/procedure to repopulate the cache.
• Immediately capture a fresh @plan_handle via sys.dm_exec_query_stats or sys.dm_exec_cached_plans before attempting plan guide creation again, minimizing the delay between capture and execution.
• Avoid running DBCC FREEPROCCACHE in between these steps, as this invalidates the handle you are trying to use.

Step 5: Check for Existing Conflicting Plan Guides

• Run SELECT * FROM sys.plan_guides WHERE scope_batch = '<your statement>' to check if a plan guide already exists for that statement.
• Drop any conflicting guide first using sp_control_plan_guide @operation = 'DROP', @name = N'<guide_name>' before attempting to recreate it.

Alternative or Verification Steps

• After creation, verify success by querying SELECT name, is_disabled, scope_type_desc FROM sys.plan_guides WHERE name = '<your_guide_name>' and confirm is_disabled = 0.
• If handle-based creation continues to fail, use Query Store (enabled by default in SQL Server 2025) as an alternative — force a known good plan via sp_query_store_force_plan instead of relying on legacy plan guides.

Troubleshooting Guide: Error 10531 – Cannot Create Plan Guide from Cache

Why This Error Happens

Error 10531 occurs when a user attempts to create a plan guide from the plan cache using the sp_create_plan_guide_from_handle system stored procedure without holding the required VIEW SERVER STATE permission. This permission is mandatory because SQL Server needs to inspect server-wide execution plan cache metadata to extract the cached plan, and this action is restricted to prevent unauthorized visibility into other sessions’ query activity. In Microsoft SQL Server 2025, tightened default security roles on Windows Server 2025/Windows 11 installations often mean even sysadmin-adjacent accounts may lack this explicit grant unless manually assigned.

Step-by-Step Resolution Guide

Step 1: Verify the Current Permission State

• Open SQL Server Management Studio (SSMS) and connect to the target instance.
• Run the following query to check existing permissions for the affected login:

  SELECT * FROM sys.fn_my_permissions(NULL, 'SERVER') 
  WHERE permission_name = 'VIEW SERVER STATE';

• If no rows are returned, the permission is confirmed missing.

Step 2: Grant VIEW SERVER STATE Permission

• Execute the following T-SQL command as a sysadmin or equivalent privileged account:

  USE master;
  GRANT VIEW SERVER STATE TO [DOMAIN\Username or LoginName];

• For SQL Server 2025 environments using the newer granular permission model, you may alternatively assign the more specific permission:

  GRANT VIEW SERVER PERFORMANCE STATE TO [DOMAIN\Username or LoginName];

• Ensure the login is mapped correctly under Security > Logins in SSMS if the grant fails silently.

Step 3: Refresh the Session and Re-run the Plan Guide Creation

• Have the user log out and reconnect to SQL Server to refresh their permission token.
• Re-execute the original sp_create_plan_guide_from_handle command that previously failed.
• Confirm no Error 10531 is thrown in the Messages tab of SSMS.

Step 4: Check Group Policy and Windows Authentication Conflicts

• On Windows Server 2025, verify that Group Policy Objects (GPOs) are not overriding local security token caching, which can delay permission propagation.
• Run gpupdate /force from an elevated Command Prompt if the login is domain-managed.
• Restart the SQL Server service via SQL Server Configuration Manager only if permission changes still do not reflect after a session refresh.

Alternative or Verification Steps

• Verify the fix by querying sys.fn_my_permissions again post-grant — it should now return the VIEW SERVER STATE row for the user.
• As an alternative, if granting server-wide state visibility is not desirable for security reasons, have a sysadmin user create the plan guide on behalf of the requesting user and export/import it using sp_control_plan_guide.