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

by

Troubleshooting Guide: Error 3702 – “Cannot Drop Database Because It Is Currently In Use”

Why This Error Happens

This error occurs because SQL Server 2025 prevents dropping a database while one or more active connections, sessions, or processes still hold a reference to it. Common causes include open query windows in SQL Server Management Studio (SSMS), active application connection pools, lingering replication or mirroring configurations, or background processes like SQL Server Agent jobs still accessing the database. The database engine enforces this lock to prevent data corruption that could result from removing a database mid-transaction.

Step-by-Step Resolution Guide

Step 1: Identify Active Connections to the Database

  • Open a new query window in SSMS (ensure it’s not connected to the target database itself).
  • Run the following command to list all active sessions:
  SELECT session_id, login_name, host_name, program_name, status
  FROM sys.dm_exec_sessions
  WHERE database_id = DB_ID('YourDatabaseName');
  • Note any session_id values returned, as these represent active connections blocking the drop operation.

Step 2: Terminate Active Sessions

  • Kill each blocking session individually using the KILL command:
  KILL <session_id>;
  • Alternatively, force the database into single-user mode to automatically disconnect all other sessions:
  ALTER DATABASE YourDatabaseName SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
  • This rolls back any open transactions and immediately disconnects all non-essential users.

Step 3: Check for Lingering OS-Level Locks (Windows Server 2025 / Windows 11)

  • Open Resource Monitor (resmon.exe) and check the CPU tab under “Associated Handles” for sqlservr.exe holding file locks on the .mdf/.ldf files.
  • Restart the SQL Server Agent service via services.msc if a scheduled job is actively querying the database.
  • Verify no backup software (e.g., Windows Server Backup or third-party agents) is currently reading the database files.

Step 4: Drop the Database

  • Once the database is in single-user mode and confirmed idle, execute:
  DROP DATABASE YourDatabaseName;
  • If the command still fails, restart the SQL Server service via services.msc (service name: MSSQLSERVER or your named instance) and immediately retry the drop command before new connections can establish.

Alternative or Verification Steps

  • Verify the fix: Re-run SELECT name FROM sys.databases WHERE name = 'YourDatabaseName'; — if it returns zero rows, the database has been successfully dropped.
  • Alternative method: If the GUI-based drop in SSMS fails, right-click the database → Tasks → Take Offline, then delete the database using Object Explorer, which often bypasses lingering session locks that command-line methods cannot clear.