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.
>>
>>.
>.
>