October 29, 2007 at 5:29 am
We have a PRD db on our SAP Production server and a PRD db on our SAP Production Disaster Recovery server.
We employ log shipping to keep the DR box db up to date.
Over the weekend, log shipping stopped working and the DR PRD db is stuck in "Loading\Read-Only\Suspect" mode and we cannot do anything with it.
Is there a way to salvage this without trashing the DR PRD db, restoring over the top from a Production dump and then re-creating the log shipping process??
Any help you guys could give would be great!
Thanks,
Ben
October 30, 2007 at 10:02 am
I've never found a way out of this. However, before you fix the problem, you should investigate the cause. Look at all the logs for a clue to what caused the failure. If you don't find the error, you may rebuild only to find it happens again.
You may feel that rebuilding DR is your biggest problem right now. In reality the biggest problem may be finding what caused the DR server to go to suspect mode and ensuring it doesn't happen again.
I create log files (in the advanced tab of the jobs) for the various agents so errors are logged. Sometimes, the agent remains on the last command it tried to apply to the DR server (which caused the failure). In this case, you should be able to modify the agent job and add the output log files, re-run the agent and look at the output for a clue.
Possibly new code was rolled out that is affecting log shipping, or a new filegroup was added.
When you rebuild, you can leave log-shipping in place. You don't need to recreate the log shipping process. Disable the restore agent. Make sure production is still capturing logs. Make sure the job that cleans up the log files is set to NOT delete any logs. Restore from last backup with norecovery and turn the agents back on. Sometimes, I've had to manually apply some logs (with norecovery) to the DR server to get the restore agent job to catch up to the correct log file.
This ensures you still have everything captured from production in the event of prod failure.
November 1, 2007 at 11:26 am
It happened to me few times few years ago. Dont remember exactly what I did, but the way I would start troubleshooting this: check SQL Error Log. Any particular issues which help you. What was the last tran log applied? Try to apply the same log manually. Will it work? Then go to another log, etc...
November 1, 2007 at 11:35 pm
Looks like your DR database has got corrupted for some reasons. Was there a restart of the service when the log restore was going on or any changes to the sql server. check you error, agent and log shippingg log they should hint us something.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com
November 2, 2007 at 1:09 pm
Hi,
can you check the log shipping monitor?...see what is the status of backup delta, copy delta & load delta?...
check whether the space of drive to which TRN files (log shipping files) are copying from primary server are running out of space. check also whether the space of drive in which data files of secondary database are running out of space.
Thanks
Joseph
November 5, 2007 at 3:30 am
Ben Curry (10/29/2007)
We have a PRD db on our SAP Production server and a PRD db on our SAP Production Disaster Recovery server.We employ log shipping to keep the DR box db up to date.
Over the weekend, log shipping stopped working and the DR PRD db is stuck in "Loading\Read-Only\Suspect" mode and we cannot do anything with it.
Is there a way to salvage this without trashing the DR PRD db, restoring over the top from a Production dump and then re-creating the log shipping process??
Any help you guys could give would be great!
Thanks,
Ben
Ben, check your last successfully loaded log backup and the next one that failed using:
restore headeronly from disk = 'successful log backup name'
restore headeronly from disk = 'failed log backup name'
Compare LastLSN of your successful backup with the FirstLSN of the one that failed. If they are different - the only thing you can do is a full production DB backup and then re-starting your log shipping from scratch.
If they are identical, log-loading job should work fine unless log backup files are corrupt.
June 20, 2008 at 6:02 pm
I know you probably just restarted log shipping however I fixed this issue. Since there was not much info out there I thought I would leave the steps I used to fix this for anyone else that runs into this. What happened in my scenario is that power to our DR site was cut during a restore. When we brought everything back up our DR DB was in a state of Loading\Read-Only\Suspect
1.) First I stopped all of the log shipping jobs except the backup.
2.) I reset the DR DB status
sp_resetstatus 'DBname '
3.) Set the status to Emergency Mode
EXEC sp_configure 'allow', 1
RECONFIGURE WITH OVERRIDE
GO
UPDATE master..sysdatabases SET status = 32768 WHERE [name] = 'DBname'
4.) Rebuilt the log
DBCC REBUILD_LOG(DBname, 'file')
It then was in loading state
5.) Started the Log Shipping Restore Job until it caught up.
6.) Started all other Log Shipping Jobs.
Hopefully this will help someone else and save the time of restarting from scratch as this was a very painless process.
May 18, 2012 at 2:18 am
I've been having a lot of similar issues recently, and would like to know if this has worked for anyone on 2K.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply