1

I am planning an index rebuild, however my logs are prone to huge overblown sizes.

In fact I was leaning towards doing an intelligent re-indexing, but since the fragmentation levels are greater than 30% I thought I should do a rebuild.

My databases use the full recovery model. Should I change the recovery model to Bulk logged before I do the rebuild later this weekend when there are less transactions happening.

Log shipping is enabled as a DR solution in these databases.

What do you suggest if my latency is quite high? Is there a better DR solution that I should look into?

Improve this question

2 Answers 2

Reset to default
0

I think this is kind of the same question as the other one you just posted. there are a couple of new points you address:

Bulk logged mode doesn't make the logs shipped over the wire smaller. It just makes writing to the local log file less IO intensive. Once the log has to be backupped, alle changed pages haved to be included in the backup. The end result is that your trans log backup will be just as big.

If you already have High latency, changing to mirroring will not change that. the only way to spread the load is: stop doing rebuild and change to a spread reorganize strategy (as I mentioned in your other question.)

Or as somebody else there mentioned, start doing more frequent log backups and ship smaller log backups in a higher frequency.

Just based on what you are telling us now. (the high latency) I personally would not advise to change to mirroring. But the choice between mirroring and log shipping should be based on many more items then only latency.

Improve this answer
2
  • 1
    It is not necessarily correct to state that switching to mirroring will have no effect on sending the logs down the wire. For 2008 and up, transaction log compression is employed for mirroring.
    – briancarrig
    Commented Aug 29, 2012 at 20:23
  • You can also compress your log backups while logshipping. (only an Enterprise feature in sql 2008, in R2 it's also standard) Furthermore, TS wants to solve the problem of a peak high load during Index rebuild. Changing to mirroring (even async) will likely maximize the peak on the wire since the transactions will be send over the wire instantly.
    – Edward Dortland
    Commented Aug 30, 2012 at 3:51
0

Are you compressing your log backups? I recommend that you do so. It is an available option in Standard Edition for 2008 R2 and EE in 2008 R1. I agree that you should be considering more than just latency when deciding on the DR solution.

Improve this answer
1
  • Iam planning to implement the log compressions as I now feel it definitely should make a difference
    – jchelad
    Commented Aug 30, 2012 at 15:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.