Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
add comment

2 Answers

up vote 0 down vote accepted

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.

share|improve this answer
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 Aug 29 '12 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 Aug 30 '12 at 3:51
add comment

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.

share|improve this answer
    
Iam planning to implement the log compressions as I now feel it definitely should make a difference –  jchelad Aug 30 '12 at 15:46
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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