SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Administration (2000)
 Shipping failed and DB in ''Suspect' mode
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Jake Shelton
Yak Posting Veteran

74 Posts

Posted - 03/24/2012 :  07:44:11  Show Profile  Reply with Quote
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

Edited by - Jake Shelton on 03/24/2012 08:20:40

tkizer
Almighty SQL Goddess

USA
37142 Posts

Posted - 03/25/2012 :  13:40:37  Show Profile  Visit tkizer's Homepage  Reply with Quote
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 Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

Jake Shelton
Yak Posting Veteran

74 Posts

Posted - 04/01/2012 :  05:40:42  Show Profile  Reply with Quote
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 Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog



You were quite right Tara, many thanks for your input!
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37142 Posts

Posted - 04/01/2012 :  14:21:52  Show Profile  Visit tkizer's Homepage  Reply with Quote


Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

Jake Shelton
Yak Posting Veteran

74 Posts

Posted - 04/08/2012 :  12:21:33  Show Profile  Reply with Quote
Just another thing, are there any good resources on shipping for 2K still out there?
Go to Top of Page

adamdale345
Starting Member

2 Posts

Posted - 04/14/2012 :  23:17:45  Show Profile  Reply with Quote
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.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000