Not able to drop Database

Question

Post reply

Not able to drop Database

arooj300

Hall of Fame

Points: 3433
More actions
January 29, 2014 at 3:57 am

#288827

Hi,
I have a database which I am not able to drop, yesterday I tried to drop the database but process is running upto 1 hour and database remain same.
while processing the database , I have run sp_who2 and checked what was the issue so for that database CommandShellLogin has blocked , when I tried to kill that process , that has not happened.
please help me on this...

Viewing 5 posts - 1 through 4 (of 4 total)

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

Henrico Bekker One Orange Chip Points: 27652 More actions · Answer 1

Run the following to determine who and how many connections are open against which DB's

SELECT

DB_NAME(dbid) as DatabaseName,

COUNT(dbid) as OpenConnections,

loginame as LoginName

FROM

sys.sysprocesses

WHERE

dbid > 0

GROUP BY

dbid, loginame

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle

arooj300 Hall of Fame Points: 3433 More actions · Answer 2

Hi Bekker,

Thanks for the reply,

I ran the query, i found that noone is using that DB.

Actually I have a problem that I am not able to do in Single user mode or Offline mode also.

Please help me on this

Henrico Bekker One Orange Chip Points: 27652 More actions · Answer 3

Your last reply confused me a bit but I think you meant you can't get the affected DB in offline mode?

ONLY if the above is true AND you intend to take the Database OFFLINE, do the following at your own risk.

To put the DB offline

USE master

GO

ALTER DATABASE YourDatabaseName

SET OFFLINE WITH ROLLBACK IMMEDIATE

GO

to put it back online

USE master

GO

ALTER DATABASE YourDatabaseName

SET ONLINE

GO

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle

arooj300 Hall of Fame Points: 3433 More actions · Answer 4

still you are not getting me. whatever u sent the query, yesterday i ran all the query. The last one query was executed upto 1 hr and its going on, but nothing has happend. I have to forcily stop the query.

what I want to say.....

This is the test DB. Previously we have tested our data in that DB but now I have created other DB for testing. this DB is taking too much space so that I want to drop the DB.

We had connected this DB to the SSIS package but now we have changed the connection string and now its connecting to the new DB.

Now there is no use of this DB , yesterday I have tried with everything like offline , single user mode, but nothing is happened.

when I have tried to drop the DB its showing exclusively lock, database is in use.

but when i check noone is using this DB.

I believe this scenario is clear for you.

Thanks...