Cannot restore database. Cannot get exclusive accesss

Question

Cannot restore database. Cannot get exclusive accesss

kjgreen1112

SSC Enthusiast

Points: 107
More actions
November 29, 2013 at 5:50 pm

#301916

While restoring database in sql server 2012, i am getting the following error in sql server 2012
"Exclusive access could not be obtained because the database is in use "
No users are connected except me as dba.
I have killed the SPID that is connected to the DB but it keeps coming back..
Any thoughts?

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

sqlsurfing SSCarpal Tunnel Points: 4883 More actions · Answer 1

kjgreen1112 (11/29/2013)
While restoring database in sql server 2012, i am getting the following error in sql server 2012
"Exclusive access could not be obtained because the database is in use "
No users are connected except me as dba.
I have killed the SPID that is connected to the DB but it keeps coming back..
Any thoughts?

There must be a server/process/application/etc that is re-connecting to your database after you kill the session.

You could "off-line" your database so no one can re-connect then run the restore.

ALTER DATABASE [DATABASE] SET OFFLINE WITH ROLLBACK IMMEDIATE

--------------------------------------------------
...0.05 points per day since registration... slowly crawl up to 1 pt per day hopefully 😀

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 2

Set it to "single user" with "Rollback immediate" then immediately to "multi-user" and then immediately execute the restore command. Of course, these 3 things should be in a script.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

sqlsurfing SSCarpal Tunnel Points: 4883 More actions · Answer 3

Jeff Moden (11/30/2013)
Set it to "single user" then immediately to "multi-user" and then immediately execute the restore command. Of course, these 3 things should be in a script.

Good one Jeff, that would be quicker than bringing off/online. 😛

--------------------------------------------------
...0.05 points per day since registration... slowly crawl up to 1 pt per day hopefully 😀

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 4

sqlsurfing (12/1/2013)
Jeff Moden (11/30/2013)
Set it to "single user" then immediately to "multi-user" and then immediately execute the restore command. Of course, these 3 things should be in a script.
Good one Jeff, that would be quicker than bringing off/online. 😛

Thanks for the kudo but I forgot to include the "Rollback immediate" thing, though. I've updated my original post.

I do a lot of restores in my Dev and QA environments. They both have a wealth of little connection greedy web services running against them so I had to do something that ran quick enough to "get in" before they did. I also had the experience of my connection failing after I set a database to "single user" and having one of those little buggers grabbing the only connection available. That's why I do the multi-user thing immediately after the single-user thing. It hasn't failed me yet (as I knock on wood).

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Summer90 SSC-Dedicated Points: 32891 More actions · Answer 5

Create this stored Proc, run it as a step before your restore step. I use this to refresh NonProd databases all the time. The syntax for the kill step is this:

exec sp_killallprocessindb [TESTDB_NAME_HERE]

USE [master]

GO

