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

by

Troubleshooting Guide: SQL Server Error 5006

Why This Error Happens

Error 5006 occurs when SQL Server 2025 attempts to acquire an exclusive lock on a database file or resource (such as during a RESTORE, ALTER DATABASE, or file shrink operation) but another process is actively holding a handle to that resource. This is frequently caused by lingering connections, a backup agent, or an antivirus/EDR solution on Windows Server 2025 scanning the .mdf/.ldf files in real-time. It can also happen if a previous SQL Server process did not release the file handle cleanly after a crash or improper shutdown.

Step-by-Step Resolution Guide

Step 1: Identify Active Connections and Locks

  • Open SQL Server Management Studio (SSMS) and run sp_who2 or query sys.dm_exec_sessions to find active sessions connected to the target database.
  • Check for open transactions using DBCC OPENTRAN('YourDatabaseName').
  • If stray sessions are found, terminate them using KILL [session_id].

Step 2: Set Database to Single-User Mode

  • Force the database into single-user mode to strip away any lingering background connections (like reporting services or linked server pings):
  • ALTER DATABASE YourDatabaseName SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
  • Attempt your original operation (e.g., Restore or File Shrink) immediately after this command succeeds.

Step 3: Check for File-Level Locks (OS Level)

  • On Windows Server 2025, use Resource Monitor (resmon.exe) or Process Explorer to search for the specific .mdf or .ldf file path mentioned in the error message.
  • Identify if a non-SQL process (like Windows Defender, a backup agent, or a search indexer) is holding the handle.
  • Add the SQL Server data directory (typically C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\DATA) to your antivirus exclusion list.

Step 4: Restart the SQL Server Service

  • If the lock persists despite no visible active sessions, the lock might be a “ghost” handle from the SQL Server Engine itself.
  • Restart the specific instance via Windows Services (services.msc) by finding SQL Server (MSSQLSERVER).
  • Alternatively, use PowerShell: Restart-Service -Name 'MSSQLSERVER' -Force.

Step 5: Revert Database to Multi-User Mode

  • Once the operation (restore/alter) is complete, ensure you return the database to its normal operating state to allow user traffic back in.
  • Run: ALTER DATABASE YourDatabaseName SET MULTI_USER;

Alternative or Verification Steps

  • Verification: Re-run the original command that triggered Error 5006 (e.g., the RESTORE DATABASE script). If it completes without hanging or throwing the exclusive lock error, the fix was successful.
  • Alternative: If the error persists only during automated backups, check the SQL Server Agent Job History to ensure no overlapping jobs are scheduled to run against the same database simultaneously.