SQL Server: 'Cannot drop database because it is currently in use' (error 3702)

Cannot drop database "fix7_lab" because it is currently in use. (error 3702) means at least one other session is still connected to that database. SQL Server will not drop it under someone's feet: find the session and close it, or let the ALTER DATABASE statement below disconnect everyone first.

The one-batch fix is ALTER DATABASE fix7_lab SET SINGLE_USER WITH ROLLBACK IMMEDIATE; DROP DATABASE fix7_lab;, run from master. When the session holding the database belongs to your own program, it is often a pooled connection, and SqlConnection.ClearAllPools() releases it.

The error

PS> .\FixLab.exe 'Server=(localdb)\MSSQLLocalDB;Database=master;Integrated Security=true' 'DROP DATABASE fix7_lab'
ex.Number = 3702, ex.Class = 16, ex.State = 4
Microsoft.Data.SqlClient.SqlException: Cannot drop database "fix7_lab" because it is currently in use.

sqlcmd prints the same text under Msg 3702, Level 16, State 4.

Why it happens

A second program had opened a connection to fix7_lab (the test's database) with Application Name=ReportWorker and was sleeping on it, the way an idle web app, an open query window or a report tool does. Any session whose current database is the one you drop blocks the drop, even an idle one. sys.dm_exec_sessions shows who it is:

PS> .\FixLab.exe 'Server=(localdb)\MSSQLLocalDB;Database=master;Integrated Security=true' "SELECT session_id, login_name, program_name, status FROM sys.dm_exec_sessions WHERE database_id = DB_ID('fix7_lab')"
71 | MACHINE\user | ReportWorker | sleeping
OK (-1 row(s) affected) in 0.2 s

The surprising case is your own code. Connection pooling keeps the physical connection open after Dispose, so a test that uses the database and then drops it from master in the same process blocks itself:

using (var lab = new SqlConnection(labCs))
{
    lab.Open();
    using var count = new SqlCommand("SELECT COUNT(*) FROM sys.tables", lab);
    Console.WriteLine($"tables in {lab.Database}: {count.ExecuteScalar()}");
} // Dispose returns the connection to the pool; the session stays open on the server.

if (clearPools)
    SqlConnection.ClearAllPools();

return RunSql(masterCs, "DROP DATABASE fix7_lab");

Without clearPools that drop failed with the same 3702; with it, the drop succeeded.

The fix

PS> .\FixLab.exe 'Server=(localdb)\MSSQLLocalDB;Database=master;Integrated Security=true' 'ALTER DATABASE fix7_lab SET SINGLE_USER WITH ROLLBACK IMMEDIATE; DROP DATABASE fix7_lab; SELECT COUNT(*) FROM sys.databases WHERE name = ''fix7_lab'''
info: Nonqualified transactions are being rolled back. Estimated rollback completion: 0%.
info: Nonqualified transactions are being rolled back. Estimated rollback completion: 100%.
0
OK (-1 row(s) affected) in 3.2 s

SINGLE_USER WITH ROLLBACK IMMEDIATE disconnected the ReportWorker session, rolling back anything it had open, and the drop went through while that program was still running. The count of 0 confirms the database is gone. Use it on development and test databases; on a shared server, find out whose session it is first, because their work is rolled back without warning.

When sys.dm_exec_sessions shows a single session you know, ending just that one is enough. In a second run the holding session was 51:

PS> .\FixLab.exe 'Server=(localdb)\MSSQLLocalDB;Database=master;Integrated Security=true' 'KILL 51; DROP DATABASE fix7_lab; SELECT COUNT(*) FROM sys.databases WHERE name = ''fix7_lab'''
0
OK (-1 row(s) affected) in 0.2 s

How it was reproduced

A console app from dotnet new console -n FixLab -f net10.0 (.NET SDK 10.0.403) with Microsoft.Data.SqlClient 7.1.1, against SQL Server 2022 LocalDB 16.0.1210.3 and sqlcmd 16. A second copy of the same app, started in the background with a --hold option, opened a connection to the database and slept for 60 seconds.

Frequently asked

How do I see who is using a SQL Server database before dropping it?

Query sys.dm_exec_sessions WHERE database_id = DB_ID('name') from master. It lists the session id, login, program name and status of every session whose current database is that one.

How do I force drop a database in SQL Server?

Run ALTER DATABASE name SET SINGLE_USER WITH ROLLBACK IMMEDIATE followed by DROP DATABASE name in one batch from master. Open transactions in other sessions are rolled back and those sessions are disconnected.

Why does DROP DATABASE fail after my code closed its connection?

Connection pooling keeps the physical connection open after Close or Dispose. Call SqlConnection.ClearAllPools() before the drop, or use SET SINGLE_USER WITH ROLLBACK IMMEDIATE.

PostgreSQL refuses the same drop as 55006, being accessed by other users. More decoded errors in the Fixes category.