You've probably encountered this classic SQL Server headache:

Msg 3703, Level 16, State 4, Line 1
Cannot detach the database 'DEMO' because it is currently in use.

Even after rebooting the server, the stubborn message persists. Let's dive into why this happens and how to properly force detach a database.

SQL Server maintains internal connections that aren't always visible through standard tools. Common hidden connections include:

• Active transactions that weren't properly closed
• Snapshot isolation sessions
• Database mirroring or replication processes
• SQL Agent jobs accessing the database

When normal detachment fails, put the database in single-user mode first:

ALTER DATABASE DEMO SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO
EXEC sp_detach_db 'DEMO', 'true';
GO

The ROLLBACK IMMEDIATE parameter kills all active connections forcefully.

For a more surgical approach, identify active connections first:

SELECT 
    s.session_id,
    s.login_name,
    s.host_name,
    s.program_name
FROM sys.dm_exec_sessions s
WHERE DB_NAME(s.database_id) = 'DEMO';

Then terminate specific sessions:

KILL 55; -- Replace with actual session_id
GO

As a last resort, set the database offline which effectively detaches it:

ALTER DATABASE DEMO SET OFFLINE WITH ROLLBACK IMMEDIATE;
GO

Remember to bring it back online later:

ALTER DATABASE DEMO SET ONLINE;
GO

Best practices to avoid this situation:

1. Always check for active connections before detaching
2. Consider using sp_who2 for quick connection checks
3. Schedule detachments during maintenance windows
4. Document your detachment procedures

Every SQL Server DBA has encountered this roadblock - you try to detach a database but get blocked by the infamous "Database currently in use" error, even when you're certain no connections exist. Here's what's really happening under the hood:

Msg 3703, Level 16, State 2, Line 1
Cannot detach the database "DEMO" because it is currently in use.

SQL Server marks databases as "in use" in several non-obvious scenarios:

• Active transactions (even from aborted sessions)
• Database snapshots
• Replication components
• Active Service Broker conversations
• Open backup operations

When standard methods fail, force the database into single-user mode:

ALTER DATABASE DEMO SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO
EXEC sp_detach_db 'DEMO', 'true';
GO

The ROLLBACK IMMEDIATE parameter terminates all active connections - use cautiously in production.

For more surgical precision, identify and kill specific SPIDs:

SELECT 
    s.session_id,
    s.login_name,
    s.host_name,
    s.program_name
FROM sys.dm_exec_sessions s
WHERE s.database_id = DB_ID('DEMO');

-- Then terminate problematic SPIDs:
KILL 55; -- Example SPID

If you're in a dev environment, restarting SQL Server will clear all connection states:

-- Command to restart SQL Server service (admin required)
NET STOP MSSQLSERVER
NET START MSSQLSERVER

Implement these proactive measures:

-- Set database to restricted user mode before maintenance
ALTER DATABASE DEMO SET RESTRICTED_USER;

-- Check for active transactions
DBCC OPENTRAN('DEMO');

-- Verify no snapshots exist
SELECT name FROM sys.databases WHERE source_database_id = DB_ID('DEMO');