none
Exclusive access could not be obtained because the database is in use

    Question

  • Hi

     

    I am trying to use sql server 2005 management stodio to restore a database. But I got this error message:

     

    Exclusive access could not be obtained because the database is in use

     

    Anyone know how to solve this problem?

     

    Thanks

     

    Li

    Wednesday, April 11, 2007 1:46 PM

Answers

  • when u restoer the database system should have Exculusive lock. in your case some process is using this database. you should kill those process which are using database. otherwise use this command

     

    use Master 

    ALTER DATABASE yourdatabasename SET MULTI_USER WITH ROLLBACK IMMEDIATE;

     

    Drop database yourdatabasename

     

    BTW, though Management studio is good and it support all the feature, you must try all these command (Resotre/Backup/Kill /Single_user Mode) etc from the Query Analyser. It gives u a better understanding and it will clear your basics

     

     

    Madhu

    Thursday, April 12, 2007 7:51 AM
    Moderator

All replies

  • This error is occurring since some one is trying to access the database while the restore is trying to do a restore. Go to the Management -> activity monitor and see if some one is accessing the DB if so kill the process and then run the restore.

    with Smile)s
    Santhosh
    http://sqlspy.blogspot.com
    Wednesday, April 11, 2007 3:19 PM
  • when u restoer the database system should have Exculusive lock. in your case some process is using this database. you should kill those process which are using database. otherwise use this command

     

    use Master 

    ALTER DATABASE yourdatabasename SET MULTI_USER WITH ROLLBACK IMMEDIATE;

     

    Drop database yourdatabasename

     

    BTW, though Management studio is good and it support all the feature, you must try all these command (Resotre/Backup/Kill /Single_user Mode) etc from the Query Analyser. It gives u a better understanding and it will clear your basics

     

     

    Madhu

    Thursday, April 12, 2007 7:51 AM
    Moderator
  • I would NOT recommend dropping the database.  It is a complete waste of time.  If you drop the database, then the first thing that the restore operation has to do is to recreate the database along with reallocating all of the disk space for all of the files.  This can make your restore operation MUCH longer than it really needs to be.
    Thursday, April 12, 2007 9:15 PM
    Moderator
  • I'm having the same issue.  I do not see activity manager under the management tree.  Am I looking in the right place.  A screen shot would be helpful if possible.

    Thanks

    Victor

    Monday, April 16, 2007 2:23 PM
  • Use any script view (such as query analyzer) to execute the following script.  This will put your db in single user mode and execute the restore:

     

    Code Snippet

    Use Master

     

    Alter Database YOURDB
      SET SINGLE_USER With ROLLBACK IMMEDIATE

     
      RESTORE DATABASE YOURDB
      FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\YOURDB.bak'

     

     

    Aaron Smith

    DiaMed - Diabetes Management Software

    http://www.chirondata.com

    • Proposed as answer by imughal Thursday, March 08, 2012 1:52 PM
    Saturday, May 19, 2007 8:03 PM
  • Error 3101
    Severity Level 16
    Message Text
    Exclusive access could not be obtained because the database is in use.

    Explanation
    This error occurs when you attempt to load a backup while users are accessing the database. This error can occur with RESTORE DATABASE or RESTORE LOG. You cannot use the RESTORE DATABASE statement while the database is in use by any user, including yourself.

    Action
    Use the ALTER DATABASE SET SINGLE_USER to remove users from the database.

    Or, wait until all users have finished using the database, and then use the RESTORE DATABASE statement. Make sure that you are not using the database being loaded when you issue the RESTORE DATABASE statement. Although not required, it is best to run the RESTORE DATABASE statement from the master database.


    MAKE USE THAT YOU ARE RUNNING THE QUERY FROM THE MASTER DATABASE!

    - If you invoke the query under the database you are trying to restore, you will continue to receive the Error 3101.

    Wednesday, June 13, 2007 8:13 PM
  •  

    hello,

     

    Where is this option Activity Monitor?

     

    Thanks,
    Dom

    Tuesday, June 19, 2007 8:46 PM
  •  Aaronator wrote:

    Use any script view (such as query analyzer) to execute the following script.  This will put your db in single user mode and execute the restore:

     

    Code Snippet

    Use Master

     

    Alter Database YOURDB
      SET SINGLE_USER With ROLLBACK IMMEDIATE

     
      RESTORE DATABASE YOURDB
      FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\YOURDB.bak'

     

     

    Aaron Smith

    DiaMed - Diabetes Management Software

    http://www.chirondata.com

     

    As I am putting my DB in Single User Mode how do I return to the normal mode?

     

    Thanks

    Tuesday, June 19, 2007 8:50 PM
  •  Felyjos wrote:

     

    As I am putting my DB in Single User Mode how do I return to the normal mode?

     

     

    When you restore a database, it will be restored in the mode in which it was when the backup was made, probably MULTI USER. In this case a restore will suffice.

     

    To put a database to MULTI USER yourself:

     

    Code Snippet

    USE master;
    GO


    ALTER DATABASE << db name>>

    SET MULTI_USER;
    GO

     

     

     

    Regards,

    Jeroen

    Thursday, August 23, 2007 9:20 AM
  • What does the Rollback Immediate do?

     

    Tuesday, August 28, 2007 3:55 AM
  • Thanks! this was very helpful!

     

    Monday, October 29, 2007 6:31 PM
  •  Jason Love wrote:
    What does the Rollback Immediate do?

     

     

    From BOL

      the termination option WITH ROLLBACK IMMEDIATE in the  ALTER DATABASE statement. All incomplete transactions will be rolled back and any other connections to the db  will be immediately disconnected. If any users are connected to the db and some command need exclusive lock of db then that command can not be run . So to terminate the connections we uses this command

     

    Madhu

    Tuesday, October 30, 2007 12:50 AM
    Moderator
  • Worked perfectly!!

    Thursday, February 21, 2008 6:50 PM
  • I tried the suggested solutions here and I am still getting the same error. Anyone have any Ideas?

     

    Wednesday, March 26, 2008 4:58 PM
  • I tested this while having services running that would reconnect to the database.  I found you had to set to Single User Mode, then run sp_who2 to see where the one connection was coming from, and note the SPID.  You can run the kill command for that SPID and the restore in the same transaction, and it should go through.  Here is the sequence I used:

    USE MASTER
    ALTER DATABASE DATABASENAME SET SINGLE_USER WITH ROLLBACK IMMEDIATE
    GO

    -This will make it so only one connection to the database can be made.
    -Run the following command to see where any recurring connections to database are coming from.

    EXEC SP_WHO2

    -Check this list, looking under the DBName column.  If the database is listed, check the ProgramName, and HostName column to see who is attempting to connect.
    -If it is not a service, or other application that would automatically reconnect which can be shut down, note the number in the SPID column to kill the connection, and immediately begin the backup.  Replace SPID below with just the number.

    KILL SPID
    RESTORE DATABASE DATABASENAME FROM DISK = 'X:\PATHTO\BACKUP.BAK'
    GO

    -If this completes successfully, we can set the newly restored database back to multi user mode.

    ALTER DATABASE DATABASENAME SET MULTI_USER WITH ROLLBACK IMMEDIATE
    GO
    Thursday, March 27, 2008 4:10 AM
  • By the principle that no one is suppose to be attached while a restore is being done, just detach and reattach the database. This will kick all sessions off and allow the restore to take place.

     

    No its not very clever way of resolving these issues, but its effective with out being drastic.

    Sometimes rogue sessions stay connected for whatever reason and are a nuisance to stop. So the above method just eliminates this time consuming process.

     

    The same effect could possibly be had be stopping the agent.

     

    The full methods to attach and detach is…

     

    Right click on the database in question.

    Go to tasks and detach.

     

    To reattach

     

    Right click on Databases

    Go to Attach

     

    Browse to the Mdf and Ldf files on the relevant drives.

     

    Now try your restore.

    Tuesday, July 08, 2008 1:20 PM
  • Detaching and reattaching the database is a complete waste of time.  If you have something sitting on the other side that is going to automatically create a connection, you are still going to wind up with the possibility of someone gaining a connection to the database as soon as it is attached, but before the restore starts.  If ALTER DATABASE...WITH ROLLBACK IMMEDIATE doesn't get the connections to stay out long enough to get the restore kicked off, then you can utilize DDL triggers, specifically logon triggers to control access to the database and keep everyone out while the restore kicks off.

     

    Thursday, July 10, 2008 4:31 AM
    Moderator
  • We are having the same problem here. 

     

    I have a database, created by my app, over which I wish to restore a backup taken from another database that is controlled by the app (the underlying structures are exactly the same).

     

    I have set the database to single user and can see this reflected in SSMS

     

    I have run sp_who and can see absolutely no other connections to the database.

     

    I am receiving the "Exclusive access..." message

     

    I am going to take the scripted approach posted above, but have concerns about the Full Text elements of the restore.  When performing a manual restore, I redirect these elements to another folder structure created specifically (we don't need them for the purposes of this restore). 

     

    The originally intend restoring to "sysft.EMGR_Item1_FullText", etc.

     

    Pardon what may be complete ignorance, but if I attempt the restore using my backup (taken 2 days ago) using the scripted approach, will the FullText elements (which I can't therefore redirect) cause any problems for the existing live database (from which the backup was taken)

     

    many thanks for any and all replies.

     

    Steven

     

     

    Thursday, August 14, 2008 12:37 PM
  •  Li556035 wrote:

    Hi

     

    I am trying to use sql server 2005 management stodio to restore a database. But I got this error message:

     

    Exclusive access could not be obtained because the database is in use

     

    Anyone know how to solve this problem?

     

    Thanks

     

    Li




    If you don not have any process running on your pc that uses your database like Enterprise Manager or any other third party application, use

     

    SqlConnection.ClearAllPools();

     

     in C# code before restoring. Like the following code:


           

    Code Snippet

    public void BackupAndRestore(string Query)

    {

         SqlConnection.ClearAllPools();

     

    SqlConnection connection = new     SqlConnection(ConfigurationManager.AppSettings["ConnectToMasterDB"]);

         SqlDataAdapter da = new SqlDataAdapter();

         da.SelectCommand = new SqlCommand();

         da.SelectCommand.Connection = connection;

         da.SelectCommand.CommandText = Query;

         da.SelectCommand.ExecuteNonQuery();

    }


    Saturday, September 06, 2008 11:38 AM
  • Ensure you are in the [master] database, not the database you are trying to RESTORE, otherwise you become the connection that is preventing exclusive access to the DB.

     

    Regards,

    -Darius

    • Proposed as answer by Lucian L Friday, January 07, 2011 12:02 AM
    Friday, September 12, 2008 3:02 PM
  • detach and reattach the database. This will kick all sessions off and allow the restore to take place.
    No its not very clever way of resolving these issues, but its effective with out being drastic.

    This worked perfectly for me and was an easy fix.

    (As someone else comments, though, it may not help if you happen to have some other process that immediately makes a new connection to the database as soon as it's re-attached.)
    Tuesday, July 07, 2009 9:50 AM
  • On SQL Enterprise Manager 2000, there was function All Tasks > Detach Database > Clear Button.
    That Clear button is absent in SQL Management Studio, and only checkbox to Drop Connection.
    Actually with Clear button you don't have to re-attach the database, because the purpose just to drop any active connection.

    To SQL Team please add this function to upcoming service pack or release, and might be better to have under All Tasks > Drop Active Connections > Select Connection (All)
    Wednesday, December 23, 2009 9:41 AM
  • Please check that whether any instance of query window is opened with same DB name?

    This holds the current db resource to which you are going to override (restore).

    Solution: Just close this window from query analyzer.  :)

    -Rajiv Giri
    Headstrong India
    Monday, February 15, 2010 1:55 PM
  • Thank you Darius

    it worked

     

    Regards,

    Nabi

     

    Tuesday, June 01, 2010 1:39 PM
  • Thank you Darius !

    It worked very good !

    • Proposed as answer by GS2010 Wednesday, July 28, 2010 7:13 PM
    Tuesday, June 01, 2010 4:37 PM
  • Thanks for all the replies - they were helpful.

    If anyone wants to know - I had to stop server - try backup - unable to connect to server - Start server - backup and it worked.

    Then I went to log in but it reported failure to load database and after about 3 goes or 2 mins it was all restored.

     

    Wednesday, July 28, 2010 7:13 PM
  • I had the same problem. If you use management studio, choose the option in the restore window which says "Restrict access to the restored database" and it would resolve the issue.

    After the restore is complete reset the database back to multi_user mode.

    Tuesday, October 19, 2010 6:12 PM
  • bru, 

    select database , bring it offline and bring it online and try to restore database 

     

    that worked for me

     

    Tiki

    Wednesday, February 16, 2011 11:11 AM
  • Following How to Get Exclusive Access to SQL Server Database article will be of help to you.

    Hope This Helps!

    Thanks
    Ashish Kumar Mehta

    Friday, February 18, 2011 6:44 AM
  • The way we fixed it was a bit more simpler then all these:

    (using SQL Server 2005 as an example)

    1.  Go to the Activity Monitor within Management dropdown

    2.  On the left pane under "Select a page" click on "Locks by Object".

    3.  Scroll over to the right & sort by "Database".

    4.  Find the database that is appears to be locked (and trying to restore) and take note of the "Process ID" on the left.

    5.  Click back to "Process Info" under "Select a page" and find the Process ID # from step #4.

    6.  KILL IT WITH SWIFT PREJUDICE!

    7.  Activate restore.

    8.  Snicker as your restore is happening & the offending person is trying to reconnect.

    Friday, April 15, 2011 3:00 PM
  • This error is occurring since some one is trying to access the database while the restore is trying to do a restore. Go to the Management -> activity monitor and see if some one is accessing the DB if so kill the process and then run the restore.

    with Smile)s
    Santhosh
    http://sqlspy.blogspot.com
    HEy, a mi me ha venido de lujo esta solucion.
    Wednesday, April 27, 2011 4:43 PM
  • gracias!!
    Wednesday, April 27, 2011 4:43 PM
  • i had this problem too, but i resolved it.

    u should:

    because of u are using ur db so u can not restore it by itself, therefor u should login with another db like master then u can restore ur db

            public void RestoreBackup(string DBName, string Path, string BackupFileName)
            {
                SqlConnection con = new SqlConnection("data source=Bahman-pc;Initial Catalog=Master;User ID=sa;Password=123;Integrated Security=True;   Trusted_Connection=true;");
                SqlCommand com = new SqlCommand();
                string UseMaster = "USE master";
                string restore = "RESTORE DATABASE Battalion FROM DISK ='" + Path+BackupFileName + "' WITH FILE = 1 , REPLACE";           
                string Alter1 = @"ALTER DATABASE [" + DBName + "] SET Single_User WITH Rollback Immediate";           
                string Alter2 = @"ALTER DATABASE [" + DBName + "] SET Multi_User";
                try
                {
                    con.Open();
                    com.Connection = con;
                    com.CommandText = UseMaster;
                    com.ExecuteNonQuery();
                    com.CommandText = Alter1;
                    com.ExecuteNonQuery();
                    com.CommandText = restore;
                    com.ExecuteNonQuery();
                    com.CommandText = Alter2;
                    com.ExecuteNonQuery();               
                }
                catch (Exception ex)
                {
                    com.CommandText = Alter2;
                    com.ExecuteNonQuery();
                }
                finally
                {
                    com.Clone();
                }
            }

    • Proposed as answer by yanzi0407 Thursday, September 01, 2011 7:04 AM
    • Unproposed as answer by yanzi0407 Thursday, September 01, 2011 7:04 AM
    Friday, July 15, 2011 4:54 PM
  • Restarting the SQL services would also fix the problem. It fixed for me :)
    Monday, October 10, 2011 8:44 PM
  • Hi,

    I am having the same issue on a SQL 2008 database.  The website is not live yet, so other than me, nobody would be trying to connect to it.  The database is on a sql only web server.  The website is on another web server.  I have several identical sites with their respective databases all running on these machines with no issue.

    When I try to run:

    Use Master
      
    Alter Database myDatabaseName
      SET SINGLE_USER With ROLLBACK IMMEDIATE 

    (of course putting my actual database name there :)

    I get this error:

    Msg 5061, Level 16, State 1, Line 5
    ALTER DATABASE failed because a lock could not be placed on database 'myDatabaseName'. Try again later.
    Msg 5069, Level 16, State 1, Line 5
    ALTER DATABASE statement failed.

    Any ideas what's up?

    thanks

    David

    Thursday, October 20, 2011 3:02 PM
  • I was trying to restore but was not letting me, even after killing spids.  This worked for me.  Run it as one transaction.

    use Master 

    ALTER DATABASE yourdatabasename SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

    restore database databasename

    from ...

     

    Thanks Madhu

    Sunday, December 18, 2011 2:04 AM
  • This is excessive overkill if you have dozens of users or more connected.  I would not recommend this procedure at all.
    Thursday, March 08, 2012 2:26 PM
  • Open SQL Management Studio and press F8 to open Object Explorer.

    There you can find Management Click on Management to view Activity Monitor.

    Saturday, June 23, 2012 9:33 AM
  • take the database offline and close connections bring in back online and then do the restore
    • Proposed as answer by siauwei Tuesday, September 18, 2012 8:13 AM
    • Unproposed as answer by siauwei Tuesday, September 18, 2012 8:13 AM
    Friday, July 27, 2012 7:57 AM
  • Very Tanks for:

    SqlConnection.ClearAllPools();

    Tuesday, December 18, 2012 9:55 AM
  • Thank you :)
    Tuesday, May 21, 2013 4:05 PM
  • Thanks for this!  Setting the database to single user mode did NOT work for us but the drop/reattach worked great!
    Wednesday, March 19, 2014 4:59 PM