Kill all the Active Connections to Your Database |   | 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. |
