How should I kill any process for a database inorder to detach it?

• Sameh Mohamed

  SSC Veteran

  Points: 224

  More actions

  December 5, 2004 at 2:43 am

  #102303

  Can you please help me with this!

  I made a tool (using visual basic 6) which would update a database structure by creating a new one with the new structure, copy the data and then drop the original data and restore the new one .

  Anyway, whenever I try to detach (or drop) the old database it returns an error indicating that it still in use!

  I tried a code I found to kill the processes in the database which helped

  DECLARE @sql VARCHAR(500)

  SET @sql = ''

  SELECT @sql = @sql + ' KILL ' + CAST(spid AS VARCHAR(10)) + ' ' From master.dbo.sysprocesses WHERE DB_NAME(dbid) = '" & oldfile & "' AND spid > 50 AND spid <> @@SPID

  EXEC(@sql)

  I use this code by sending it as an adodb command.execute

  but I found that I have to wait for other 15 sec inorder to free that database and then drop the file!

  Some times some machines would require even more time than 15 sec

  Is there any reliable way to detach the database??

  Thanks

  Sameh

   

• December 8, 2004 at 8:00 am

  This was removed by the editor as SPAM

• Frank Kalis

  SSC Guru

  Points: 111183

  More actions

  December 8, 2004 at 8:23 am

  #533055

  This should kick every user out of the db:

  USE PUBS

  --The friendly method:

  ALTER DATABASE Pubs

  SET SINGLE_USER WITH ROLLBACK AFTER 15 SECONDS

  --The not so friendly method

  ALTER DATABASE Pubs

  SET SINGLE_USER WITH ROLLBACK IMMEDIATE

  --After all is done

  ALTER DATABASE Pubs

  SET MULTI_USER

  You might also want to search the script section here.

  --
  Frank Kalis
  Microsoft SQL Server MVP
  Webmaster: http://www.insidesql.org/blogs
  My blog: http://www.insidesql.org/blogs/frankkalis/[/url]