Kill them all... Your SPIDs of course!

I had a pretty common task to perform the other day.  And that task was to simply restore a database on a development server.  Now, restoring a database in and of itself is a very simple thing to do.  So simple, it's probably one of the very first things you learn to do when learning SQL Server.  However, when you're learning SQL Server on your personal laptop or desktop, you don't have a number of other people connected to the database.  You may have others connected when in a work environment.

When you have other people working on a db, you've got other open connections to the database besides your own.  Typically, a way to close those connections is by using the "KILL" statement.  You can run an sp_who2 statement to find all of the open SPIDs and then run KILL (::SPID::) to close the connection.  Now the problem I ran into using this method, aside from being more time consuming and tedious, was that while I was running the kill statements on the list of SPIDs that I found, other connections were being opened.  So I received another error when attempting to restore the database.  The solution for this is pretty simple.  Just kill all of the connections at once and immediately attempt to restore the database.  So I found a script that did that very thing...

SET NOCOUNT ON
DECLARE @DBName varchar(50)
DECLARE @spidstr varchar(8000)
DECLARE @ConnKilled smallint
SET @ConnKilled=0
SET @spidstr = ''

Set @DBName = 'DATABASE_NAME'
IF db_id(@DBName) < 4
BEGIN
PRINT 'Connections to system databases cannot be killed'
RETURN
END
SELECT @spidstr=coalesce(@spidstr,',' )+'kill '+convert(varchar, spid)+ '; '
FROM master..sysprocesses WHERE dbid=db_id(@DBName)

IF LEN(@spidstr) > 0
BEGIN
EXEC(@spidstr)
SELECT @ConnKilled = COUNT(1)
FROM master..sysprocesses WHERE dbid=db_id(@DBName)
END

I found this script here on stackoverflow.com.

This script will go through all of the SPIDs connected to the db that you enter as the "@DBName" parameter and kill the connection, leaving the db available to perform a restore.