Log Shipping: When you boss pulls the plug

So me and my boss were rooting around in the rack and he discovered that the secondary SQL server's cabling to its hard drive enclosure wasn't screwed down. He discovered this by knocking it free of course. Considering I installed them I thought he showed alot of restraint not throttling me. Anyway after we got done going over all the connections in the rack to make sure that was the only loose one we bounced the box and the RAID controller recovered with remarkable aplomb. So we went on with our chores.

This morning SQL was whining about the latency of the last backup applied to our secondary server. Basically log shipping was kaput. Running  the job by hand yielded errors, but not much in the way of info. So I did a log shipping report and manually ran a restore of the last log it had finished. I was operating on the hunch it failed to finish the last one before it was disconnected. I go the error "A previous restore operation was interrupted and did not complete processing on file". Quick Google sent me here. Apparently WITH RESTART didn't work for that guy but it worked like a charm for me. So I thought I was roll'in, but restore job failed on me again. Alot of good info to be had in msdb.log_shipping_monitor_history_detail. I also tried running the next log restore by hand and it got most of the way through before bitching about the trn being messed up. So I surmised that the copy prolly got cut off when the drives where disconnected (trn temp storage is on the same drive set). I went and got a new copy from the primary sql server and fired off the job again. Log shipping reports shows latency of last backup falling so I believe its catching up.

These boxes are both Win Srv '08 and SQL Srv '08 so you might be asking why we haven't gone to DB mirroring. I looked at it when I initially setup the servers, but the whitepapers where saying you could expect a 25% performance hit with synchronous mirroring. That was pretty unacceptable to us. Async would probably work fine but we don't want to buy the expensive version of SQL Srv. I guess I should bite the bullet at some point and setup mirroring just to test it.
comments powered by Disqus