I can't drop a database, please help

Question

Post reply

I can't drop a database, please help

onlo

SSCertifiable

Points: 5080
More actions
November 6, 2008 at 8:18 pm

#72246

Dear all,
I have a database in SQL 2005 Std. Ed. cluster,
its status is (In Recovery / Read-Only ), I want to drop it and then re-attach it, but I have tried all of the following actions and failed :
Detach
Resotre Database DB001 WITH RECOVERY
Drop
Take Offline
It always said some bodies in use, but sp_who2 told me nobody was holding this db.
How I can drop this DB ? Please help me.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Elisabeth Rédei Hall of Fame Points: 3873 More actions · Answer 1

Hi,

If you right-click the database and choose Detach you are given the option to disconnect all users, can you do that?

Run

SELECT rsc_dbid, rsc_type, req_spid

FROM syslockinfo

WHERE rsc_dbid = yourdatabasename

to find out who is holding a lock (you can also run sp_lock but the output can be enormous on a busy server. at least you are limited to the output from a single database with the above query).

HTH!

/Elisabeth

elisabeth@sqlserverland.com
MCITP | MCT
http://sqlblog.com/blogs/elisabeth_redei/
http://linkedin.com/in/elisabethredei

MANU-J. SSC-Dedicated Points: 31126 More actions · Answer 2

Run the below mentioned code(on under database) to kill all connections to this database and then drop the database:

DECLARE @DatabaseName nvarchar(50)

SET @DatabaseName = N'Enter DB Name here'

DECLARE @SQL varchar(50)

SET @SQL = ''

SELECT @SQL = @SQL + 'Kill ' + Convert(varchar, SPId) + ';' FROM MASTER..SysProcesses WHERE DBId = DB_ID(@DatabaseName) AND SPId <> @@SPId

SELECT @SQL

EXEC(@SQL)

Go

DROP Database 'Enter DB Name here''

HTH

MJ

Raghavender Chavva SSCrazy Eights Points: 8669 More actions · Answer 3

I think for the database In Recovery status no user can connect to them.

So there will be other problem in deleting the Database.

Have tried by right click on database and delete option ?

Thanks

Thank You.

Regards,
Raghavender Chavva

Elisabeth Rédei Hall of Fame Points: 3873 More actions · Answer 4

Is the database in Recovery /Pending mode?

SELECT state_desc FROM sys.databases WHERE name = YourDatabase

Then you have to set it in Emergency mode:

ALTER DATABASE YourDatabase SET EMERGENCY

HTH!

/Elisabeth

elisabeth@sqlserverland.com
MCITP | MCT
http://sqlblog.com/blogs/elisabeth_redei/
http://linkedin.com/in/elisabethredei

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 5

onlo (11/6/2008)
It always said some bodies in use, but sp_who2 told me nobody was holding this db.

ALTER DATABASE < DB name > SET OFFLINE WITH ROLLBACK IMMEDIATE

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

onlo SSCertifiable Points: 5080 More actions · Answer 6

Thank every bodies, I fixed the problem by re-install the whole SQL Server 2005.

Thanks a lot, your information is very very useful.