2

We all know that index rebuilding creates lot of log growth..so what changes would you consider to your logshipping side to accomodate this log growth?

  1. Do we increase the frequency of the logbackups?
  2. ship the logs to an external storage such as SAN?

Please give your valuable suggestions.

3 Answers 3

2

My first suggestion would be to use intelligent re-indexing rather than just blindly re-indexing everything. This will help to minimise the impact on the logs un-necessarily. Ola Hallengren's offers one such solution. See http://ola.hallengren.com/

External storage on a SAN that uses either cheap discs (SATA), or dedupe technology, makes it more affordable than expensive SAN discs.

Increasing the frequency of the log backups,and the copy and restore operations, makes sense to me. Ensure the maintenance is out of hours so that any learning curve about capacity minimises any pain.

1
  • Peter, how would changing to cheaper slower storage help solving peak load of index Rebuilds? More likely you want faster more expensive storage to solve any logshipping performance issues? Aug 30, 2012 at 4:02
1

If you incure heavy log overhead because of rebuilds I would advise a strategy where you would change to reorganize instead of rebuild.

Yes it's true on higher fragmentation reorganize is less efficient then rebuild but the great thing about reorganize is that you can stop it, and next time when yu start it it will continue where it left.

So you could start scheduling regular intervals of reorganizing spread across the day/night so you equalize the load instead of creating huge peeks.

1
  • technically, a reorganize doesn't continue where it left. But say that it has done the first 10% of the pages. The next time it will start, it will fly super fast past this first 10% since it doesn.t need to rearange to pages again. Aug 29, 2012 at 18:42
0

Re-building Index generates lesser log as compared to Re-organize.Whatever be the case logs will be generated and has to be managed by log backups.

  • First of all make sure you have sufficient disk space where the log
    backups and the copy of log backups go. This is for sudden burst of log growth.

  • If you have configured log backups,copy n restore jobs to run every
    15 minutes then its fine and will be able to take the prevent any log growth.

During this the restore will take time and logshipping alerts will be generated. You can disable the alerts or increase the threshold for this. And make sure the index maintenance is done on weekends.

1
  • Of course, if the normal timing of your jobs is every 15 minutes, then to prevent log growth you probably would need to make them more frequent.
    – RDFozz
    Jul 7, 2017 at 15:38

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.