You are here: Home > Technical Articles > MS SQL Server - Kill All Processes

MS SQL Server - Kill All Processes

Example on how to kill all processes without restarting the MS SQL Server.

Create Proc Sp_KillAllProcessInDB

@DbName VarChar(100)

as
if db_id(@DbName) = Null
begin
Print 'DataBase dose not Exist'
end
else

Begin
Declare @spId Varchar(30)

DECLARE TmpCursor CURSOR FOR
Select 'Kill ' + convert(Varchar, spid) as spId
from master..SysProcesses
where db_Name(dbID) = @DbName
and spId <> @@SpId
and dbID <> 0
OPEN TmpCursor

FETCH NEXT FROM TmpCursor
INTO @spId

WHILE @@FETCH_STATUS = 0

BEGIN

Exec (@spId)

FETCH NEXT FROM TmpCursor
INTO @spId

END


CLOSE TmpCursor
DEALLOCATE TmpCursor

end

--To Execute
Exec Sp_KillAllProcessInDB databasename


Published On: 04 Jul 2009