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

by

Why This Error Happens

Error 5131 occurs when SQL Server attempts to rebuild a transaction log—typically during an ALTER DATABASE... REBUILD LOG operation or an emergency-mode repair via DBCC CHECKDB with REPAIR_ALLOW_DATA_LOSS—but detects that the database metadata references multiple log files. The log rebuild process is hardcoded to only work when exactly one .ldf file is registered to the database, as the engine cannot safely reconstruct a Virtual Log File (VLF) chain across multiple physical log files. This usually happens on databases where a DBA previously added a second log file for performance or space reasons, and the primary log subsequently became corrupted or inaccessible.

Step-by-Step Resolution Guide

Step 1: Identify All Log Files Attached to the Database

  • Open SQL Server Management Studio (SSMS) or connect via sqlcmd on Windows Server 2025.
  • Run the following query to list every file associated with the affected database:
  USE [YourDatabaseName];
  GO
  SELECT name, physical_name, type_desc 
  FROM sys.database_files 
  WHERE type_desc = 'LOG';
  • Confirm whether more than one row with type_desc = 'LOG' is returned.

Step 2: Remove the Extra Log File(s) from Database Metadata

  • Set the database to EMERGENCY mode or SINGLE_USER mode first if it is not already accessible:
  ALTER DATABASE [YourDatabaseName] SET EMERGENCY;
  ALTER DATABASE [YourDatabaseName] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
  • Attempt to remove the secondary log file(s) using the logical file name identified in Step 1:
  ALTER DATABASE [YourDatabaseName] REMOVE FILE [SecondaryLogFileLogicalName];
  • If the command fails because the file is required for recovery, proceed to the detach/reattach method in Step 3.

Step 3: Detach and Reattach Using Only the Primary Data File

  • Stop the SQL Server service via services.msc or net stop MSSQLSERVER on Windows Server 2025.
  • Rename or move the secondary .ldf file out of the C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\DATA\ directory (adjust path/version as needed).
  • Reattach the database using CREATE DATABASE ... FOR ATTACH_REBUILD_LOG, referencing only the primary .mdf file:
  CREATE DATABASE [YourDatabaseName] 
  ON (FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\DATA\YourDatabaseName.mdf')
  FOR ATTACH_REBUILD_LOG;
  • This forces SQL Server to generate a brand-new single log file since only one data file is referenced.

Step 4: Run DBCC CHECKDB to Validate Integrity

  • Once the log rebuild succeeds, immediately run:
  DBCC CHECKDB ('YourDatabaseName') WITH NO_INFOMSGS, ALL_ERRORMSGS;
  • Review the output for any lingering consistency errors introduced by the forced rebuild.

Alternative or Verification Steps

  • Verify the fix by querying sys.database_files again to confirm exactly one log file now exists and that the database state in sys.databases shows ONLINE.
  • If the ATTACH_REBUILD_LOG method fails due to corruption in the primary data file itself, restore from your most recent full backup plus transaction log backups instead of attempting further repairs, as rebuilding the log discards all unrecoverable transactions.