Wednesday, May 7, 2008

Kill all SQL active connections

For the last couple of weeks i'm using SSIS a lot. In some tasks i need to make sure that any client is connected to the database where i need to perform a specific task.

To solve this issue i found this script that basically, kills all the active connections on the database.

DECLARE 
@p_SPID int,
@p_SQL nvarchar(2000),
@dbName nvarchar(100)

SET @dbName = 'MyDBName'

DECLARE #cur_Processes CURSOR FOR

SELECT
p.SPID
FROM
master.dbo.sysprocesses AS p
JOIN master.dbo.sysdatabases AS d ON( d.dbid = p.dbid )
WHERE
d.Name = @dbName AND p.SPID > 50 -- AND spid >= 51 (because spids of 50 or less are reserved for internal use.)
OPEN #cur_Processes
FETCH NEXT FROM #cur_Processes INTO @p_SPID
WHILE @@FETCH_STATUS = 0

BEGIN
SET @p_SQL = 'KILL ' + CONVERT( nvarchar(30), @p_SPID )
PRINT @p_SQL
EXECUTE( @p_SQL )
FETCH NEXT FROM #cur_Processes INTO @p_SPID
END
CLOSE #cur_Processes
DEALLOCATE #cur_Processes

1 comment:

canu said...

thanks for the script mate...