How to close existing connections to a DB

Dealing with connections piece by piece

Sniping a single session

If you're looking to eliminate specific connections without impacting others, use the KILL command, a tool no 'killer app' should be without:

-- Let's find the session we wanna 'kill'. Rest in peace, connection.
SELECT session_id FROM sys.dm_exec_sessions WHERE database_id = DB_ID('YourDatabaseName');
-- Time to break some hearts. Say your goodbyes.
KILL <session_id>;


Cleaning house with a cursor

For those who love some automation, you can script a loop with a cursor. It spins through active connections and cuts them off, like a DJ turning off the music at a party:

DECLARE @session_id INT;
DECLARE session_cursor CURSOR FOR
SELECT session_id FROM sys.dm_exec_sessions WHERE database_id = DB_ID('YourDatabaseName');
OPEN session_cursor;
FETCH NEXT FROM session_cursor INTO @session_id;
WHILE @@FETCH_STATUS = 0
BEGIN
    EXEC('KILL ' + @session_id); -- 'Another One Bites the Dust'
    FETCH NEXT FROM session_cursor INTO @session_id;
END;
CLOSE session_cursor;
DEALLOCATE session_cursor;


Understanding your connections better

Get comprehensive connection details through a simple query! It's like Facebook stalking, but for your database using sys.sysprocesses, sys.sysdatabases, and sys.sysusers.

A graphic way out

If you're allergic to command lines, SQL Server Management Studio offers a graphical workaround. Click on "close existing connections" when restoring a database. It does the same job, but with mouse clicks instead of keystrokes!

Proceed with caution

Pondering application impacts

While forcibly closing connections, think of possible fallouts on applications and users. You wouldn't want your database going full 'Hulk' on them. Plan during downtime periods to ensure minimal disruption, like the quiet guy at the party.

Keep your scripts handy

Remember the scripts or stored procedures you were tinkering around with? Refine and keep them handy for future use. These can be your Swiss Army knife in maintaining connections in the future.

Handling connections during restoration

Restorations might need special handling of connections. While SINGLE_USER mode guarantees no room for interferences, you'd also want to practice switching back instantly, so you're not left high and dry.

Careful with detaching

Detaching a database is a handy way to say goodbye to all connections. But be careful, as it takes the database off the server instance. So make sure you remember to put it back where it belongs once you're done fixing things up.