Killing the DB :P
-
Some one please tell me the T SQl syntax of
1-Disconnecting the Users of a DB
2-Killing the sessions/Processes of a DB
By da way i required this in order to drop the DB using the DROP command, and before that i want to make sure that no User is connected to the DB also there is no Session/Process of the DB
-
You could try:
ALTER DATABASE myDb SET RESTRICTED_USER WITH NO_WAIT
/Kenneth
-
I found a way....its the KILL statement.....KILL statement requires the SPID of the process to be killed, i wrote the following code to find the SPID's of my Database, but now the porblem is KILL is not working with the SPID in variable....i.e KILL works fine if we write
KILL 23 (here 23 is a SPID)............ but it is not working if we write
Declare @id int
SET @id = 23
KILL @id
consider the following code...i m finding the SPID's of all process of my Database, then i use cursor to KILL those processes one by one.....but KILL is not working with SPID stored in Variable.
begin
create table #temptable (id int,ecid int, status varchar(200), login varchar(200), host varchar(200),blk int, db varchar(200), cmd varchar(500))
insert into #temptable exec SP_who
DECLARE abc CURSOR for select id from #temptable where db = 'ADL'
declare @id int
open abc
fetch next from abc into @id
while @@fetch_status = 0
begin
KILL @id
fetch next from abc into @id
end
close abc
deallocate abc
end
-
You have to use EXEC('Kill ' + @spid) since 'KILL' won't accept a variable as parameter
Though note that if you have quick users, there's nothing preventing them to reconnect immediately after you've killed them...
/Kenneth
-
Something like this will work:
DECLARE @spid_table TABLE (spid smallint)
DECLARE @spid smallint,
@kill nvarchar(11)
INSERT INTO @spid_table
SELECT spid FROM master..sysprocesses
WHERE dbid=DB_ID(db_name()) AND spid <> @@spid
WHILE (SELECT count(spid) FROM @spid_table) > 0
BEGIN
SET @spid = (SELECT TOP 1 spid FROM @spid_table)
SET @kill = 'KILL '+cast(@spid as varchar)
EXEC sp_executesql @stmt=@kill
DELETE FROM @spid_table WHERE spid = @spid
END
Note that it excludes @@spid, as you can't kill your own connection.
-
You should also avoid the systemspids 1-7.
/Kenneth
-
thanks every one who replied.....
My solution works fine after the change suggested by Kenneth. i.e EXEC('Kill ' + @spid)
Now about the system spids.....they will not be returned by the cursor...coz i m selecting the spids of only my DB.......
Now about killing the own connection.......this script will be executed from an application installer.....so it has to kill all the connections of the DB, no matter who is connected with the DB.
So i m getting the desired results...
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply