unable to drop database, Currently in Use


Sometimes when we try to restore/drop the db, we sometimes get following message.

"Cannot drop database {YourDbName} because it is currently in use."
 
This happens because there are active connections to the database, so it does not allows you to perform drop/restore of database.
 
How can we solve this problem, without dropping the database. We will have to kill the active connection and then perform our drop/restore related task on the db.
 
So in this article I would like to discuss few ways of doing it.
 
OPTION 1:
- You must check there are no query window open with the database selected.
 if there are any active query window open for respective database, you will not be able to drop or restore the db.
  solution:  Close all the active query windows and then try to drop/restore the db.
 
OPTION 2: 
- You can turn your database to OFFLINE mode , which will inturn kill all the active connection.
  So your step will be something like 
  - Bring your database to offline mode
  - Bring it Online
  - Run your queries, like restore.
	Query to make your database offline.

	ALTER DATABASE [Database_Name] SET OFFLINE WITH ROLLBACK IMMEDIATE 
OPTION 3:
- Another option is to set your database to single user mode and then try to run your query. 
  As soon as you make this to single user mode, it kills all the active connections.
  So your step will be something like 
  a) set to single user mode with roll back
  b) run your queries like restore etc.
  c) set it back to multi user mode.
use master
	ALTER DATABASE YourDatabase SET SINGLE_USER WITH ROLLBACK IMMEDIATE 
	--do you stuff here 
	ALTER DATABASE YourDatabase SET MULTI_USER
Option 4:
- Another option is to execute sp_who2 and get all the spids with the user details. Now you can kill the spids of the users that are connected.
 
Please let me know if there is any issue with this solution Or if there is any alternative way to do this.

Hope this helps!!! Thanks for visiting.
 

Author details:

Add Comments



www.Gilgh.com