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

by

Troubleshooting Guide: Error 2774 – “Collation ID %d is invalid”

Why This Error Happens

This error occurs when SQL Server 2025 encounters a collation ID in system metadata that does not correspond to any collation registered in the server’s internal collation table, typically due to database corruption, an incomplete upgrade/migration process, or mismatched system catalog entries. It can also surface when a database is restored or attached from a different SQL Server version or build that supports a collation ID not recognized by the current instance. Additionally, corrupted rowset or sys.columns metadata pages referencing invalid collation IDs will trigger this error during query compilation or DBCC validation.

Step-by-Step Resolution Guide

Step 1: Verify SQL Server Build and Collation Compatibility

  • Confirm the SQL Server 2025 build number via SELECT @@VERSION; and compare against the source server (if this is a migrated/restored database).
  • Check the database’s current collation using SELECT name, collation_name FROM sys.databases;.
  • Ensure both source and destination servers use matching or compatible Cumulative Update (CU) levels, since collation tables can change between builds.

Step 2: Run Database Consistency Checks

  • Open a new query window in SSMS connected to the affected instance.
  • Execute DBCC CHECKDB('YourDatabaseName') WITH NO_INFOMSGS, ALL_ERRORSMSG; to identify corrupted pages referencing invalid collation IDs.
  • Review the output for errors referencing sys.columns, sys.types, or metadata corruption tied to collation IDs.

Step 3: Repair or Rebuild Affected Objects

  • If DBCC CHECKDB reports repairable errors, run DBCC CHECKDB('YourDatabaseName') WITH REPAIR_ALLOW_DATA_LOSS; only after taking a full backup, as this option can delete corrupted rows.
  • For specific tables with bad collation metadata, consider scripting out the schema and data, then recreating the table with an explicit valid collation (e.g., SQL_Latin1_General_CP1_CI_AS).
  • Restart the SQL Server service via services.msc or Restart-Service MSSQLSERVER in PowerShell (run as Administrator) to clear cached metadata.

Step 4: Rebuild System Databases (If Corruption Persists)

  • If the error originates from the master database, run the SQL Server 2025 setup executable with the /ACTION=REBUILDDATABASE parameter from an elevated Command Prompt.
  • Example: Setup.exe /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=MSSQLSERVER /SQLSYSADMINACCOUNTS=BUILTIN\Administrators.
  • Restore user databases from a known-good backup after the rebuild completes.

Alternative or Verification Steps

  • After remediation, re-run DBCC CHECKDB('YourDatabaseName') WITH NO_INFOMSGS; and confirm zero allocation or consistency errors are returned.
  • If the error persists only on specific queries, test with sp_help 'TableName' to confirm all columns report a valid, recognized collation name instead of a null or unresolved ID.