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

by

Troubleshooting Guide: SQL Server Error 4189 — Legacy LOB Type Encoding Conflict

Why This Error Happens

This error occurs because SQL Server 2025 attempts to apply a UTF-8 or UTF-16 collation to a legacy Large Object (LOB) column of type text or ntext. These deprecated data types were architecturally never updated to support variable-width Unicode encodings like UTF-8, as they rely on older fixed-width storage mechanisms. Consequently, when a database or column-level collation change triggers an implicit conversion on these old types, the engine blocks the operation to prevent data corruption.

Step-by-Step Resolution Guide

Step 1: Identify the Offending Columns and Collation

  • Run a metadata query to find every instance of text or ntext in your schema, as these are the root cause.
  • Use the following T-SQL to scan your current database:
SELECT t.name AS TableName, c.name AS ColumnName, ty.name AS DataType
FROM sys.columns c
JOIN sys.tables t ON c.object_id = t.object_id
JOIN sys.types ty ON c.user_type_id = ty.user_type_id
WHERE ty.name IN ('text', 'ntext');
  • Note the specific UTF-8 collation (e.g., Latin1_General_100_CI_AS_SC_UTF8) you are trying to apply, as this is likely the trigger.

Step 2: Migrate Legacy Columns to Modern Types

  • Alter the table schema to replace the legacy types with their modern, UTF-8 compatible equivalents.
  • For text columns, convert to varchar(max).
  • For ntext columns, convert to nvarchar(max).
  • Execute the alteration via SSMS or command line:
ALTER TABLE YourTableName
ALTER COLUMN YourColumnName VARCHAR(MAX);
  • Ensure no active triggers or replication jobs are locking the table during this schema change on Windows Server 2025.

Step 3: Re-apply the Collation Change

  • Once the schema conversion is complete, re-run the original command that triggered the error (e.g., ALTER DATABASE... COLLATE or ALTER TABLE... COLLATE).
  • Verify there are no remaining dependencies, such as Full-Text Indexes, pointing to the old LOB columns before proceeding.
  • If a Full-Text Index exists, you must drop it temporarily using DROP FULLTEXT INDEX ON YourTableName before altering the column type, then recreate it post-migration.

Step 4: Restart the SQL Server Service

  • Navigate to the Services manager on Windows Server 2025 (services.msc) to clear any cached execution plans referencing the old schema.
  • Restart the “SQL Server (MSSQLSERVER)” service to ensure the Database Engine recognizes the new column definitions globally.

Alternative or Verification Steps

  • Verification: Re-run the metadata query from Step 1; if the result set is empty, no legacy LOB types remain, and the collation change should succeed without Error 4189.
  • Alternative: If altering the column directly causes constraint violations, create a new varchar(max) column, migrate the data using an UPDATE statement, drop the old text column, and rename the new column to match the original name.