SQL Server – Kill All Process in a Database

Leave a comment (0) Go to comments

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.

SQL Server   Kill All Process in a Database SQL Server   Kill All Process in a Database kill all processes Kill All Process in a Database Kill All Process Error : 3702 Cannot drop database because it is currently in use Error : 3702 Database Cannot drop database  because it is currently in use Cannot drop database
Drop Database Failed

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.

SQL Server   Kill All Process in a Database SQL Server   Kill All Process in a Database kill all processes Kill All Process in a Database Kill All Process Error : 3702 Cannot drop database because it is currently in use Error : 3702 Database Cannot drop database  because it is currently in use Cannot drop database

sql server kill all process in a database

EOF - SQL Server – Kill All Process in a Database, SQL Server 2012 , 2008 R2 , 2008 , 2005 , 2000

Leave a Reply

Your email address will not be published. Required fields are marked *


*


You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Notify me of followup comments via e-mail. You can also subscribe without commenting.