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

by

Troubleshooting Guide: Error 11428 — Column Cannot Be Altered Online to XML Type with Schema Collection

Why This Error Happens

This error occurs because SQL Server’s online indexing and schema modification engine does not support converting a column to an XML data type bound to a schema collection while the table remains accessible for concurrent reads/writes. The ONLINE = ON option conflicts with the internal locking mechanism required to validate and apply XML schema collection constraints during the ALTER COLUMN operation. SQL Server 2025 enforces this restriction strictly to prevent data integrity issues that could arise from partial schema validation on live data.

Step-by-Step Resolution Guide

Step 1: Identify the Offending ALTER Statement

  • Locate the T-SQL script or application code issuing the ALTER TABLE ... ALTER COLUMN command.
  • Check for syntax similar to: ALTER TABLE dbo.YourTable ALTER COLUMN YourColumn XML(YourSchemaCollection) WITH (ONLINE = ON);
  • Confirm the target column is being converted to XML with a bound schema collection, not a plain XML type.

Step 2: Modify the Command to Run Offline

  • Remove or change the ONLINE option from ON to OFF in the ALTER statement.
  • Updated syntax example: ALTER TABLE dbo.YourTable ALTER COLUMN YourColumn XML(YourSchemaCollection) WITH (ONLINE = OFF);
  • If no WITH (ONLINE = ON) clause is explicitly present, check server-level or database-scoped configurations forcing online operations by default via ALTER DATABASE SCOPED CONFIGURATION SET ELEVATE_ONLINE = ON.

Step 3: Schedule a Maintenance Window for Offline Execution

  • Since this operation requires an offline schema lock, plan execution during low-traffic hours to minimize disruption.
  • Open SQL Server Management Studio (SSMS) or Azure Data Studio and connect to the instance.
  • Execute the corrected ALTER statement directly against the database; expect a brief table lock (Sch-M) during execution.

Step 4: Validate Schema Collection Binding

  • Before re-running the ALTER command, verify the XML schema collection exists and is valid using: SELECT * FROM sys.xml_schema_collections WHERE name = 'YourSchemaCollection';
  • Ensure the collection is not corrupted or missing namespace components, as this can trigger secondary errors after bypassing 11428.

Step 5: Apply the Change and Monitor

  • Run the offline ALTER COLUMN statement in a transaction block if rollback capability is needed.
  • Monitor the Activity Monitor or query sys.dm_exec_requests in a separate session to confirm the operation completes without blocking critical workloads.

Alternative or Verification Steps

  • Verify the fix by querying sys.columns and sys.xml_schema_collections to confirm the column now reflects the correct system_type_id and xml_collection_id values post-alteration.
  • If offline execution is not feasible due to uptime SLAs, consider an alternative approach: create a new column with the XML schema type, migrate data via a batched UPDATE script, then drop the old column and rename the new one—avoiding the ALTER COLUMN restriction entirely.