T-SQL Stored Procedure to Kill all Connections to a DB

I’ve encountered the below error message many times when I was trying to do an adhoc restore operation on a DB server:

Msg 3101, Level 16, State 1, Line 2
Exclusive access could not be obtained because the database is in use.
Msg 3013, Level 16, State 1, Line 2
RESTORE DATABASE is terminating abnormally.

And to quickly gain exclusive access to the target DB, I wrote down this proc to kick out all connections that are bound to the target DB and don’t hate me because I used cursors 🙂

[sourcecode language=”sql”]
/*
Stored procedure to kill processes that are connected to a specific DB

SQL Version: 2005+

Last modified on 2014-03-17 by Nick Xu

–Usage
EXEC sp__KillDBConnections ‘DBADB’

*/

–For generic setting
USE master
GO

IF EXISTS (SELECT * FROM sysobjects WHERE name = N’sp__KillDBConnections’ AND type = N’P’)
BEGIN
DROP PROC sp__KillDBConnections
END
GO

CREATE PROC sp__KillDBConnections
@dbname AS VARCHAR(100) = NULL
AS
BEGIN
DECLARE @DatabaseName VARCHAR(50);
DECLARE @Spid VARCHAR(20);
DECLARE @Command VARCHAR(50);

SET @DatabaseName = @dbname;

IF @DatabaseName IS NULL
BEGIN
PRINT ‘Please specify to which DB you want to kill all connections…’
RETURN
END

–Select all SPIDs except the SPID for this connection and kill them
DECLARE SpidCursor CURSOR FOR
SELECT spid FROM master.dbo.sysprocesses
WHERE dbid = DB_ID(@DatabaseName) AND spid != @@spid

open SpidCursor
FETCH NEXT FROM SpidCursor INTO @Spid
while @@fetch_status = 0
BEGIN
SET @Command = ‘kill ‘ + RTRIM(@Spid) + ‘;’;
EXEC(@Command);
FETCH NEXT FROM SpidCursor INTO @Spid
END

CLOSE SpidCursor
DEALLOCATE SpidCursor

END
GO
[/sourcecode]