SQL SERVER – ALTER DATABASE dbname SET SINGLE_USER WITH ROLLBACK IMMEDIATE

« SQLAuthority News – Converting a Delimited String of Values into Columns

SQLAuthority News – Ahmedabad Community Tech Days – Jan 30, 2010 – Huge Success »

SQL SERVER – ALTER DATABASE dbname SET SINGLE_USER WITH ROLLBACK IMMEDIATE

February 11, 2010 by pinaldave

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)

Posted in Pinal Dave, SQL, SQL Authority, SQL Query, SQL Scripts, SQL Server, SQL Tips and Tricks, T SQL, Technology | 9 Comments

9 Responses

on February 11, 2010 at 10:36 am | Reply Nitin Sharma

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…
on February 11, 2010 at 12:03 pm | Reply Jinesh

Great post,
Thanks,
Jinesh.
on February 11, 2010 at 10:08 pm | Reply Mihir

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
on February 26, 2010 at 3:29 am | Reply Dave Peter

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
on April 16, 2011 at 7:01 am | Reply SQL SERVER – Making Database to Read Only – Changing Database to Read/Write Journey to SQLAuthority

[...] 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. [...]
on May 26, 2011 at 3:26 am | Reply Jasen

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
on August 3, 2011 at 1:09 pm | Reply visualtalks

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
on October 15, 2011 at 4:44 pm | Reply Mangesh

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
on November 16, 2011 at 11:31 am | Reply karthik

Use

ALTER DATABASE [Demo]
SET MULTI_USER