Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Jake Shelton
Yak Posting Veteran
74 Posts |
 Posted - 2012-03-24 : 07:44:11
|
| Hi all, We have a DB whose 15-min interval log shipping recovery job has been failing for 2 weeks (wasn't me, I'm the whistle-blower, not the negligent!) and is now marked suspect. I spotted the following error no's:1) 3013(saw this in the Monitor)2) 22029(saw this in the restore job)Having googled both, I concluded that a particularly large log had been overtaken during the copy process by a subsequent, smaller one with a higher LSN, thus when SQL later tried to restore the earlier, larger one, the restore process failed as the log chain was now broken. Now, I presume the only way to fix is to: - Disable all three jobs (Backup/Copy/Restore); - Run SP_Resetstatus against the suspect secondary DB; - Take a FULL backup of the primary DB; - Put secondary DB into single-user mode; - Restore full backup to the secondary; - Put secondary DB back into multi-user mode; - Re-enable the three jobs.but I have some concerns in this regard...1) Have I got the above steps correct in content and sequence?2) The restore job isn't running, BUT when I ran a CheckDB with no_info/All_errors, SQL replied that the DB WAS in the middle of a restore!3) SP_Resetstatus has a pretty bad success rate, judging by comments on Google - given the above, what if this fails? Attach the backup file instead of restoring it?Thanks in advance guys,Jake |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2012-03-25 : 13:40:37
|
| I don't see how it's possible for a smaller tlog to be overtaken by a larger one. That's not how it works. Log shipping knows the order to do them and waits for the files to be copied. It doesn't proceed out of order.Did you check the error log as well as the event log on the restore server for issues?Just drop the suspect database and setup log shipping again. Doing all of those steps you mentioned is unnecessary, just start over.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
 |
|
|
Jake Shelton
Yak Posting Veteran
74 Posts |
Posted - 2012-04-01 : 05:40:42
|
quote:
Originally posted by tkizer
I don't see how it's possible for a smaller tlog to be overtaken by a larger one. That's not how it works. Log shipping knows the order to do them and waits for the files to be copied. It doesn't proceed out of order.Did you check the error log as well as the event log on the restore server for issues?Just drop the suspect database and setup log shipping again. Doing all of those steps you mentioned is unnecessary, just start over.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog
You were quite right Tara, many thanks for your input! |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
Jake Shelton
Yak Posting Veteran
74 Posts |
Posted - 2012-04-08 : 12:21:33
|
| Just another thing, are there any good resources on shipping for 2K still out there? |
|
|
|
adamdale345
Starting Member
2 Posts |
Posted - 2012-04-14 : 23:17:45
|
| You might want to google 'Log Shipping with SAP - Nildram'. Altough it says, the logshipping is with SAP you can follow the basic setup as explained in this doc. Just used this a couple of days to setup logshipping and it worked like a charm. |
|
|
|
|
|
|
|
|
Shipping failed and DB in ''Suspect' mode
Tara Kizer