Restore database fails because database is in use.

I'm a newbie so please be gentle.

In attempting to run a restore I get the following error message:
"Exclusive access could not be obtained because the database is in
use." However, it doesn't appear that there are any connections to the
database. At the suggestion of another thread, I ran sp_who2 and there
are no connections to the database in question. I've been running the
same restore for months and all has been well, until yesterday...

I wrote an .asp page (below) to test connectivity to this database.
The vb code ran successfully and soon thereafter, my nightly restore
failed. Task manager does no show that the asp page is active.

Lastly, I did add a user ("jbtest") as well as change the option to use
either Windows authentication or SQL Server authentication in
Enterprise Manager.

Any ideas would be greatly appreciated.

Thanks.

<%@. Language=VBScript %
<%
companyName = "agemni"

Set cnn = Server.CreateObject("ADODB.Connection")
cnn.ConnectionString = "DRIVER=SQL
Server;SERVER=SHEELA-NA-GIG;UID=jbtest;PWD=test1;APP=Microsoft
Development Environment;WSID=SHEELA-NA-GIG;DATABASE=" & companyName &
";Trusted_Connection=No"

cnn.ConnectionTimeout = 300
cnn.CommandTimeout = 300
cnn.Open

Set RS = Server.CreateObject("ADODB.Recordset")
strSQL = "SELECT * FROM Preferences"
RS.Open strSQL, cnn

