Know Dot Net Article

Kill all the Active Connections to Your Database

by William Ryan

If you ever need to kill all of your open database connections, here's a little script that can do it.  (Sure, you can use the Detach option from EM and then close all the connections, but if you need to do it programatically...

CREATE PROCEDURE usp_killDataBaseConnections @DatabaseName varchar(50), @WithMessage bit=1
AS
SET NOCOUNT ON
DECLARE @spidstr varchar(8000)
DECLARE @ConnectionKilled smallint
SET @ConnectionKilled=0
SET @spidstr = ''

IF db_id(@DatabaseName) < 4 <BR> BEGIN
    PRINT 'No can do...'
    RETURN
END

SELECT @spidstr=COALESCE(@spidstr,',' )+'kill '+CONVERT(VARCHAR, spid)+ '; '
FROM master..sysprocesses WHERE dbid=db_id(@DatabaseName)

IF LEN(@spidstr) > 0
BEGIN
    EXEC(@spidstr)

    SELECT @ConnectionKilled = COUNT(1)
    FROM master..sysprocesses WHERE dbid=db_id(@DatabaseName)

END

IF @WithMessage =1
    PRINT  CONVERT(VARCHAR(10), @ConnectionKilled) + ' Connection(s) killed for DB '  + @DatabaseName

GO

Basically, we just have another loop and run through querying sysprocesses and calling the Kill function.  We use a variable @spidstr to dynamically construct a SQL Statement than firing it within a loop.  It takes an optional parameter @WithMessage to show each connection that's been killed systematically.