SQL SERVER 2K Delete current in use users or Session 4

[sRockyHill]

Hi,

I want to delete a database everynigth. Sometime it works when no users are in use. If database in use, I can't delete this database. Can you please give me a code - stored procedure that will terminate or Kill this active or sleep user from this database.

Thank you

 

[DBAWinnipeg]

CREATE PROC Kill_Connections (@dbName varchar(128))
as
	DECLARE @ProcessId varchar(4)
	DECLARE CurrentProcesses SCROLL CURSOR FOR
	select spid from sysprocesses where dbid = (select dbid from sysdatabases where name = @dbName ) order by spid 
	FOR READ ONLY
OPEN CurrentProcesses
FETCH NEXT FROM CurrentProcesses INTO @ProcessId
WHILE @@FETCH_STATUS <> -1
BEGIN
	--print 'Kill ' + @processid
	Exec ('KILL ' +  @ProcessId)
	--Kill @ProcessId
	FETCH NEXT FROM CurrentProcesses INTO @ProcessId
END
CLOSE CurrentProcesses
DeAllocate CurrentProcesses

Thanks in advance!!!

Colin in da 'Peg

 

[NoCoolHandle]

There is a kill statement that allows you (as a sysadmin) to terminate a process.

My approach would be to query sysprocesses to see what processid's (spid col) are using the database (dbid). Kill them (via a loop) and then drop your db..

Of course you can also "Pause" a server, this would have the effect of stopping any new connection request, but allowing the users who are active to continue there current actions until complete then once there connections closes you could do the drop rebuild thing.

Hope that helps


Rob

 

[SQLBill]

Check out SINGLE_USER mode in the BOL. Use the Index tab and enter SINGLE_USER. The BOL shows how to ALTER DATABASE to put it in single user mode.

-SQLBill

Posting advice: FAQ481-4875

 

[JayKusch]

As NoCoolHandle stated ... You may want to go this route

use master
go
declare @dbname sysname
set @dbname = 'db2kill'	-- substitute your database name here

set nocount on
declare Users cursor for 
	select spid
	from master..sysprocesses 
	where db_name(dbid) = @dbname

declare @spid int, @str varchar(255)

open users

fetch next from users into @spid

while @@fetch_status <> -1
begin
   if @@fetch_status = 0
   begin
      set @str = 'kill ' + convert(varchar, @spid)
      exec (@str)
   end
   fetch next from users into @spid
end

deallocate users

exec ('drop database ' + @dbname)

Thanks

J. Kusch

 

[sRockyHill]

Thank you All - I tried solutions from DBAWinnipeg, and JayKusch. Both solution works well. Thank you

 

[NoCoolHandle]

Ahh stars go to people who write all your code for you

 

[mercwrought]

NoCoolHandle that is what I thought a first too but I have posted code many times and generally they just stop posting. Not that I’m upset that I didn’t get a star but some times I like to know if my solution worked. When I post an answer (I’m calling it an answer but sometimes the cure is worse than the disease) I do so because the question I found interesting or may have need in the future to solve the same or similar problem, not to mention that I’m trying to help others that in the future may help me. Look at me I’m just rambling now… anyways I’m just saying that to me stars are appreciated but sometimes just responding and letting me know how things went are appreciated just as much.

 

[Bajwa11]

hope someone is happy NOW

[highlight]
M.Bajwa

http://www.nextgenlogic.com/bajwa

[/highlight]

 

[mercwrought]

Come on ladies and gentlemen I was just expressing that courtesy is nice and that we have more vested in each other than just some posted code in a forum. We are here to in a way lean on each others shoulders and make each other think. Once more I’m rambling… I really didn’t want a star I have a couple I was just stating a point.


…. Bajwa11 Thx for the star anyways, I know that it was given in good humor..