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

by

Troubleshooting Guide: SQL Server Error 5022 — “Log file ‘%ls’ for this database is already active”

Why This Error Happens

This error occurs when SQL Server attempts to attach, restore, or bring a database online while one of its transaction log files is already marked as active by another process or another instance. This typically happens due to a duplicate database attach attempt, a stale lock left by an improperly shut down instance, or conflicting file paths where two database configurations point to the same physical log file (.ldf). It can also surface after a failed failover in an Always On Availability Group where the log file was not released cleanly before a secondary attempted to mount it.

Step-by-Step Resolution Guide

Step 1: Verify No Duplicate Instance Is Using the Log File

  • Open SQL Server Configuration Manager and check if multiple instances (e.g., default and named instances) are configured to use the same data/log directory.
  • Run sp_helpdb 'YourDatabaseName' to confirm the exact path of the .ldf file currently registered.
  • Check Windows Task Manager or services.msc for duplicate sqlservr.exe processes that may be holding a handle on the file.

Step 2: Release File Locks Using Handle Tools

  • Download and run Handle.exe (Sysinternals) from an elevated PowerShell prompt:
  • handle.exe -a "YourDatabase_log.ldf"
  • Identify the PID holding the lock and confirm whether it belongs to a legitimate SQL Server process or an orphaned one.
  • If it’s an orphaned process (not the active SQL Server service), terminate it safely:
  • taskkill /PID <ProcessID> /F

Step 3: Take the Database Offline and Re-Attach Cleanly

  • In SSMS, right-click the database, select Tasks > Take Offline.
  • If the database is stuck, force detach via T-SQL:
  • EXEC sp_detach_db 'YourDatabaseName', 'true';
  • Re-attach using CREATE DATABASE ... FOR ATTACH with the correct, unique file path:
  • CREATE DATABASE YourDatabaseName ON (FILENAME = 'D:\SQLData\YourDatabase.mdf'), (FILENAME = 'D:\SQLLogs\YourDatabase_log.ldf') FOR ATTACH;

Step 4: Restart the SQL Server Service to Clear Residual Locks

  • Open Services.msc on Windows Server 2025 / Windows 11.
  • Restart SQL Server (MSSQLSERVER) or the named instance service to flush any residual file handles.
  • Confirm the SQL Server Error Log (via sp_readerrorlog) no longer references Error 5022 on restart.

Step 5: Check for Always On / Log Shipping Conflicts

  • If using Always On Availability Groups, verify the log file isn’t simultaneously mounted on a secondary replica.
  • Run SELECT * FROM sys.dm_hadr_database_replica_states; to confirm replica state consistency before re-attaching on the primary.

Alternative or Verification Steps

  • Run DBCC CHECKDB('YourDatabaseName') WITH NO_INFOMSGS; after re-attaching to confirm the database is consistent and the log file is properly bound.
  • If the error persists, restore the database from a known-good backup to a new location with a distinct log file name to bypass any lingering file-lock conflict.