Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Restore Database Failed: 'Exclusive access could not be obtained because the database is in use.' Expand / Collapse
Author
Message
Posted Monday, April 4, 2011 2:02 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 4:04 AM
Points: 934, Visits: 1,530
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.
Post #1087930
Posted Monday, April 4, 2011 6:33 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 10:07 AM
Points: 14,034, Visits: 28,406
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
The Scary DBA
Author of: SQL Server Query Performance Tuning
SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1088009
Posted Tuesday, April 5, 2011 2:16 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 3:09 AM
Points: 1,296, Visits: 2,267
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).
Post #1088483
Posted Tuesday, April 5, 2011 3:05 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 4:04 AM
Points: 934, Visits: 1,530
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.
Post #1088494
Posted Tuesday, April 5, 2011 3:12 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, September 17, 2014 9:19 AM
Points: 1,307, Visits: 4,508
What is the default database for the job step running the restore?


Post #1088495
Posted Tuesday, April 5, 2011 3:16 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 4:04 AM
Points: 934, Visits: 1,530
master

Thanks.
Post #1088497
Posted Tuesday, April 5, 2011 5:17 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 10:07 AM
Points: 14,034, Visits: 28,406
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
The Scary DBA
Author of: SQL Server Query Performance Tuning
SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1088524
Posted Tuesday, April 5, 2011 2:17 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Tuesday, November 25, 2014 3:14 PM
Points: 4,363, Visits: 9,545
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
Problems are opportunites brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs
Post #1088899
Posted Tuesday, April 5, 2011 2:53 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 10:07 AM
Points: 14,034, Visits: 28,406
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
The Scary DBA
Author of: SQL Server Query Performance Tuning
SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1088934
Posted Tuesday, April 5, 2011 3:08 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, April 15, 2011 11:50 AM
Points: 2, Visits: 10
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
Post #1088942
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse