I'm sorry to say that I don't have a copy of SQL Server 6.5 still running. However, the following SQL Server 2000 script may be adaptable to your situation. It's called 'Kill all users in a DB' and I found it in the script library at SQLServerCentral.com.
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)
Now this specific script kills all spids in a given user database. You could just as easily set it to kill spids on any other aspect of sysprocesses.
Do you have comments on this Ask the Expert Q&A? Let us know.
This was first published in July 2005