Kill all the SQL connections for a DB or app

One of customers needed to be able to clear all of the connections to a database before running some maintenance tasks on the database.  So the question came my way and after searching the Internets, I ended up with the following T-SQL code

DECLARE @spid INT    
DECLARE @getspid CURSOR    
declare @KillCmd nvarchar(128)

-- create a table variable to hold the results of the call to sp_who
declare @k TABLE (spid INT, 
  ecid INT, 
  STATUS VARCHAR(150), 
  loginame VARCHAR(150), 
  hostname VARCHAR(150), 
  blk INT, 
  dbname VARCHAR(150), 
  cmd VARCHAR(150), 
  RequestID int)

-- fill the table variable
INSERT INTO @k EXEC sp_who

-- Create a cursor to use to walk through the table variable
-- that matches the database we want to filter on
SET @getspid = CURSOR FOR 
	SELECT spid 
	FROM @k 
	where dbname = 'YourDatabaseNameHere'

OPEN @getspid    

FETCH NEXT FROM @getspid INTO @spid    

-- For each row in the table, create a kill command
-- kill does not work with variables, we need to
-- execute it with sp_executeSQL
WHILE @@FETCH_STATUS = 0 
BEGIN
  SET @KillCmd = 'KILL ' + CAST(@SPId as nvarchar(10))
  print @KillCmd
  EXEC sp_executeSQL @KillCmd
  FETCH NEXT FROM @getspid INTO @spid
END

-- cleanup
CLOSE @getspid

DEALLOCATE @getspid

The way it works is that we call the sp_who system stored procedure.  This procedure returns a set that lists current users, sessions, and processs.  From that set, we can get a list of all of the connections for a database. 

Since we need to work with the rows of that set, we create a table variable named @k and populate it with the result set returned from sp_who.  You need to match the number of fields and the field types (or pick field types that SQL can convert automatically).

Next, we create a cursor and iterate through the rows that match the database name that we want to kill the connections on.  We are using the kill command to kill the connection.  You basically call kill with the session id to kill, and that session is terminated. In this example, we are matching on the dbname column.  You could easily match by loginame or hostname, depending on your needs.

The kill command has a slight little kink, where you have to pass a literal value to the kill command.  It doesn’t work with variables.  If you try it, you’ll get an “incorrect syntax near…” error message.

To get around this, we fill a string variable with the kill command and the session id.  We then call sp_executesql to execute our dynamically generated SQL statement. It looks somewhat less than elegant, but it works just fine.

This was written as inline SQL so the customer could add it to his maintenance script.  You could easily make sproc out of it and toss it into the master database.