SQL SERVER – ALTER DATABASE dbname SET SINGLE_USER WITH ROLLBACK IMMEDIATE

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)

Solarwinds

Previous Post
SQLAuthority News – Converting a Delimited String of Values into Columns
Next Post
SQLAuthority News – Ahmedabad Community Tech Days – Jan 30, 2010 – Huge Success

Related Posts

21 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…

    Reply
    • 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 :)

      Reply
  • Great post,
    Thanks,
    Jinesh.

    Reply
  • 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

    Reply
  • 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

    Reply
  • 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

    Reply
  • 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

    Reply
  • 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

    Reply
  • Use

    ALTER DATABASE [Demo]
    SET MULTI_USER

    Reply
  • Zeeshan Khan
    July 2, 2012 3:31 pm

    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

    Reply
  • salmansohail@live.co.uk
    August 28, 2012 5:05 pm

    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.

    Reply
  • 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.

    Reply
  • I am getting above error while restoring Database from file

    Reply
  • 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

    Reply
  • 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?

    Reply
  • 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.

    Reply
  • 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.

    Reply
  • Can’t you just take the database offline?

    Reply
  • Thanks a lot.Very good answer!

    Reply
  • 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?

    Reply

Leave a Reply

Menu