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

by

Why This Error Happens

Error 12706 occurs when SQL Server’s metadata incorrectly references a file ID that conflicts with another file already registered to the same database, typically caused by a corrupted or improperly restored transaction log, a botched file-add operation during a crash, or manual manipulation of system catalog views like sys.master_files. This can also surface after a failed ALTER DATABASE ADD FILE command that partially committed, or when a database is attached from a backup where file ID sequences were duplicated due to replication or mirroring misconfigurations. In rare cases, antivirus or backup software locking the .mdf/.ldf files during a file-add operation on Windows Server 2025 triggers this inconsistency.

Step-by-Step Resolution Guide

Step 1: Verify the Current File ID Mapping

  • Open SQL Server Management Studio (SSMS) and connect to the affected instance.
  • Run the following query to inspect file IDs for the problem database:
  SELECT file_id, name, physical_name, state_desc FROM sys.master_files WHERE database_id = DB_ID('YourDatabaseName');
  • Look for duplicate or missing file_id entries, especially around the ID mentioned in the error (%d).

Step 2: Take the Database Offline and Check File Locks

  • Set the database to single-user mode to prevent concurrent access:
  ALTER DATABASE YourDatabaseName SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
  • On Windows Server 2025/Windows 11, open Resource Monitor (resmon.exe) and search the CPU tab for handles locking the .mdf or .ldf files under C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\DATA\.
  • Terminate any third-party backup agent or antivirus process holding a lock on these files, then restart the SQL Server service via services.msc.

Step 3: Rebuild or Correct the File Metadata

  • If a duplicate file ID is confirmed, remove the conflicting file reference using:
  ALTER DATABASE YourDatabaseName REMOVE FILE DuplicateLogicalFileName;
  • Re-add the file with a clean, unused file ID path:
  ALTER DATABASE YourDatabaseName ADD FILE (NAME = 'NewFileName', FILENAME = 'D:\SQLData\NewFile.ndf');
  • If the database won’t come online at all, restore the last known good full backup and replay transaction logs instead of repairing in place.

Step 4: Return Database to Multi-User Mode

  • Once the file structure is corrected, switch the database back:
  ALTER DATABASE YourDatabaseName SET MULTI_USER;
  • Confirm no error messages appear in SQL Server Error Log (ERRORLOG file located in the MSSQL\Log directory).

Alternative or Verification Steps

  • Run DBCC CHECKDB('YourDatabaseName') WITH NO_INFOMSGS, ALL_ERRORMSGS; to confirm there is no remaining corruption after the fix.
  • If the error persists after metadata correction, perform a full restore from backup onto a new database name and redirect application connections, as this guarantees a clean file ID sequence.