cannot drop database because it is currently in use

Posted on : 18-04-2012 | By : Devi Prasad | In : SQL Errors, SQLServerPedia Syndication

0



When you try to drop a database you get the below error:

Msg 3702, Level 16, State 3, Line 1
Cannot drop database “DataBaseName” because it is currently in use.

How to drop database when this error occurs?
1. Check if you are using the database you are trying to drop
– Close all the SQL Server management studio windows that are open
– Re-Connect to the server
– Try to drop the database.

2. Check if you are using the same database connection and trying to drop it.
– Change the database in the connection and try to run the drop statement.

Drop Database Using Same Database

3. Check if the script is trying to use the database that is to be dropped.
[sql]
USE TestShrink
DROP DATABASE TestShrink
[/sql]

Change this to:
[sql]
USE master
DROP DATABASE TestShrink
[/sql]

4. Kill all the sessions that are using the database you are trying to drop
[sql]
EXEC sp_who2
–Run kill spid for each process that is using the database to be dropped.
kill <<processid>> — Kill 57
[/sql]
Kill the sessions and try to drop the database.




Reference : Devi Prasad (sqlserverlearner.com)

Do you like my blog?

If you liked reading this blog, please help spread the word by sharing this blog with your friends.



Need Help On SQL Server?

Cannot Find Solution to your problem (or) If you are looking for some help on SQL Server. Dont worry Click Here to Post your question and solve your issue.




Your Ad Here


Tags: , , , , , , , , , , , , , , , ,