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

by

Troubleshooting Guide: SQL Server Error 1806 — CREATE DATABASE Collation Failure

Why This Error Happens

This error occurs when the collation name specified in a CREATE DATABASE statement is either misspelled, deprecated, or unsupported by the SQL Server 2025 instance’s installed language resources. It can also surface if the server-level default collation was configured incorrectly during setup, or if the database is attempting to inherit a collation that conflicts with the underlying Windows Server 2025 locale settings. Additionally, corrupted or incomplete installation of SQL Server’s collation metadata files can prevent the engine from validating the requested collation string.

Step-by-Step Resolution Guide

Step 1: Verify the Exact Collation Name Syntax

  • Run SELECT * FROM fn_helpcollations(); in SSMS to pull a list of all valid collation names supported by your instance.
  • Compare this list against the exact string used in your CREATE DATABASE statement — check for typos, extra spaces, or outdated collation names (e.g., using a SQL Server 2008 collation name that was renamed in 2025).
  • Example corrected syntax: CREATE DATABASE MyDB COLLATE SQL_Latin1_General_CP1_CI_AS;

Step 2: Check the Server’s Default Collation Settings

  • Open a query window and run SELECT SERVERPROPERTY('Collation'); to confirm the instance-level default.
  • If the server-level collation is incompatible with the one you’re requesting, decide whether to explicitly override it at the database level or reinstall with the correct server collation.
  • Navigate to Control Panel > Region > Administrative > Language for non-Unicode programs on Windows Server 2025 to ensure the OS locale matches the intended SQL collation family.

Step 3: Repair Corrupted Collation Metadata Files

  • Close SQL Server services via services.msc or run net stop MSSQLSERVER in an elevated Command Prompt.
  • Navigate to C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Binn\ and verify that sqlserver.rll and related resource files are present and not zero-byte.
  • Launch SQL Server Setup, choose Repair, and select the affected instance to restore missing or corrupted collation resource files.
  • Restart the service with net start MSSQLSERVER.

Step 4: Reconfigure the Instance for Correct Collation (If Required)

  • If the collation truly needs to change at the instance level, back up all databases first.
  • Run setup from an elevated Command Prompt using: setup.exe /QUIET /ACTION=rebuilddatabase /INSTANCENAME=MSSQLSERVER /SQLSYSADMINACCOUNTS=<YourAdminAccount> /SAPWD=<Password> /SQLCOLLATION=<DesiredCollation>
  • Confirm post-rebuild collation with SELECT SERVERPROPERTY('Collation');.

Alternative or Verification Steps

  • After applying fixes, re-run your original CREATE DATABASE command with the explicit COLLATE clause and confirm it completes without Error 1806.
  • As a quick alternative, omit the COLLATE clause entirely and let the database inherit the server’s default collation — this bypasses mismatches entirely if a custom collation isn’t a strict requirement.