I'm implementing SQL Server Log Shipping. I don't have
Enterprise, so I have to create my own jobs. I'm in the
step where I'm executing a store procedure on the stanby
server to restore the database and I get an error stating
that the database state cannot be change because it is in
used by other users. The exact error is at the bottom of
this message.
I'm not sure why I'm getting this and how to fix it.
Nobody else has access to this server but myself and the
database is set to DBO USE ONLY. I closed out
Enterprise manager and SQL Query Analyzer on the standby
server and I still get the error. Please help. The
exact error is:
Executed as user: sa. RESTORE DATABASE is terminating
abnormally. [SQLSTATE 42000] (Error 3013) Exclusive
access could not be obtained because the database is in
use. [SQLSTATE 42000] (Error 3101) ALTER DATABASE
statement failed. [SQLSTATE 42000] (Error 5069) Database
state cannot be changed while other users are using the
database 'myDBName' [SQLSTATE 42000] (Error 5070)
sp_dboption command failed. [SQLSTATE 01000] (Error
15627). The step failed.Before you restore, you need to make sure there's no one in the database.
You will have to kill all user connections in the database, if there are
any. More info can be found at:
http://vyaskn.tripod.com/administration_faq.htm#q16
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Vic" <vduran@.specpro-inc.com> wrote in message
news:017e01c3d93f$dd6bc360$a401280a@.phx.gbl...
I'm implementing SQL Server Log Shipping. I don't have
Enterprise, so I have to create my own jobs. I'm in the
step where I'm executing a store procedure on the stanby
server to restore the database and I get an error stating
that the database state cannot be change because it is in
used by other users. The exact error is at the bottom of
this message.
I'm not sure why I'm getting this and how to fix it.
Nobody else has access to this server but myself and the
database is set to DBO USE ONLY. I closed out
Enterprise manager and SQL Query Analyzer on the standby
server and I still get the error. Please help. The
exact error is:
Executed as user: sa. RESTORE DATABASE is terminating
abnormally. [SQLSTATE 42000] (Error 3013) Exclusive
access could not be obtained because the database is in
use. [SQLSTATE 42000] (Error 3101) ALTER DATABASE
statement failed. [SQLSTATE 42000] (Error 5069) Database
state cannot be changed while other users are using the
database 'myDBName' [SQLSTATE 42000] (Error 5070)
sp_dboption command failed. [SQLSTATE 01000] (Error
15627). The step failed.sql
Showing posts with label step. Show all posts
Showing posts with label step. Show all posts
Monday, March 26, 2012
Log Shipping Restore Failing
**PLEASE help. Restore of Log Shippng is failing**
Job error is: sqlmaint.exe failed. [SQLSTATE 42000] (Error 22029). The step failed.
Detailed output of the job says: [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 4305: [Microsoft][ODBC SQL Server Driver][SQL Server]The log in this backup set begins at LSN 31433000001386500001, which is too late to apply to the database. An earlier lo
g backup that includes LSN 314320
00002000300001 can be restored.[Microsoft][ODBC SQL Server Driver][SQL Server]RESTORE LOG is terminating abnormally. (null) [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 4305: [Microsoft][ODBC SQL Server Driver][SQL Server]The log in this backup set be
gins at LSN 31433000001386500001, which is too late to apply to the database. An earlier log backup that includes LSN 31432000002000300001 can be restored. [Microsoft][ODBC SQL Server Driver][SQL Server]RESTORE LOG is terminating abnormally.(null) [Micros
oft SQL-DMO (ODBC SQLState: 42000)] Error 3201: [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot open backup device 'D:\SHIPPEDLOGS\LVBOEProd_tlog_200406272345.TRN'. Device error or device off-line. See the SQL Server error log for more details.[Micr
osoft][ODBC SQL Server Driver][SQL Server]RESTORE LOG is terminating abnormally.(null Loaded 0 files
**All of my shares/directories are correct. I have tried this at least a dozen times. It was successfull the 1st time the restore ran, but after that it has failed every time.
VC,
have a look at your chain of log backups and the log-shipping monitor. See
if you have any logs before 27th June 11:45pm which haven't been transfered
and/or restored. Manually restoring these 'missing' logs then restarting the
logshipping job should fix the problem.
HTH,
Paul Ibison
|||Paul, thank you very much for your reply. this is very important that i get this working ASAP. i have been fighting this day in and day out.
i re-did the log shipping and it's the same problem. do you have an email account that i can email the log to. it has been appending since the 1st restore. you can email me at v_c@.hotmail.com and i'll reply with the log.
there are no files earlier than the 1st file. in fact, this time around, it was successful.
|||VC,
no need to send over the log - it won't help me solve your issue.
I suspect that someone inadvertantly truncated the transaction log before
the first backup and hence the log sequence numbers were out of sync.
Alternatively it could be that the databases themselves were initially out
of sync. You could use LogExplorer (even the evaluation edn) to test if the
former is the case - to see if there are some records missing from the log.
Still, if it's running OK now then fine.
Regards,
Paul Ibison
|||VC,
no need to send over the log - it won't help me solve your issue.
I suspect that someone inadvertantly truncated the transaction log before
the first backup and hence the log sequence numbers were out of sync.
Alternatively it could be that the databases themselves were initially out
of sync. You could use LogExplorer (even the evaluation edn) to test if the
former is the case - to see if there are some records missing from the log.
Still, if it's running OK now then fine.
Regards,
Paul Ibison
|||i was doing this all night, like 2 in the morning. i doubt anyone was truncating logs. however, this is a good suspicion. any other ideas how the log could have gotten truncated?
|||i was doing this all night, like 2 in the morning. i doubt anyone was truncating logs. however, this is a good suspicion. any other ideas how the log could have gotten truncated?
|||2am - and I thought I worked too long hours ;-)
No other ideas really about how else your log got truncated.
I'd check out the transaction log using a 3rd party tool to try to make
sense of it.
Regards,
Paul Ibison
|||2am - and I thought I worked too long hours ;-)
No other ideas really about how else your log got truncated.
I'd check out the transaction log using a 3rd party tool to try to make
sense of it.
Regards,
Paul Ibison
Job error is: sqlmaint.exe failed. [SQLSTATE 42000] (Error 22029). The step failed.
Detailed output of the job says: [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 4305: [Microsoft][ODBC SQL Server Driver][SQL Server]The log in this backup set begins at LSN 31433000001386500001, which is too late to apply to the database. An earlier lo
g backup that includes LSN 314320
00002000300001 can be restored.[Microsoft][ODBC SQL Server Driver][SQL Server]RESTORE LOG is terminating abnormally. (null) [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 4305: [Microsoft][ODBC SQL Server Driver][SQL Server]The log in this backup set be
gins at LSN 31433000001386500001, which is too late to apply to the database. An earlier log backup that includes LSN 31432000002000300001 can be restored. [Microsoft][ODBC SQL Server Driver][SQL Server]RESTORE LOG is terminating abnormally.(null) [Micros
oft SQL-DMO (ODBC SQLState: 42000)] Error 3201: [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot open backup device 'D:\SHIPPEDLOGS\LVBOEProd_tlog_200406272345.TRN'. Device error or device off-line. See the SQL Server error log for more details.[Micr
osoft][ODBC SQL Server Driver][SQL Server]RESTORE LOG is terminating abnormally.(null Loaded 0 files
**All of my shares/directories are correct. I have tried this at least a dozen times. It was successfull the 1st time the restore ran, but after that it has failed every time.
VC,
have a look at your chain of log backups and the log-shipping monitor. See
if you have any logs before 27th June 11:45pm which haven't been transfered
and/or restored. Manually restoring these 'missing' logs then restarting the
logshipping job should fix the problem.
HTH,
Paul Ibison
|||Paul, thank you very much for your reply. this is very important that i get this working ASAP. i have been fighting this day in and day out.
i re-did the log shipping and it's the same problem. do you have an email account that i can email the log to. it has been appending since the 1st restore. you can email me at v_c@.hotmail.com and i'll reply with the log.
there are no files earlier than the 1st file. in fact, this time around, it was successful.
|||VC,
no need to send over the log - it won't help me solve your issue.
I suspect that someone inadvertantly truncated the transaction log before
the first backup and hence the log sequence numbers were out of sync.
Alternatively it could be that the databases themselves were initially out
of sync. You could use LogExplorer (even the evaluation edn) to test if the
former is the case - to see if there are some records missing from the log.
Still, if it's running OK now then fine.
Regards,
Paul Ibison
|||VC,
no need to send over the log - it won't help me solve your issue.
I suspect that someone inadvertantly truncated the transaction log before
the first backup and hence the log sequence numbers were out of sync.
Alternatively it could be that the databases themselves were initially out
of sync. You could use LogExplorer (even the evaluation edn) to test if the
former is the case - to see if there are some records missing from the log.
Still, if it's running OK now then fine.
Regards,
Paul Ibison
|||i was doing this all night, like 2 in the morning. i doubt anyone was truncating logs. however, this is a good suspicion. any other ideas how the log could have gotten truncated?
|||i was doing this all night, like 2 in the morning. i doubt anyone was truncating logs. however, this is a good suspicion. any other ideas how the log could have gotten truncated?
|||2am - and I thought I worked too long hours ;-)
No other ideas really about how else your log got truncated.
I'd check out the transaction log using a 3rd party tool to try to make
sense of it.
Regards,
Paul Ibison
|||2am - and I thought I worked too long hours ;-)
No other ideas really about how else your log got truncated.
I'd check out the transaction log using a 3rd party tool to try to make
sense of it.
Regards,
Paul Ibison
Subscribe to:
Comments (Atom)