SQL Connection Kill Switch

Aaaarrrrg……

OK, so a few SSMS functions, such as Detach Database, have a checkbox letting you kill all current connections before performing the requested operation.  But some, say Restore, don’t (could someone give me a good reason why?), and end up failing if someone else (or yourself in another query window) has an open connection.  Your solution is to open the Activity Monitor, sort by database and kill each of the connections and hope that no new ones are opened in the mean time.  And what if you want to do this all in a script anyway?

To solve the above problems you can run the following script:

USE master

DECLARE @SQLStatement VARCHAR(MAX)
SET @SQLStatement = ''

SELECT
        @SQLStatement = @SQLStatement
            + 'KILL '
            + convert(VARCHAR(10), spid)
            + CHAR(13) + CHAR(10)
    FROM master.dbo.sysprocesses
    WHERE DB_NAME(dbid) = 'MyDatabase'
        AND dbid <> 0
        AND spid <> @@SPID

EXEC(@SQLStatement)

GO

So what exactly is this doing?  The SELECT statement is building a collection of KILL n statements where n is a process ID of a process connected to the specified database (in this case “MyDatabase”).  The CHAR(13) + CHAR(10) are really only there for aesthetics and debugging purposes.  Note that the WHERE clause explicitly excludes the connection of the current process.

Once this collection of KILL statements is built it executes them.  By using @SQLStatement = @SQLStatement + {next KILL statement}, it’s continually appending the next KILL statement to itself and the resulting value in the @SQLStatement variable is the entire list of KILL statements.

Giving credit where credit is due, I would like to note that I copied and tweaked the above statement from the Aaquagua blog.  In that blog, it is noted that the following statements will have the same effect:

ALTER DATABASE MyDatabase
    SET SINGLE_USER WITH ROLLBACK IMMEDIATE

ALTER DATABASE MyDatabase
    SET MULTI_USER WITH ROLLBACK IMMEDIATE

What we’re doing here is setting the database to single user mode, which immediately kills any current connections, and then setting back to multi-user mode.  Although this will in fact have the same effect, there’s something hackish about it that makes me a tad uncomfortable.

But that’s just my neuroses speaking.  You can pick whichever one works for you.

UPDATE:

OK.  So after writing this, I was thinking about the method of setting the database to single user mode first.  My issue with using it to kill connections is that your using a statement that does one thing, set a database to single user mode, to perform something else, kill all the connections.  Yes it works, but if someone were to come along and review your script, they may wonder what you’re doing.

Anyway, upon further pondering, I decided that setting the database to single user mode before performing an action, such as a restore or major update, is probably a good idea anyway by preventing any other connections from interfering with the action.  The idea would be to wrap your action inside the two set statements.