/****** Object: StoredProcedure [dbo].[sp_killallprocessindb] Script Date: 12/02/2013 10:49:58 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

create proc [dbo].[sp_killallprocessindb]

@dbname varchar(100)

as

if db_id(@dbname) = null

begin

print 'database dose not exist'

end

else

begin

declare @spid varchar(30)

declare tmpcursor cursor for

select 'kill ' + convert(varchar, spid) as spid

from master..sysprocesses

where db_name(dbid) = @dbname

and spid <> @@spid

and dbid <> 0

open tmpcursor

fetch next from tmpcursor

into @spid

while @@fetch_status = 0

begin

exec (@spid)

fetch next from tmpcursor

into @spid

end

close tmpcursor

deallocate tmpcursor

end

GO

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 6

Markus (12/2/2013)
Create this stored Proc, run it as a step before your restore step. I use this to refresh NonProd databases all the time. The syntax for the kill step is this:
exec sp_killallprocessindb [TESTDB_NAME_HERE]
USE [master]
GO
/****** Object: StoredProcedure [dbo].[sp_killallprocessindb] Script Date: 12/02/2013 10:49:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create proc [dbo].[sp_killallprocessindb]
@dbname varchar(100)
as
if db_id(@dbname) = null
begin
print 'database dose not exist'
end
else
begin
declare @spid varchar(30)
declare tmpcursor cursor for
select 'kill ' + convert(varchar, spid) as spid
from master..sysprocesses
where db_name(dbid) = @dbname
and spid <> @@spid
and dbid <> 0
open tmpcursor
fetch next from tmpcursor
into @spid
while @@fetch_status = 0
begin
exec (@spid)
fetch next from tmpcursor
into @spid
end
close tmpcursor
deallocate tmpcursor
end
GO

I strongly recommend that you NEVER kill SPIDs. Killing spids is one of the worst things you can do at any time. It can leave multiple "zero rollback SPIDs" open that consume nearly a whole CPU each trying to rollback something that can't be rolled back. It's also a CONNECT item. The only work around for such a stuck SPID is to bounce the service and that's documented in the CONNECT item, as well.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Summer90 SSC-Dedicated Points: 32891 More actions · Answer 7

Interesting.... I got this Kill SP from this forum years ago... I have never seen anything to the effect of do not kill SPIDs... except system ones obviously. I guess I need to change my jobs for this.

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 8

Markus (12/2/2013)
Interesting.... I got this Kill SP from this forum years ago... I have never seen anything to the effect of do not kill SPIDs... except system ones obviously. I guess I need to change my jobs for this.

Here's the link to the CONNECT item. I've personnally experienced the "zero rollback" problem many times when killing a SPID. Unfortunately, MS closed the item as "could not reproduce".

https://connect.microsoft.com/SQLServer/feedback/details/433703/killed-rollback

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

dan-572483 SSCoach Points: 15870 More actions · Answer 9

Obviously the bext option is to identify any connections that exist and shut them off from the originating end. If that is not possible (in an evironment I manage that is sometimes not possible without disrupting access to other non-prod copies of the same application), I'll run the folowing in one batch, which kills the SPID and lauches the restore before the other process can jump back on.

KILL xxx --<SPID

GO

RESTORE DATABASE ......

I would be reluctant to kill a SPID of I didn't understand what it was and what is was doing.

Summer90 SSC-Dedicated Points: 32891 More actions · Answer 10

For test database refreshes the connections I kill are 99.9% idle as the app keeps connections eventhough no one is using the application.

sqlsurfing SSCarpal Tunnel Points: 4883 More actions · Answer 11

I do a lot of restores in my Dev and QA environments. They both have a wealth of little connection greedy web services running against them so I had to do something that ran quick enough to "get in" before they did. I also had the experience of my connection failing after I set a database to "single user" and having one of those little buggers grabbing the only connection available. That's why I do the multi-user thing immediately after the single-user thing. It hasn't failed me yet (as I knock on wood).

I re-read your original post, I would have thought setting it to multi-user post restore would be safest so no one "sneaks" in before the restore is initiated in between. Then i did see having it in a script, that helps 🙂

--------------------------------------------------
...0.05 points per day since registration... slowly crawl up to 1 pt per day hopefully 😀

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 12

sqlsurfing (12/4/2013)
I do a lot of restores in my Dev and QA environments. They both have a wealth of little connection greedy web services running against them so I had to do something that ran quick enough to "get in" before they did. I also had the experience of my connection failing after I set a database to "single user" and having one of those little buggers grabbing the only connection available. That's why I do the multi-user thing immediately after the single-user thing. It hasn't failed me yet (as I knock on wood).
I re-read your original post, I would have thought setting it to multi-user post restore would be safest so no one "sneaks" in before the restore is initiated in between. Then i did see having it in a script, that helps 🙂

Setting it to multi-user post restore is safe... provided that you don't lose your connection and then it's a nightmare.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)