?
Solved

Exclusive access could not be obtained because the database is in use.

Posted on 2006-06-06
25
Medium Priority
?
12,718 Views
Last Modified: 2012-05-05
[SQLSTATE 01000] (Message 3014)  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.

How can I fix this.
During restore process I would like the restore SQL script to have complete access but after the restore script is ran it should be no more exclusive use.

I have restore script in my schedulled task, which runs nightly. Most of the time there are no users but if there are, I want them not to have access and restore process should have access.

Please help me with TSQL.

Kind Regards,
0
Comment
Question by:pinaldave
  • 10
  • 9
  • 4
  • +1
25 Comments
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 16844715
>>I want them not to have access and restore process should have access.<<
Than you need to make sure you KILL them.  Actually not them but the spid they are using :)

You can get this manually by using sp_who and checking who has that database.  You can then do:
KILL xx         -- Where xx matches the spid in use.
0
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 200 total points
ID: 16844729
For an automatic approach, consider stuffing the results of sp_who into a temporary table and then using a cursor to execute KILL for all the users connected to the database.
0
 
LVL 21

Author Comment

by:pinaldave
ID: 16844924
Can anybody provide me script for that please?
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 13

Expert Comment

by:Atlanta_Mike
ID: 16845704
I have a better tip than all of these...

Right mouse click on the database, click All Tasks, click detach database, click the button that says clear. No click Cancel (you don't really want to detach the database).

This is how I do it... it's very easy as long as you don't accidentally detach the database. If you do, just reattach it.
0
 
LVL 21

Author Comment

by:pinaldave
ID: 16845710
I wrote a cursor but it shows the error that error at something.



CREATE TABLE #TmpWho
(spid INT, ecid INT, status VARCHAR(150), loginame VARCHAR(150), hostname VARCHAR(150), blk INT, dbname VARCHAR(150), cmd VARCHAR(150))

INSERT INTO #TmpWho
EXEC       sp_who

DECLARE @spid INT    
DECLARE @getspid CURSOR    

SET @getspid = CURSOR FOR    
      SELECT       spid
      FROM      #TmpWho
      WHERE       dbname = 'mydb'

OPEN @getspid    

FETCH NEXT FROM @getspid INTO @spid    

WHILE @@FETCH_STATUS = 0
BEGIN
 KILL @spid --SELECT @spid works fine here
FETCH NEXT FROM @getspid INTO @spid
END
CLOSE @getspid
DEALLOCATE @getspid

DROP TABLE #TmpWho

Error:
Server: Msg 170, Level 15, State 1, Line 24
Line 24: Incorrect syntax near '@spid'.



0
 
LVL 21

Author Comment

by:pinaldave
ID: 16845733
Thank you, Atlanta_Mike
Well I am requesting T-SQL. How can we do what you suggested in T-SQL please? The reason is I would like it auto mated.
0
 
LVL 13

Expert Comment

by:Atlanta_Mike
ID: 16845746
Ahhh... I see...

I have something... let me find it and I'll post.
0
 
LVL 13

Accepted Solution

by:
MikeWalsh earned 1600 total points
ID: 16845748
I would just do this this is how I write all of my restore scripts

ALTER DATABASE databaseName
SET SINGLE USER WITH ROLLBACK IMMEDIATE -- (This immediately interrupts all access and rollsback any work)

RESTORE .....

0
 
LVL 21

Author Comment

by:pinaldave
ID: 16845751
If someone can fix my cursor above will solve my problem too.
0
 
LVL 13

Expert Comment

by:MikeWalsh
ID: 16845752
Sorry like this:

ALTER DATABASE DatabaseName
SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
0
 
LVL 13

Expert Comment

by:MikeWalsh
ID: 16845761
Then just issue the RESTORE commands..

This way the user becomes in single_user by the process that is doign the restore, the restore runs and when the database is restored it is back in multi user mode.
0
 
LVL 13

Assisted Solution

by:Atlanta_Mike
Atlanta_Mike earned 200 total points
ID: 16845764
Even better... made mod to your script

CREATE TABLE #TmpWho
(spid INT, ecid INT, status VARCHAR(150), loginame VARCHAR(150), hostname VARCHAR(150), blk INT, dbname VARCHAR(150), cmd VARCHAR(150))

INSERT INTO #TmpWho
EXEC      sp_who

DECLARE @spid INT  
DECLARE @tString varchar(15)
DECLARE @getspid CURSOR    

SET @getspid = CURSOR FOR    
     SELECT      spid
     FROM     #TmpWho
     WHERE      dbname = 'mydb'

OPEN @getspid    

FETCH NEXT FROM @getspid INTO @spid    

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @tString = 'KILL ' + @spid
    EXEC(@tString)

FETCH NEXT FROM @getspid INTO @spid
END
CLOSE @getspid
DEALLOCATE @getspid

DROP TABLE #TmpWho
0
 
LVL 21

Author Comment

by:pinaldave
ID: 16845765
MikeWalsh,
That is great suggestion.
Would you pls suggest how can I make it for multi user after restore.

Regards,
0
 
LVL 13

Expert Comment

by:MikeWalsh
ID: 16845779
it becomes multi user after the restore by default.
0
 
LVL 13

Expert Comment

by:Atlanta_Mike
ID: 16845784
Mike I like that... I've never though of that one. I'll have to remember that one if I ever decide to move a database through a arestore process.

Nice
0
 
LVL 13

Expert Comment

by:MikeWalsh
ID: 16845789
Because it forgets that it was in Single User and reverts back to the state it was in at the time of the backup..

Give it a shot and you will see.
0
 
LVL 13

Expert Comment

by:MikeWalsh
ID: 16845797
The only downside is any users work is interrupted and stopped, so I use this with proper warning.. YOu can also lookup he ROLLBACK command and see options for delaying like ROLLBACK AFTER 5 (minutes).. but immediate is what I use
0
 
LVL 21

Author Comment

by:pinaldave
ID: 16845800
1) Thank you MikeWalsh, I will try it now and will get back to you.
2) Altanta_mike
The modification in your script which works fine is as follows:

