not able to drop database which is suspect mode

• pmadhavapeddi22

  SSCertifiable

  Points: 5306

  More actions

  May 20, 2010 at 1:48 am

  #156969

  Hi

  I am unable to drop database which is in suspect mode.

  No connection is existing for that particular db but still it is showing error as " cannot drop database becuase its currently in use"

  when i am trying to detach then it is showing error as

  " alter database failed for this db "

  or

  the database is in single user mode and a user is currently conneted to it

  I also tried to make as multi user , but still not happening,

  I want to drop that db with out restarting my service

  Any help ?

  Thanks

• sanketahir1985

  SSCarpal Tunnel

  Points: 4473

  More actions

  May 20, 2010 at 2:11 am

  #1169917

  is it ur production DB?

  fire following command

  select spid from sysprocesses where dbid=db_id('DBNAME')

  identify query of this spids using "Sp_who4 "

  get the attached script from http://www.sqlservercentral.com/Forums/Topic865824-146-1.aspx

  if it is dead process u can kill it

  using

  "KILL SPID"

  Sanket Ahir
  Don't run behind the success, Try to be eligible & success will run behind u......

• pmadhavapeddi22

  SSCertifiable

  Points: 5306

  More actions

  May 20, 2010 at 3:17 am

  #1169958

  Hi,

  I did find any entry with my db name in sysprocess or sp_who2 or sp_who4

  any help

  Thanks

• pmadhavapeddi22

  SSCertifiable

  Points: 5306

  More actions

  May 20, 2010 at 4:09 am

  #1169971

  sorry for the typo error, please find the corrected one below

  Hi,

  I did not find any entry with my db name in sysprocess or sp_who2 or sp_who4

  any help

  Thanks

• MANU-J.

  SSC-Dedicated

  Points: 31126

  More actions

  May 20, 2010 at 11:28 am

  #1170358

  Execute drop just after running the kill connections script.

  declare @spid smallint

  declare @cmd varchar(20)

  declare spid_cursor cursor for

  select spid from master.dbo.sysprocesses

  where dbid=db_id('enter dbname here')

  OPEN spid_cursor

  FETCH NEXT FROM spid_cursor

  INTO @spid

  WHILE @@FETCH_STATUS = 0

  BEGIN

  SELECT @cmd = 'kill ' + CONVERT(char, @spid)

  EXEC (@cmd)

  FETCH NEXT FROM spid_cursor

  INTO @spid

  END

  CLOSE spid_cursor

  DEALLOCATE spid_cursor

  set nocount off

  GO

  Drop Database 'dbname'

  Note: Make sure you really just want to drop the database.