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

by

Troubleshooting Guide: Error 5381 – Internal Error During Index On Expression Creation (SQL Server 2025)

Why This Error Happens

Error 5381 typically occurs when SQL Server 2025’s query processor encounters an internal failure while computing or persisting metadata for an Index On Expression (computed/expression-based index), often due to a corrupted plan cache, an incompatible database compatibility level, or a malformed expression that the optimizer cannot resolve during index build. This can also surface if the underlying table schema was altered mid-transaction or if system catalog views (sys.index_columns, sys.computed_columns) contain stale or orphaned metadata. In rarer cases, insufficient ALTER permissions on the schema combined with a corrupted tempdb system database can trigger this internal fault.

Step-by-Step Resolution Guide

Step 1: Verify Database Compatibility Level and Expression Syntax

  • Open SQL Server Management Studio (SSMS) and run SELECT compatibility_level FROM sys.databases WHERE name = 'YourDBName';
  • Ensure compatibility level is set to 170 (SQL Server 2025) using: ALTER DATABASE YourDBName SET COMPATIBILITY_LEVEL = 170;
  • Review the expression used in the CREATE INDEX ... ON (expression) statement for unsupported functions (e.g., non-deterministic functions like GETDATE() are not allowed in expression indexes).

Step 2: Clear Corrupted Plan Cache and Restart Related Services

  • Run DBCC FREEPROCCACHE; to clear any corrupted cached execution plans tied to the index operation.
  • Open Services.msc on Windows Server 2025 / Windows 11, locate SQL Server (MSSQLSERVER), right-click, and select Restart.
  • Alternatively, use PowerShell: Restart-Service -Name 'MSSQLSERVER' -Force

Step 3: Rebuild TempDB System Database

  • Internal errors during index creation are frequently linked to a degraded tempdb. Check tempdb health via DBCC CHECKDB(tempdb);
  • If corruption is found, stop the SQL Server service, delete the tempdb files located at C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\DATA\tempdb.mdf, and restart the service to force tempdb regeneration.

Step 4: Validate Schema and Table Permissions

  • Confirm the executing user has ALTER and REFERENCES permissions on the target table and schema: GRANT ALTER ON SCHEMA::dbo TO [YourUser];
  • Check for orphaned computed column metadata using: SELECT * FROM sys.computed_columns WHERE object_id = OBJECT_ID('YourTable');
  • Drop and recreate the computed column expression if metadata inconsistencies are found.

Step 5: Apply Latest Cumulative Update (CU)

  • Navigate to the Microsoft Update Catalog and download the latest Cumulative Update for SQL Server 2025.
  • Run the installer with elevated privileges and confirm the build number post-install via SELECT @@VERSION;

Alternative or Verification Steps

  • After applying fixes, re-run the original CREATE INDEX statement with the expression in a test environment to confirm Error 5381 no longer appears in the SQL Server Error Log (C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG).
  • If the error persists, bypass the expression index temporarily by creating a persisted computed column first, then build a standard non-expression index on that column as a workaround.