April 4, 2011 at 2:02 am
Hi,
While trying to restore a database (with replace option), I am getting the below error -
Exclusive access could not be obtained because the database is in use. [SQLSTATE 42000] (Error 3101) RESTORE DATABASE is terminating abnormally. [SQLSTATE 42000] (Error 3013). The step failed.
Though, before kicking off the refresh, I took the database in 'single user' mode and also used the below script to KILL all the connected sessions.
DECLARE @SPIDSTR varchar(8000)
DECLARE @ConnKilled smallint
DECLARE @DBName varchar(100)
SET @SPIDSTR = ''
SET @ConnKilled = 0;
SET @DBName = '<database>'
SELECT @SPIDSTR=coalesce(@spidstr,',' )+'KILL '+convert(varchar, spid)+ '; '
FROM MASTER..SYSPROCESSES WHERE dbid=db_id(@DBName);
Print @SPIDSTR;
IF LEN(@spidstr) > 0
BEGIN
EXEC(@spidstr);
Print @SPIDSTR;
SELECT @ConnKilled = COUNT(1)
FROM master..sysprocesses WHERE dbid=db_id(@DBName)
END
Can you please suggest, what went wrong, in my adhered process?
Atlast, I had to drop the database and now when I am trying to restore, it is working fine.
Kindly suggest with your opinion.
Thanks.
April 4, 2011 at 6:33 am
Even if you place it in single user mode, if you're connected to that database or another connection is there, it might still be active when you try to run the backup. Generally I don't use single user mode, I use restricted user, but that assumes that most of our logins are not 'sa' and the ones that are I can kill or identify and alert individually.
"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
April 5, 2011 at 2:16 am
Are you doing the restore from SSMS rather than a query? When SSMS opens its restore window, it starts a session using your default database. If this is the database you're trying to restore, you get the error described. (This has always annoyed me).
April 5, 2011 at 3:05 am
no, refresh of the database is being performed using a SQL JOB. I dont use the SSMS generally for this kind of tasks, it is prone to hang for large size bkps.
Thanks.
April 5, 2011 at 3:12 am
What is the default database for the job step running the restore?
April 5, 2011 at 3:16 am
master
Thanks.
April 5, 2011 at 5:17 am
Can you try using restricted user instead of single user? I've seen applications that constantly refresh their connection so they can squeek in before you start the restore.
"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
April 5, 2011 at 2:17 pm
Grant Fritchey (4/5/2011)
Can you try using restricted user instead of single user? I've seen applications that constantly refresh their connection so they can squeek in before you start the restore.
If this doesn't work, then you have some process refreshing its connection and is using a privileged account.
I prefer taking the database offline myself - this insures that nobody can access that database and allows the restore to proceed with no problems.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
April 5, 2011 at 2:53 pm
Jeffrey Williams-493691 (4/5/2011)
Grant Fritchey (4/5/2011)
Can you try using restricted user instead of single user? I've seen applications that constantly refresh their connection so they can squeek in before you start the restore.If this doesn't work, then you have some process refreshing its connection and is using a privileged account.
I prefer taking the database offline myself - this insures that nobody can access that database and allows the restore to proceed with no problems.
True, but I prefer to not let anyone have privileged access to the server except other DBAs.
"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
April 5, 2011 at 3:08 pm
Hi Sourav Mukherjee,
You can use the below command.
ALTER DATABASE [mydb] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
RESTORE DATABASE [mydb] FROM DISK='f:\backup\mydb.bak'
GO
April 5, 2011 at 7:35 pm
Grant Fritchey (4/5/2011)
True, but I prefer to not let anyone have privileged access to the server except other DBAs.
Of course, I would prefer that also - but I have various systems where the vendors have setup their applications to use privileged accounts. I don't have a choice...unfortunately.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
September 27, 2012 at 12:28 am
In my case: I was logged in to SSMS as 'user_x'. Default database for 'user_x' was 'Test_DB'.
I got same errormessage when I tried to restore in this session 'Test_DB'.
When I logged out as 'user_x' and logged in SSMS as another user (that did not have default database 'Test_DB'), restore was successful.
September 27, 2012 at 12:30 am
Good.
Thanks.
September 27, 2012 at 4:48 am
Make sure no other instances of SSMS are open.
If you have multiple tabs open, make sure they are not pointing to the DB in question.
You could also try restarting the service.
Any replication going on? Or any other jobs or triggers pointing to your troublesome DB?
July 29, 2014 at 11:08 am
Dennis Post (9/27/2012)
...If you have multiple tabs open, make sure they are not pointing to the DB in question...
This helped and fixed the problem, thank you
(Using... MSSQL 2012 DEV EDT)
Msg 3101, Level 16, State 1, Line 2
Exclusive access could not be obtained because the database is in use.
Msg 3013, Level 16, State 1, Line 2
RESTORE DATABASE is terminating abnormally.
(after logs growth due to uncommitted transaction and also data was being inserted at the same time.. the log is in a huge size and adding additional log then backup-ing the log and restoring the database full backup and restoring the recent log..... all things came to normal)
ww; Raghu
--
The first and the hardest SQL statement I have wrote- "select * from customers" - and I was happy and felt smart.
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply