Windows/MSSQL

Transact SQL

snippets...

Kill all Processes

print and kill all connections to a particular DB

USE master
GO

SET NOCOUNT ON
DECLARE @DBName varchar(50)
DECLARE @spidstr varchar(8000)
DECLARE @ConnKilled smallint
SET @ConnKilled=0
SET @spidstr = ''

Set @DBName = 'PROD'
IF db_id(@DBName) < 4
BEGIN
PRINT 'Connections to system databases cannot be killed'
RETURN
END
SELECT @spidstr=coalesce(@spidstr,',' )+'kill '+convert(varchar, spid)+ '; '
FROM master..sysprocesses WHERE dbid=db_id(@DBName) and spid <> @@SPID

IF LEN(@spidstr) > 0
BEGIN
PRINT ''
PRINT @spidstr
PRINT ''
EXEC(@spidstr)
SELECT @ConnKilled = COUNT(1)
FROM master..sysprocesses WHERE dbid=db_id(@DBName)
PRINT 'Killed: '+convert(varchar, @ConnKilled)+' Connections'
END