I have recently been conducting lots of training on SQL Server technology. During these trainings, I quite often create new databases and drop them as well. Many times, I am not able to drop the database as one of my instances might be using the database. As I am working on my laptop and very confident regarding dropping the database, I always take my database in single user and drop it immediately.
ALTER DATABASE [YourDbName]
SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
The above query will rollback any transaction which is running on that database and brings SQL Server database in a single user mode.
The usual error due to database in use is as follows:
Msg 3702, Level 16, State 3, Line 1
Cannot drop database “YourDbName” because it is currently in use.
as the ALTER script mentioned on the top of the post will remove that error. Please make sure that if you are on production server, alter database should be used very carefully.
Reference : Pinal Dave (http://blog.SQLAuthority.com)
Hi Pinal,
I cant get this POST.
I created the database and then dropped the DB..it didnt throw any error…
What is this script doing???
please share once again…
Great post,
Thanks,
Jinesh.
Nitin,
You will not receive the error that Pinal described unless there are open connections to the database in question. Try creating the database, opening a connection, and then attempting a DROP from a different session.
Mihir
What do I need to do if I want to attach the database again?
It is because I need to detach the database and remove the log files and then attach again.
Thank you.
Dave
[...] If you face error that if database is already in use, you can resolve the same by making database in single user mode – here is the guideline SQL SERVER – ALTER DATABASE dbname SET SINGLE_USER WITH ROLLBACK IMMEDIATE. [...]
Other helpful thing that helped was to do a sp_who2 and kill the spids of the users that are connected. Once I did that then I was able to Alter Database
Hello there.
Jasen, there are applications that will reconnect automatically if the connection is dropped (killing the SPIDs), so your solution is good for certain scenarios.
Best regards,
Calin
ALTER DATABASE [Demo]
SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
after run above query my demo database work only single user,if i want to again same database as multiuser then with query apply
Regards
Mangesh
Use
ALTER DATABASE [Demo]
SET MULTI_USER
When i attach any database in SQL 2008 it display as Read Only, When i try to change Database–> Properties –>Options–>Status to Read Only to False but it display error
“Alter Failed for Database
——————————
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
——————————
Unable to open the physical file “”. Operating system error 5: “5(Access is denied.)”.
Unable to open the physical file . Operating system error 5: “5(Access is denied.)”.
Database ” cannot be opened due to inaccessible files or insufficient memory or disk space.
Please help me
Thanks
Use
ALTER DATABASE [Demo]
SET MULTI_USER
I am getting error msg 5064 level 16 sate 1, line 2
msg 5069 level 16, state 1, line 2
alter database statement failed.
[...] thing where we can use ALTER syntax to take database in single user mode. Read more about that over here and [...]
[...] dropping it. Here is the quick tutorial regarding how to bring the database in single user mode: Using T-SQL | Using [...]
Hi Pinal, I am getting “Exclusive access could not be obtained because the database is in use.” could you please help? I have tried all the solutions provided in your blogs, but it didn’t help me this time.
It means that the database is used by some others. Close all connections and try
I am getting above error while restoring Database from file
hello
i performe this uery excute in sql2005:
1) USE [master]
GO
ALTER DATABASE GuestAutomationSystemDB SET READ_WRITE WITH NO_WAIT
GO
2)ALTER DATABASE GuestAutomationSystemDB
SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
This type of Error Occured:
Msg 5061, Level 16, State 1, Line 1
ALTER DATABASE failed because a lock could not be placed on database ‘GuestAutomationSystemDB’. Try again later.
Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.
please help me
I am having the same issue as Parimal. During an SSIS copy database we run the same statement (#2). Recently we have started to recieve the same lock error.
Is there a method to guarentee a kill of all connections priro to the detach poriton of the copy process?
I beleive a have the solution. – thanks to Jonathan K. at SQLSkills. The ALTER DATABASE dbName command should be issued using a connection to the dbName database, not master (or any other DB). This way your connection is picked up as the single_user connection. Otherwise, the next process that connects is picked up.