CREATE TABLE #TmpWho
(spid INT, ecid INT, status VARCHAR(150), loginame VARCHAR(150), hostname VARCHAR(150), blk INT, dbname VARCHAR(150), cmd VARCHAR(150))

INSERT INTO #TmpWho
EXEC      sp_who

DECLARE @spid INT  
DECLARE @tString varchar(15)
DECLARE @getspid CURSOR    

SET @getspid = CURSOR FOR    
     SELECT      spid
     FROM     #TmpWho
     WHERE     dbname = 'mydb'

OPEN @getspid    

FETCH NEXT FROM @getspid INTO @spid    

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @tString = 'KILL ' + CAST(@spid AS VARCHAR(4))
    EXEC(@tString)

FETCH NEXT FROM @getspid INTO @spid
END
CLOSE @getspid
DEALLOCATE @getspid

DROP TABLE #TmpWho
0
 
LVL 21

Author Comment

by:pinaldave
ID: 16845811
MikeWalsh
 "The only downside is any users work is interrupted and stopped, "
That is perfect. There should not be any user on the DATABASE at that time. That is the requirement. So if they are, I need to kick them out any way.
0
 
LVL 21

Author Comment

by:pinaldave
ID: 16845821
Question?
Do I need to have GO between
ALTER DATABASE DatabaseName
SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO --is it required?
RESTORE
0
 
LVL 13

Expert Comment

by:MikeWalsh
ID: 16845824
I wouldn't even go through the KILL cursor.. There is no need to do that when you just rollback immediate, it does the same thing but a little more gracefully (both on you and the processes you are rolling back.. less coding and it forces a rollback of any non-committed transactions)
0
 
LVL 13

Expert Comment

by:MikeWalsh
ID: 16845831
It is not required, that is just how I execute it.. I have a script to restore several databases to a QA server and I separate each block of commands by a GO
0
 
LVL 21

Author Comment

by:pinaldave
ID: 16845847
Thanks MikeWalsh,

Reason I was asking cause everytime I put GO in my Schedulled Job it says... it does not like it. So sometime I put them in different Steps but I just wanted to have them in one step only.

Will report back in 10 min.
0
 
LVL 21

Author Comment

by:pinaldave
ID: 16845888
All Good Guys.
Thanks for everything.
MikeWalsh your answer is magical :)
0
 
LVL 13

Expert Comment

by:MikeWalsh
ID: 16845894
lol no problem, glad I could help.
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Suggested Courses

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question