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 (https://blog.sqlauthority.com)
22 Comments. Leave new
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…
the post is for case when two or more people are using or executing a query in a single database at the same time, sql is such that u cannot delete a database when it is in use, u have to set the db to single user mode from the multi user mode so that u wil be able to delete it :)
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
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.
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.
hi all ,
in my program, I run two sql scripts by osql.exe.
Script 1 :
Alter Database [RC_DB_NAME] Set SINGLE_USER with Rollback Immediate
GO
EXEC dbo.sp_dbcmptlevel @dbname= [RC_DB_NAME], @new_cmptlevel=110
GO
Alter Database [RC_DB_NAME] Set MULTI_USER
Script 2 :
Select * from Any_Table
Have a error that is catched by sqlexption of .net when run script 2 as follows
System.Data.sqlclient.SqlException: A transport-level error has occurred when sending the request to the server.(Provider :share Memory provider,error:0 – No process is on the other end of the pipe)
Pls help me.
Can’t you just take the database offline?
Thanks a lot.Very good answer!
Hi Dave, Is it possible to keep sql server in single “user group” mode? For example, I want to keep database available only for one particular user group when some jobs are running. Is that is possible in SQL server?
I too am looking for the same solution. I need to ensure that only one specific user (a proxy account under which my SSIS packages run) has access so that the job containing 5 packages cannot be affected. Thanks, Ivan