Attempting to rename a SQL Server database only to encounter the frustrating "database must be single-user mode to perform this operation" error? This occurs because active connections are preventing exclusive access. Let's explore robust solutions to identify and handle these connections.

The sys.dm_exec_sessions and sys.dm_exec_connections DMVs provide the most comprehensive view:

SELECT 
    s.session_id,
    s.login_name,
    s.host_name,
    s.program_name,
    s.status,
    c.connect_time,
    c.net_transport
FROM 
    sys.dm_exec_sessions s
JOIN 
    sys.dm_exec_connections c ON s.session_id = c.session_id
WHERE 
    DB_NAME(s.database_id) = 'YourDatabaseName'
    OR s.database_id IS NULL;

For a quick overview without filtering:

EXEC sp_who2

Pro tip: For SQL Server 2016+, use this enhanced version:

EXEC sp_who2 @loginame = NULL, @activeonly = 1

When you need to force disconnections, first try setting the database to SINGLE_USER mode:

ALTER DATABASE YourDatabaseName
SET SINGLE_USER WITH ROLLBACK IMMEDIATE

For targeted termination (SQL Server 2012+):

-- Get the session ID first from previous queries
KILL [session_id] WITH STATUSONLY;

For frequent operations, create a stored procedure:

CREATE PROCEDURE dbo.ForceDatabaseRename
    @dbname NVARCHAR(128),
    @newname NVARCHAR(128)
AS
BEGIN
    DECLARE @sql NVARCHAR(MAX)
    
    -- Kill existing connections
    SET @sql = N'
    ALTER DATABASE [' + @dbname + '] 
    SET SINGLE_USER WITH ROLLBACK IMMEDIATE'
    
    EXEC sp_executesql @sql
    
    -- Perform rename
    SET @sql = N'
    ALTER DATABASE [' + @dbname + '] 
    MODIFY NAME = [' + @newname + ']'
    
    EXEC sp_executesql @sql
    
    -- Return to multi-user
    SET @sql = N'
    ALTER DATABASE [' + @newname + '] 
    SET MULTI_USER'
    
    EXEC sp_executesql @sql
END

For enterprise environments, consider:

• SQL Server Profiler trace for connection patterns
• Extended Events session tracking login/logoff
• Third-party monitoring tools like Redgate SQL Monitor

When attempting to perform maintenance operations like database renaming, SQL Server requires an exclusive lock on the database. The error message you're seeing typically occurs when other active connections are preventing this exclusive lock from being obtained. This is a common scenario in production environments where multiple applications or users might be connected to the database.

The most effective way to identify active connections is through SQL Server's dynamic management views (DMVs). Here's a comprehensive query that provides detailed connection information:

SELECT 
    s.session_id,
    s.login_name,
    s.host_name,
    s.program_name,
    s.status,
    db_name(r.database_id) as database_name,
    r.command,
    r.wait_type,
    r.wait_time,
    r.blocking_session_id,
    t.text as sql_text,
    s.login_time
FROM 
    sys.dm_exec_sessions s
LEFT JOIN 
    sys.dm_exec_requests r ON s.session_id = r.session_id
OUTER APPLY 
    sys.dm_exec_sql_text(r.sql_handle) t
WHERE 
    s.is_user_process = 1
    AND r.database_id = DB_ID('YourDatabaseName')
ORDER BY 
    s.login_time DESC;

If you need a quicker check or are working with older SQL Server versions, these alternatives can help:

-- Simple connection count
SELECT 
    DB_NAME(dbid) as DBName, 
    COUNT(dbid) as NumberOfConnections,
    loginame as LoginName
FROM 
    sys.sysprocesses
WHERE 
    dbid > 0
    AND DB_NAME(dbid) = 'YourDatabaseName'
GROUP BY 
    dbid, loginame;

-- Using sp_who2
EXEC sp_who2;

Once you've identified blocking connections, you might need to terminate them. Here's how to safely kill specific sessions:

-- First, check if the connection is running a transaction
SELECT transaction_id FROM sys.dm_tran_session_transactions 
WHERE session_id = [session_id_to_kill];

-- If no active transaction, proceed with kill
KILL [session_id_to_kill];

-- For Azure SQL Database
ALTER DATABASE [YourDB] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
-- Perform your operation here
ALTER DATABASE [YourDB] SET MULTI_USER;

To avoid similar issues during maintenance windows:

1. Schedule operations during low-usage periods
2. Implement application connection timeouts
3. Use connection pooling with proper configuration
4. Consider putting the database in single-user mode before critical operations

For persistent connection issues, check for:

• Application connection leaks (unclosed connections)
• Long-running transactions
• SSMS query windows left open
• Scheduled jobs or maintenance plans running during your operation
• Replication or mirroring configurations