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

Question
-
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
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)s
Santhosh
http://sqlspy.blogspot.com- Proposed as answer by ArmZox Thursday, May 22, 2014 11:25 AM
-
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
-
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.
-
-
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 SnippetUse 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 8, 2012 1:52 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.
-
-
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 SnippetUse 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
-
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 SnippetUSE master;
GO
ALTER DATABASE << db name>>SET MULTI_USER;
GORegards,
Jeroen
-
-
-
Jason Love wrote: What does the Rollback Immediate do? From BOL
the termination option
WITH ROLLBACK IMMEDIATE
in theALTER DATABASE
statement. All incomplete transactions will be rolled back and any other connections to thedb
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 commandMadhu
-
-
-
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 -
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.
-
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.
-
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
-
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();
} -
-
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.
(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.) -
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) -
-
-
-
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.
-
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.
-
-
Following How to Get Exclusive Access to SQL Server Database article will be of help to you.
Hope This Helps!
Thanks
Ashish Kumar Mehta -
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.
-
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.
HEy, a mi me ha venido de lujo esta solucion.
with)s
Santhosh
http://sqlspy.blogspot.com
-
-
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();
}
} -
-
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
-
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
-
-
Open SQL Management Studio and press F8 to open Object Explorer.
There you can find Management Click on Management to view Activity Monitor.
- Proposed as answer by desperatenaveed Saturday, June 23, 2012 9:34 AM
-
-
-
-
I wrote VB.net code to unlock the database. I was tired of always doing this manually, so I wrote a command-line tool in VB.Net to kill database locks . Replace 'test' with your database name and replace my connection string with your own. Connection strings are a different topic all together.
Module Module1
Const SelectId As String = "USE master; " & vbNewLine & "SELECT request_session_id FROM sys.dm_tran_locks WHERE DB_NAME(resource_database_id)='test' AND request_session_id != @@SPID"
Sub Main()
Dim ConnectionString As String = "Data Source=192.168.4.7,1433;Trusted_Connection=True;"
Dim Con As New Data.SqlClient.SqlConnection(ConnectionString)
Dim SqlCmd As New Data.SqlClient.SqlCommand(SelectId, Con)
Con.Open()
Dim Reader As Data.SqlClient.SqlDataReader = SqlCmd.ExecuteReader
If Reader.HasRows Then
Dim Q As New Queue(Of Integer)
Do While Reader.Read()
Dim ID As Integer = Reader.GetInt32(0)
Console.WriteLine("ID " & ID.ToString)
Q.Enqueue(ID)
Loop
Reader.Close()
Console.Write("Do you want to KILL these proccesses (y/n)? ")
Dim Response As ConsoleKeyInfo = Console.ReadKey
Console.WriteLine()
If Response.Key = ConsoleKey.Y Then
Do Until Q.Count = 0
SqlCmd = New Data.SqlClient.SqlCommand("KILL " & Q.Dequeue, Con)
SqlCmd.ExecuteNonQuery()
Loop
My.Computer.Audio.PlaySystemSound(Media.SystemSounds.Asterisk)
End If
Con.Close()
Else
Console.WriteLine("No rows found.")
Reader.Close()
Con.Close()
End If
Console.WriteLine("Done. Press any key to exit...")
Console.ReadLine()
End Sub
End Module
- Edited by RandomMicrosoftUser Monday, July 21, 2014 3:10 PM
-
-
I also encountered this problem when restoring a few databases.
What I did are the following:
1. Rename first the database for restoration using the following queries:
ALTER DATABASE [database_name] SET SINGLE_USER WITH ROLLBACK IMMEDIATE GO EXEC sp_renamedb N'[database_name]', N'[database_name]_OLD'
This is to prevent other applications from using the database, just in case other unknown applications are persistently trying to connect/make queries to the database.
2. Perform the actual restore.
3. Rename the database back to its original name
EXEC sp_renamedb N'[database_name]_OLD', N'[database_name]'
3. Don't forget to set the database to multi-user again:
ALTER DATABASE [database_name] SET MULTI_USER WITH ROLLBACK IMMEDIATE GO
P.S.: In case you are still encountering the error message "Exclusive access could not be obtained because the database is in use", you may want to set your database to multi_user first before doing the actual restore.
ALTER DATABASE [database_name_OLD] SET MULTI_USER WITH ROLLBACK IMMEDIATE GO