October 8, 2011 at 3:09 am
I was trying to restore a database AAA from a backup file AAA of different server by overwriting the existing one.
But, I was unable to restore as the error said: "An exclusive lock cannot be obtained as the database is in Use."
I doubt if this is an exact error from the SQL Server.
I completely verified that there are no active processes on the database from the table sys.sysprocesses.
Also, checked to detach the database which also threw the same error inspite of me not finding any active connections hyperlink in the detaching wizard for the database.
Here, visibly, I wasn't able to find any connection to the database through any means I tried.
I have the following questions after I was unable to restore the database.
Is this really possible that this kind of situation does exist in reality on a healthy server ?
If yes, I would like to have a preview of the scenarios in which this could probably happen.
I read on some site that the SQL Services should be restarted. But, is it not possible to have this rectified without restarting the services ?
Are there any other ways to check the database connections other than sp_who2, sys.sysprocesses which couldn't return us any records ?
October 8, 2011 at 10:46 am
try running a select from sys.dm_exec_requests to see if there are any connections on that database that are active.
Are you, yourself, connected to the database when you try to restore it? If so, that could be blocking it.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
October 8, 2011 at 1:16 pm
For such restores ( everybody out I'll overwrite it all ) I always start the operation with
Alter database Yourdb set Read_only with rollback immediate ;
restore database ....
Notice there is no GO between those two statements, so no one else can interfere your operation !
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
October 8, 2011 at 9:01 pm
Yes, I verified and couldn't find any. I had taken care I was not using the database. I was using Master Database.
Later, the instance was restarted to kill the lock on the database.
October 8, 2011 at 11:12 pm
Is that db source for a snapshot db ?
DBCC LOGINFO may cause log file locking due to a bug in SQL2005.
I don't think it got fixed for 2005.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
October 10, 2011 at 8:00 pm
I usually set the database to single user before the restore and back to multi after the restore has been done. That usually obtains an exclusive lock/access to the database to perform the restore.
ALTER DATABASE <DatabaseName>
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE
GO
/*
Restore database
*/
ALTER DATABASE <DatabaseName>
SET MULTI_USER
WITH ROLLBACK IMMEDIATE
GO
October 11, 2011 at 2:32 am
jsauni (10/10/2011)
I usually set the database to single user before the restore and back to multi after the restore has been done. That usually obtains an exclusive lock/access to the database to perform the restore.
ALTER DATABASE <DatabaseName>
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE
GO
/*
Restore database
*/
ALTER DATABASE <DatabaseName>
SET MULTI_USER
WITH ROLLBACK IMMEDIATE
GO
ALTER DATABASE <DatabaseName>
SET MULTI_USER
WITH ROLLBACK IMMEDIATE
This portion of the code above is redundant. After restoration, the database is automatically in multi_user mode.
October 11, 2011 at 2:37 am
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply