SQL Server Training | Tutorials | How to's

At times, there may be a chance, when we intentially, wanted to kill all active proccesses in a given database or

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 databse.

This generic error when DROP Database is command is executed. Error Clearly says that this 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>, Database Name for which you wanted to kill all processes

OUTPUT

After Executing, we will have the report, which will tell us

• SPID’s whioh we killed
• Date and Time, when that particular SPID killed
• SQL statement text of SPID, which we killed.

spid	Date and Time	SQL Statement
53	2010-06-13 19:02:44.650	select * from sysobjects
54	2010-06-13 19:02:44.653	select * from sysobjects
58	2010-06-13 19:02:44.653	select * from sysobjects
59	2010-06-13 19:02:44.657	select * from sysobjects
61	2010-06-13 19:02:44.657	select * from sysobjects
62	2010-06-13 19:02:44.660	select * from sysobjects
63	2010-06-13 19:02:44.660	select * from sysobjects

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