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 (http://blog.SQLAuthority.com)

23 thoughts on “SQL SERVER – ALTER DATABASE dbname SET SINGLE_USER WITH ROLLBACK IMMEDIATE

  1. 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…

    Like

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

      Like

  2. 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

    Like

  3. 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

    Like

  4. Pingback: SQL SERVER – Making Database to Read Only – Changing Database to Read/Write Journey to SQLAuthority

  5. 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

    Like

  6. 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

    Like

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

    Like

  8. 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

    Like

  9. 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.

    Like

  10. Pingback: SQL SERVER – Weekly Series – Memory Lane – #005 « SQL Server Journey with SQL Authority

  11. Pingback: SQL SERVER – Weekly Series – Memory Lane – #006 « SQL Server Journey with SQL Authority

  12. 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.

    Like

  13. 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

    Like

  14. 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?

    Like

  15. 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.

    Like

  16. 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.

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s