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

by

Why This Error Happens

Error 4160 occurs when SQL Server 2025 needs to perform “delayed fetching” of a large CLR-based column (such as a geography, geometry, hierarchyid, or custom CLR UDT) but cannot locate a unique key or suitable index on the base table to retrieve that row efficiently. This typically happens when the table lacks a primary key, unique index, or clustered index, forcing the query processor to fail when it tries to defer-load large CLR values during execution plans, DBCC CHECKDB operations, or replication snapshot processes. It can also surface if existing indexes have become fragmented, disabled, or are missing due to a schema change that was not fully propagated to dependent objects.

Step-by-Step Resolution Guide

Step 1: Identify the Affected Table and CLR Column

  • Run the failing query again with SET SHOWPLAN_XML ON or capture the full error text — it will include the table name in place of %.*ls.
  • Use sp_help 'TableName' in SQL Server Management Studio (SSMS) 21 to list all columns and confirm which one uses a CLR data type (e.g., geography, geometry, hierarchyid, or a custom SQLCLR UDT).
  • Check existing keys with:
  EXEC sp_helpindex 'TableName';

Step 2: Add a Primary Key or Unique Index

  • If the table has no primary key, add one on an appropriate integer or GUID column:
  ALTER TABLE dbo.TableName
  ADD CONSTRAINT PK_TableName PRIMARY KEY CLUSTERED (ID);
  • If a primary key already exists but is non-clustered only, consider adding a clustered index, since delayed CLR fetching relies on a clustered or unique key path to re-locate the row.
  • Avoid using the CLR column itself as part of the key — CLR UDTs are not reliably comparable for indexing purposes.

Step 3: Rebuild Indexes and Update Statistics

  • After adding the key, rebuild all indexes on the table to ensure the engine recognizes the new key structure:
  ALTER INDEX ALL ON dbo.TableName REBUILD;
  UPDATE STATISTICS dbo.TableName;
  • This step resolves cases where metadata was stale after a prior schema modification.

Step 4: Validate CLR Assembly and Database Integrity

  • Confirm the CLR assembly backing the UDT is still registered and in a healthy state:
  SELECT name, permission_set_desc, is_user_defined
  FROM sys.assemblies;
  • Run DBCC CHECKDB('YourDatabase') WITH NO_INFOMSGS, ALL_ERRORMSGS; to rule out underlying corruption that may be interfering with the delayed fetch mechanism.
  • On Windows Server 2025 / Windows 11 hosts, also confirm the SQL Server service account has read/execute permissions on the CLR assembly DLL path under C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Binn\ if assemblies are loaded EXTERNAL_ACCESS or UNSAFE.

Step 5: Restart the SQL Server Service

  • After applying the key and rebuilding indexes, restart the instance to clear cached query plans referencing the old table structure:
  Restart-Service -Name 'MSSQLSERVER' -Force
  • This ensures the plan cache no longer holds a stale execution plan that triggered the original 4160 error.

Alternative or Verification Steps

  • Re-run the original query or job that triggered Error 4160; it should now execute without failure, and sp_helpindex should confirm a valid primary/unique key exists.
  • If the error persists, drop and recreate the CLR column’s parent table (in a test environment first) with a proper primary key defined at creation time, then re-import data — this resolves rare cases where legacy schema migrations left orphaned key metadata.