single_user/multi_user question

[puterkrazy]

The following should kill all SQL users and allow only a single user:

ALTER DATABASE db
SET SINGLE_USER

For some reason this runs forever and ever and does not seem to stop and I have no idea why.

ALTER DATABASE db
SET MULTI_USER
only takes a second to run.

I need this to work on SQL7 and SLQ8, any help would be greatly appreciated. Or any other ways to kill all users? Thanks!

 

[puterkrazy]

Ok it seems that adding 'WITH ROLLBACK IMMEDIATE' gets it to work correctly?

So I have another question.... this statement will only work with SQL 2000, what to use for SQL 7? sp_dboption will not kill current users and you cannot use it if more than one user is in the system at that time. please help!

 

[Sabnac]

For each kill a rollback is initiated.
To kill connections i use a SP created in master

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
Exec ('KILL ' + @ProcessId)
FETCH NEXT FROM CurrentProcesses INTO @ProcessId
END
CLOSE CurrentProcesses
DeAllocate CurrentProcesses