Response.Write "connection established with the <b>" & RS("Company
Name") & "</b> database. They are a <b>" & RS("street") & "</b>
customer. "

RS.Close
Set RS = Nothing
%<jbmccluskey@.satcountry.com> wrote in message
news:1112117455.317127.10740@.g14g2000cwa.googlegro ups.com...
> I'm a newbie so please be gentle.
> In attempting to run a restore I get the following error message:
> "Exclusive access could not be obtained because the database is in
> use." However, it doesn't appear that there are any connections to the
> database. At the suggestion of another thread, I ran sp_who2 and there
> are no connections to the database in question. I've been running the
> same restore for months and all has been well, until yesterday...
> I wrote an .asp page (below) to test connectivity to this database.
> The vb code ran successfully and soon thereafter, my nightly restore
> failed. Task manager does no show that the asp page is active.
> Lastly, I did add a user ("jbtest") as well as change the option to use
> either Windows authentication or SQL Server authentication in
> Enterprise Manager.
> Any ideas would be greatly appreciated.
> Thanks.

<snip
Probably the most reliable solution is to use ALTER DATABASE to get rid of
any open connections, wherever they're from:

alter database MyDB set restricted_user with rollback immediate

See Books Online for the other state options (single_user, offline etc.) -
one of them might suit your needs better.

You don't mention how you're running the restore, but if it's via a
scheduled job, you should also check that the selected database for the
restore step is set to master, not to the database you're restoring,
otherwise the job can block itself.

Simon|||Thanks for the reply Simon. The problem was that I was trying to run
the RESTORE in the database itself as opposed to running it under
MASTER.

JB

Restore Database failed - Excusive Access could not be obtained.

I have a job that runs at 4:00 AM, which refreshes(RESTORE
DATABASE) the read only copy of the database. Last night
the job has failed and the message I got is, Exculsive
access could not be obtained because the database is in
use and the acutal message is as following -
Executed as user: UserNameHere. ...Executing... DTSRun
OnStart: DTSStep_DTSExecuteSQLTask_1 DTSRun OnFinish:
DTSStep_DTSExecuteSQLTask_1 DTSRun OnStart:
DTSStep_DTSExecuteSQLTask_3 DTSRun OnFinish:
DTSStep_DTSExecuteSQLTask_3 DTSRun OnStart:
DTSStep_DTSExecuteSQLTask_5 DTSRun OnError:
DTSStep_DTSExecuteSQLTask_5, Error = -2147217900
(80040E14) Error string: RESTORE DATABASE is
terminating abnormally. Error source: Microsoft OLE
DB Provider for SQL Server Help file: Help
context: 0 Error Detail Records: Error: -
2147217900 (80040E14); Provider Error: 3013 (BC5)
Error string: RESTORE DATABASE is terminating
abnormally. Error source: Microsoft OLE DB Provider
for SQL Server Help file: Help context:
0 Error: -2147217900 (80040E14); Provider Error:
3101 (C1D) Error string: Exclusive access could not
be obtained because the database is in use. Error
source: Microsoft OLE DB Provider for SQL Serv. The step
failed.
Is there any option in RESTORE DATABASE that kills all the
logged in users and restores a fresh copy of the database.
Otherwise do I have to kill all the logged in users by
using sysprocesses. Also the error message doesn't show
the user(s) name, is there any way I can find it out now.
Going forward I will start running profiler but is there a
way figure out who logged in last night.
Thanks,
Narendra.Hi,
There is no option in restore command to kill all the users connected to
database. Instead you can use the below
statements along with your Restore database command and schedule this as a
task.
use master
go
declare @.x varchar(255)
select @.x = @.x + " kill " + convert(varchar(5), spid)
from master.dbo.sysprocesses
where dbid = db_id ('dbname')
exec (@.x)
go
restore database command
Thanks
Hari
MCDBA
"Narendra" <nnarendra@.yahoo.com> wrote in message
news:0e1901c3a3b2$1ea23940$a601280a@.phx.gbl...
> I have a job that runs at 4:00 AM, which refreshes(RESTORE
> DATABASE) the read only copy of the database. Last night
> the job has failed and the message I got is, Exculsive
> access could not be obtained because the database is in
> use and the acutal message is as following -
> Executed as user: UserNameHere. ...Executing... DTSRun
> OnStart: DTSStep_DTSExecuteSQLTask_1 DTSRun OnFinish:
> DTSStep_DTSExecuteSQLTask_1 DTSRun OnStart:
> DTSStep_DTSExecuteSQLTask_3 DTSRun OnFinish:
> DTSStep_DTSExecuteSQLTask_3 DTSRun OnStart:
> DTSStep_DTSExecuteSQLTask_5 DTSRun OnError:
> DTSStep_DTSExecuteSQLTask_5, Error = -2147217900
> (80040E14) Error string: RESTORE DATABASE is
> terminating abnormally. Error source: Microsoft OLE
> DB Provider for SQL Server Help file: Help
> context: 0 Error Detail Records: Error: -
> 2147217900 (80040E14); Provider Error: 3013 (BC5)
> Error string: RESTORE DATABASE is terminating
> abnormally. Error source: Microsoft OLE DB Provider
> for SQL Server Help file: Help context:
> 0 Error: -2147217900 (80040E14); Provider Error:
> 3101 (C1D) Error string: Exclusive access could not
> be obtained because the database is in use. Error
> source: Microsoft OLE DB Provider for SQL Serv. The step
> failed.
>
> Is there any option in RESTORE DATABASE that kills all the
> logged in users and restores a fresh copy of the database.
> Otherwise do I have to kill all the logged in users by
> using sysprocesses. Also the error message doesn't show
> the user(s) name, is there any way I can find it out now.
> Going forward I will start running profiler but is there a
> way figure out who logged in last night.
> Thanks,
> Narendra.|||Instead of this, you could just set the database to
SINGLE_USER MODE for the duration of the restore and then
set it back to MULTI_USER when done. Using the WITH
ROLLBACK IMMEDIATE when setting to SINGLE_USER MODE
alter database AHP set SINGLE_USER with rollback immediate
restore database command
alter database AHP set MULTI_USER
>--Original Message--
>Hi,
>There is no option in restore command to kill all the
users connected to
>database. Instead you can use the below
>statements along with your Restore database command and
schedule this as a
>task.
>use master
>go
>declare @.x varchar(255)
>select @.x = @.x + " kill " + convert(varchar(5), spid)
>from master.dbo.sysprocesses
>where dbid = db_id ('dbname')
>exec (@.x)
>go
>restore database command
>Thanks
>Hari
>MCDBA
>
>"Narendra" <nnarendra@.yahoo.com> wrote in message
>news:0e1901c3a3b2$1ea23940$a601280a@.phx.gbl...
>> I have a job that runs at 4:00 AM, which refreshes
(RESTORE
>> DATABASE) the read only copy of the database. Last night
>> the job has failed and the message I got is, Exculsive
>> access could not be obtained because the database is in
>> use and the acutal message is as following -
>> Executed as user: UserNameHere. ...Executing... DTSRun
>> OnStart: DTSStep_DTSExecuteSQLTask_1 DTSRun OnFinish:
>> DTSStep_DTSExecuteSQLTask_1 DTSRun OnStart:
>> DTSStep_DTSExecuteSQLTask_3 DTSRun OnFinish:
>> DTSStep_DTSExecuteSQLTask_3 DTSRun OnStart:
>> DTSStep_DTSExecuteSQLTask_5 DTSRun OnError:
>> DTSStep_DTSExecuteSQLTask_5, Error = -2147217900
>> (80040E14) Error string: RESTORE DATABASE is
>> terminating abnormally. Error source: Microsoft
OLE
>> DB Provider for SQL Server Help file: Help
>> context: 0 Error Detail Records: Error: -
>> 2147217900 (80040E14); Provider Error: 3013 (BC5)
>> Error string: RESTORE DATABASE is terminating
>> abnormally. Error source: Microsoft OLE DB
Provider
>> for SQL Server Help file: Help context:
>> 0 Error: -2147217900 (80040E14); Provider
Error:
>> 3101 (C1D) Error string: Exclusive access could
not
>> be obtained because the database is in use. Error
>> source: Microsoft OLE DB Provider for SQL Serv. The
step
>> failed.
>>
>> Is there any option in RESTORE DATABASE that kills all
the
>> logged in users and restores a fresh copy of the
database.
>> Otherwise do I have to kill all the logged in users by
>> using sysprocesses. Also the error message doesn't show
>> the user(s) name, is there any way I can find it out
now.
>> Going forward I will start running profiler but is
there a
>> way figure out who logged in last night.
>> Thanks,
>> Narendra.
>
>.
>|||Van/Hari,
Thanks for the information. I will follow the suggestions.
Narendra.
>--Original Message--
>Instead of this, you could just set the database to
>SINGLE_USER MODE for the duration of the restore and then
>set it back to MULTI_USER when done. Using the WITH
>ROLLBACK IMMEDIATE when setting to SINGLE_USER MODE
>alter database AHP set SINGLE_USER with rollback immediate
>restore database command
>alter database AHP set MULTI_USER
>>--Original Message--
>>Hi,
>>There is no option in restore command to kill all the
>users connected to
>>database. Instead you can use the below
>>statements along with your Restore database command and
>schedule this as a
>>task.
>>use master
>>go
>>declare @.x varchar(255)
>>select @.x = @.x + " kill " + convert(varchar(5), spid)
>>from master.dbo.sysprocesses
>>where dbid = db_id ('dbname')
>>exec (@.x)
>>go
>>restore database command
>>Thanks
>>Hari
>>MCDBA
>>
>>"Narendra" <nnarendra@.yahoo.com> wrote in message
>>news:0e1901c3a3b2$1ea23940$a601280a@.phx.gbl...
>> I have a job that runs at 4:00 AM, which refreshes
>(RESTORE
>> DATABASE) the read only copy of the database. Last
night
>> the job has failed and the message I got is, Exculsive
>> access could not be obtained because the database is in
>> use and the acutal message is as following -
>> Executed as user: UserNameHere. ...Executing...
DTSRun
>> OnStart: DTSStep_DTSExecuteSQLTask_1 DTSRun
OnFinish:
>> DTSStep_DTSExecuteSQLTask_1 DTSRun OnStart:
>> DTSStep_DTSExecuteSQLTask_3 DTSRun OnFinish:
>> DTSStep_DTSExecuteSQLTask_3 DTSRun OnStart:
>> DTSStep_DTSExecuteSQLTask_5 DTSRun OnError:
>> DTSStep_DTSExecuteSQLTask_5, Error = -2147217900
>> (80040E14) Error string: RESTORE DATABASE is
>> terminating abnormally. Error source: Microsoft
>OLE
>> DB Provider for SQL Server Help file: Help
>> context: 0 Error Detail Records: Error: -
>> 2147217900 (80040E14); Provider Error: 3013 (BC5)
>> Error string: RESTORE DATABASE is terminating
>> abnormally. Error source: Microsoft OLE DB
>Provider
>> for SQL Server Help file: Help context:
>> 0 Error: -2147217900 (80040E14); Provider
>Error:
>> 3101 (C1D) Error string: Exclusive access could
>not
>> be obtained because the database is in use. Error
>> source: Microsoft OLE DB Provider for SQL Serv. The
>step
>> failed.
>>
>> Is there any option in RESTORE DATABASE that kills all
>the
>> logged in users and restores a fresh copy of the
>database.
>> Otherwise do I have to kill all the logged in users by
>> using sysprocesses. Also the error message doesn't show
>> the user(s) name, is there any way I can find it out
>now.
>> Going forward I will start running profiler but is
>there a
>> way figure out who logged in last night.
>> Thanks,
>> Narendra.
>>
>>.
>.
>|||FYI...the WITH ROLLBACK IMMEDIATE will rollback anything
that any users connected may have been in the middle of
doing. But that should be a little cleaner than a KILL
command.
Van Jones
MCDBA, MCSE, MCSA, MCAD
>--Original Message--
>Instead of this, you could just set the database to
>SINGLE_USER MODE for the duration of the restore and then
>set it back to MULTI_USER when done. Using the WITH
>ROLLBACK IMMEDIATE when setting to SINGLE_USER MODE
>alter database AHP set SINGLE_USER with rollback immediate
>restore database command
>alter database AHP set MULTI_USER
>>--Original Message--
>>Hi,
>>There is no option in restore command to kill all the
>users connected to
>>database. Instead you can use the below
>>statements along with your Restore database command and
>schedule this as a
>>task.
>>use master
>>go
>>declare @.x varchar(255)
>>select @.x = @.x + " kill " + convert(varchar(5), spid)
>>from master.dbo.sysprocesses
>>where dbid = db_id ('dbname')
>>exec (@.x)
>>go
>>restore database command
>>Thanks
>>Hari
>>MCDBA
>>
>>"Narendra" <nnarendra@.yahoo.com> wrote in message
>>news:0e1901c3a3b2$1ea23940$a601280a@.phx.gbl...
>> I have a job that runs at 4:00 AM, which refreshes
>(RESTORE
>> DATABASE) the read only copy of the database. Last
night
>> the job has failed and the message I got is, Exculsive
>> access could not be obtained because the database is in
>> use and the acutal message is as following -
>> Executed as user: UserNameHere. ...Executing...
DTSRun
>> OnStart: DTSStep_DTSExecuteSQLTask_1 DTSRun
OnFinish:
>> DTSStep_DTSExecuteSQLTask_1 DTSRun OnStart:
>> DTSStep_DTSExecuteSQLTask_3 DTSRun OnFinish:
>> DTSStep_DTSExecuteSQLTask_3 DTSRun OnStart:
>> DTSStep_DTSExecuteSQLTask_5 DTSRun OnError:
>> DTSStep_DTSExecuteSQLTask_5, Error = -2147217900
>> (80040E14) Error string: RESTORE DATABASE is
>> terminating abnormally. Error source: Microsoft
>OLE
>> DB Provider for SQL Server Help file: Help
>> context: 0 Error Detail Records: Error: -
>> 2147217900 (80040E14); Provider Error: 3013 (BC5)
>> Error string: RESTORE DATABASE is terminating
>> abnormally. Error source: Microsoft OLE DB
>Provider
>> for SQL Server Help file: Help context:
>> 0 Error: -2147217900 (80040E14); Provider
>Error:
>> 3101 (C1D) Error string: Exclusive access could
>not
>> be obtained because the database is in use. Error
>> source: Microsoft OLE DB Provider for SQL Serv. The
>step
>> failed.
>>
>> Is there any option in RESTORE DATABASE that kills all
>the
>> logged in users and restores a fresh copy of the
>database.
>> Otherwise do I have to kill all the logged in users by
>> using sysprocesses. Also the error message doesn't show
>> the user(s) name, is there any way I can find it out
>now.
>> Going forward I will start running profiler but is
>there a
>> way figure out who logged in last night.
>> Thanks,
>> Narendra.
>>
>>.
>.
>