July 6, 2008 at 11:08 pm
hi
i have shedule the restore log (.TRN) file of one database to another same database name of another machine. some days is work file. but yesterday i got the following error and fail the job
Exclusive access could not be obtained because the database is in use. [SQLSTATE 42000] (Error 3101) RESTORE LOG is terminating abnormally. [SQLSTATE 42000] (Error 3013). The step failed.
July 7, 2008 at 1:11 am
As the error message says, you cannot restore the log if the database is currerntly open by any users.
It would seem soneone (possibly even your own session) had the db open when you tried the restore.
Have a look at alter database - it has options to set the db into various options, including throwing out any current sessions to allow you to do a restore.
Mike John
July 7, 2008 at 7:00 am
[font="Verdana"]Hi,
If the database is in use, SQL Server won't be able to obtain exclusive access to the db for restoring tlogs hence you need to kill those connections to the db and then restore the log. Just put kill process as step 1 and restoring tlog as step 2. Check out the link below
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2825012&SiteID=1[/font]
Regards..Vidhya Sagar
SQL-Articles
July 8, 2008 at 1:30 am
thank for both to replay
the database is read only or stand by mode.
my need is restore the log file to user connected database . the user only read the database tables.
July 8, 2008 at 3:11 am
The database you are restoring to must have nobody using it. All users reading it will have to disconnect (or their connections be killed) before you can restore the log.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 8, 2008 at 9:46 am
Here the STEPS you should perform.
Kill destination connections. (run multiple times)
set Db in single_user mode.
RESTORE the LOG
set db to Multi_user
Check your Secondary Database consistency once a week for sure.
DBCC UPDATEUSAGE and DBCC CHECKDB
Maninder
www.dbanation.com
July 8, 2008 at 10:12 am
Mani Singh (7/8/2008)
Here the STEPS you should perform.Kill destination connections. (run multiple times)
set Db in single_user mode.
Or just set the database into single user mode with rollback_immediate
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply