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

by

Troubleshooting Guide: SQL Server Error 1406 — Unable to Force Service Safely in Database Mirroring

Why This Error Happens

This error occurs when an administrator attempts to force service (manually fail over) to a mirror database, but SQL Server detects that doing so could result in data loss or an inconsistent state between the principal and mirror databases. It typically happens when the mirroring session is in a SYNCHRONIZING state (rather than SYNCHRONIZED) at the time of the failure, meaning the mirror does not have a fully caught-up copy of the transaction log. SQL Server blocks the forced failover as a safety mechanism to prevent committing to a database copy that is behind the principal.

Step-by-Step Resolution Guide

Step 1: Verify the Mirroring State Before Taking Action

  • Open SQL Server Management Studio (SSMS) and connect to the mirror server instance.
  • Run the following query to check the mirroring state:
SELECT DB_NAME(database_id) AS DatabaseName, mirroring_state_desc, mirroring_role_desc
FROM sys.database_mirroring
WHERE mirroring_guid IS NOT NULL;
  • Confirm whether the state is SYNCHRONIZED or SYNCHRONIZING. If it is SYNCHRONIZING, forcing service will cause data loss, which is why SQL Server is blocking the action.

Step 2: Remove Mirroring Safely from the Mirror Database

  • On the mirror server instance, open a new query window in SSMS.
  • Execute the command to break the mirroring partnership:
ALTER DATABASE [YourDatabaseName] SET PARTNER OFF;
  • This detaches the database from the mirroring session without attempting an unsafe automatic failover.

Step 3: Recover the Database to Bring It Online

  • With mirroring removed, the database will be left in a RESTORING state. Run the recovery command to bring it online:
RESTORE DATABASE [YourDatabaseName] WITH RECOVERY;
  • This rolls forward any committed transactions and rolls back uncommitted ones, making the database accessible.
  • If the database was part of an Availability Group migration or legacy mirroring setup in Windows Server 2025, confirm no lingering endpoint configurations exist by checking sys.database_mirroring_endpoints.

Step 4: Validate Application and Service Connectivity

  • Update any connection strings or SQL Server Agent jobs that referenced the old mirroring partner or failover partner connection string.
  • Restart the SQL Server service (via services.msc or net stop MSSQLSERVER / net start MSSQLSERVER) on Windows Server 2025 to clear any cached mirroring session states.
  • Check the SQL Server Error Log (Management > SQL Server Logs in SSMS) for confirmation messages that mirroring has been removed and the database is online.

Alternative or Verification Steps

  • Run SELECT state_desc FROM sys.databases WHERE name = 'YourDatabaseName'; and confirm the result shows ONLINE rather than RESTORING or SUSPECT.
  • If the database still won’t come online after recovery, consider restoring from the most recent full backup plus transaction log backups as an alternative to salvage the mirror copy.