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

by

Why This Error Happens

Error 1807 occurs when SQL Server cannot acquire an exclusive lock on a database because another session, process, or replication/mirroring thread is actively holding a connection to it. This commonly happens when a database is still marked as “in use” by lingering connections, an open transaction, or a backup/restore job that hasn’t released its handle. It can also occur if SQL Server Management Studio (SSMS) or another application maintains a hidden connection pool to the database in the background.

Step-by-Step Resolution Guide

Step 1: Identify Active Connections Blocking the Lock

  • Open a new query window in SSMS connected to the master database.
  • Run the following command to check active sessions on the target database:
  SELECT session_id, status, login_name, host_name, program_name 
  FROM sys.dm_exec_sessions 
  WHERE database_id = DB_ID('YourDatabaseName');
  • Note any session_id values other than your own; these are holding the lock.

Step 2: Terminate Blocking Sessions

  • Kill each blocking session identified in Step 1 using:
  KILL <session_id>;
  • If the sessions belong to an application connection pool, restart the relevant application service (e.g., IIS, a Windows Service, or a reporting tool) to release the pooled connections.
  • On Windows Server 2025, check Task Manager > Services tab to confirm no orphaned service processes (like SSRS or SSIS) are still connected.

Step 3: Force Single-User Mode (If Sessions Persist)

  • If connections keep re-establishing, set the database to single-user mode to forcibly disconnect everyone:
  ALTER DATABASE YourDatabaseName 
  SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
  • Perform your required operation (detach, restore, or shrink).
  • Revert back to multi-user mode immediately after:
  ALTER DATABASE YourDatabaseName 
  SET MULTI_USER;

Step 4: Check for Hung SQL Server Agent Jobs or Replication

  • Open SQL Server Agent > Jobs and verify no backup, maintenance, or replication job is currently running against the database.
  • Navigate to Replication > Local Publications in SSMS Object Explorer and ensure no active subscription is holding a schema lock.
  • Stop the SQL Server Agent service temporarily via services.msc on Windows Server 2025 if a scheduled job is the root cause.

Alternative or Verification Steps

  • Verify the fix by re-running the original command (e.g., ALTER DATABASE, DROP DATABASE, or RESTORE); it should complete without the 1807 error.
  • If the issue persists, restart the SQL Server (MSSQLSERVER) service via services.msc or net stop MSSQLSERVER && net start MSSQLSERVER to clear all stale session locks, then retry the operation.