Kill All Process in a Database SQL Server

To totally unlock this section you need to Log-in

Login
At times, there may be a chance, when we, intentionally, want to kill all active processes in a given database o and you cannot drop database because it is currently in use. This is the error message:

Msg 3702, Level 16, State 3, Line 2


Cannot drop database “DataBaseName” because it is currently in use.

Following Script will kill all currently running processes in a given database.

This is generic error is triggered when DROP Database command is executed. Error clearly says that the database is used by some application or user.

To resolve this issue, we need to close all active proccess:

create procedure sp_Kill_All_Processes


@dbname varchar(255)=NULL, @bkdevname varchar(255) =NULL


as


DECLARE @A VARCHAR(255), @x SMALLINT,@dbid SMALLINT, @B VARCHAR(255)


goto x_continue


k_repeat:


waitfor delay ’00:00:01′


x_continue:


--############### Kill all processes in this database ###################


CREATE TABLE #Tmp_spid_killed
( timestamp datetime default getdate(), spid varchar(10), eventtype VARCHAR(150), parameters VARCHAR(50), cmd_txt VARCHAR(5550))


SELECT @dbid = dbid from master..sysdatabases where name = @dbname


DECLARE mycursor


CURSOR FOR


SELECT spid FROM master..sysprocesses where dbid = @dbid – dbid


set nocount on


OPEN mycursor


FETCH NEXT FROM mycursor INTO @x
WHILE (@@fetch_status <> -1)


BEGIN


IF (@@fetch_status <> -2)


BEGIN


select @B=‘dbcc inputbuffer(‘ + convert(varchar(10),@x) +‘)’


INSERT INTO #Tmp_spid_killed (eventtype ,parameters ,cmd_txt)


exec(@B)


update #Tmp_spid_killed set spid = @x where spid is null


select @B = cmd_txt from #Tmp_spid_killed where spid = @x


print ‘Killing SPID ‘ + convert(varchar(10),@x) + ‘ On ‘ + convert(varchar(25),getdate()) +‘ ‘ +@B


select @A=‘kill ‘+convert(varchar(10),@x)


exec(@A)


END


FETCH NEXT FROM mycursor INTO @x


END


DEALLOCATE mycursor


select spid, timestamp as ‘Date and Time’ , left(cmd_txt,255) as ‘SQL Statement’ from #Tmp_spid_killed


drop table #Tmp_spid_killed
set nocount off


—-Execution of the the procedure —-


exec sp_Kill_All_Processes ‘<database Name>’ --Database Name for which you wanted to kill all processes

After Executing, we will obtain the report (results), from the above query, which will tell us what SPIDs we have killed, with date and time, and the particular SQL statement text that the singular SPID was executing, when it has been killed by us.

[tweet]

After execution, you will also have a report, what all process you have killed, this information, might be interested by Application Manager / Owner.