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

by

Why This Error Happens

Error 5073 occurs when SQL Server 2025 attempts to modify a database’s collation while the database is in a READONLY state, which prevents any schema-level or metadata changes. This commonly happens because the database was explicitly set to READONLY for reporting/archival purposes, or because the underlying storage (such as an Azure file share, replica, or secondary availability group node) enforces read-only access. Collation changes require write access to system catalog views, so the operation is blocked at the engine level before any actual collation logic is executed.

Step-by-Step Resolution Guide

Step 1: Verify the Current Database State

  • Open SQL Server Management Studio (SSMS) or Azure Data Studio and connect to the instance.
  • Run the following query to confirm READONLY status:
  SELECT name, state_desc, is_read_only 
  FROM sys.databases 
  WHERE name = 'YourDatabaseName';
  • Confirm is_read_only returns 1, validating this is the root cause.

Step 2: Check for Active Connections or AG Role Restrictions

  • If the database is part of an Always On Availability Group, confirm whether it is a secondary replica (secondaries are read-only by design and cannot be altered directly).
  • Use sys.dm_hadr_database_replica_states to check is_primary_replica.
  • If this is a standalone instance, ensure no other session has forced READONLY via application intent connection strings (ApplicationIntent=ReadOnly).

Step 3: Set the Database to READ_WRITE Mode

  • Switch the database into single-user mode to avoid conflicts, then change its state:
  ALTER DATABASE YourDatabaseName 
  SET READ_WRITE WITH ROLLBACK IMMEDIATE;
  • This forcibly terminates other connections and flips the database into a writable state.

Step 4: Apply the Collation Change

  • Once the database reports READ_WRITE, run the intended collation command:
  ALTER DATABASE YourDatabaseName 
  COLLATE SQL_Latin1_General_CP1_CI_AS;
  • Confirm no errors are returned and the command completes successfully.

Step 5: Restore Original READONLY Setting (If Required)

  • If the database must remain READONLY for compliance or reporting reasons, revert it back after the collation change:
  ALTER DATABASE YourDatabaseName 
  SET READ_ONLY WITH ROLLBACK IMMEDIATE;
  • Ensure applications depending on read-only access resume normal connectivity.

Alternative or Verification Steps

  • Re-run SELECT DATABASEPROPERTYEX('YourDatabaseName', 'Collation'); to confirm the new collation value has been applied correctly.
  • If the database is part of an Availability Group and cannot be altered directly, perform the collation change on the primary replica only, then allow the change to propagate naturally to secondaries via log synchronization.