/*Este script mata as todas as sessoões conectadas em uma determinada base de dados

Basta informar a base de dados na variavel @dbname*/

USE Master

GO

SET NOCOUNT ON

-- 1 - Variable Declaration

DECLARE @DBNAME nvarchar(32)

DECLARE @DBID int

DECLARE @CMD1 varchar(8000)

DECLARE @spidNumber int

DECLARE @SpidListLoop int

DECLARE @SpidListTable table

(dbName nvarchar (32),UIDSpidList int IDENTITY (1,1),

SpidNumber int)

Set @dbname = 'Minha base'

-- 2 - Populate @SpidListTable with the spid information

INSERT INTO @SpidListTable (dbname,SpidNumber)

select distinct a.name,b.spid

from sysdatabases a inner join sysprocesses b on (a.dbid = b.dbid)

WHERE b.DBID NOT IN (1,2,3,4) -- Master, Tempdb, Model, MSDB

AND b.spid > 50

AND b.spid != @@spid

AND a.name = @dbname

ORDER BY spid DESC

-- 3a - Determine the highest UIDSpidList to loop through the records

SELECT @SpidListLoop = MAX(UIDSpidList) FROM @SpidListTable

-- 3b - While condition for looping through the spid records

WHILE @SpidListLoop > 0

BEGIN

-- 3c - Capture spids location

SELECT @spidNumber = spidnumber

FROM @spidListTable

WHERE UIDspidList = @SpidListLoop

-- 3d - String together the KILL statement

SELECT @CMD1 = 'KILL ' + CAST(@spidNumber AS varchar(5))

-- 3e - Execute the final string to KILL the spids

--SELECT @CMD1

EXEC (@CMD1)

-- 3f - Descend through the spid list

SELECT @SpidListLoop = @SpidListLoop - 1

END

SET NOCOUNT OFF

GO

/*Eduardo Pin

Senior database Administrator MCSA MCTS SQL 2005

Eduardo.pin@diles.com.br

*/