0

Does the log shipping server restore a transaction log backup faster if the index on primary server are rebuilt or reorganized? On the log shipping server the restores are fast in phase one but seem to take a long time in the redo & undo phases. Like a tlog backup that took a few minutes on the primary is taking 1-4 hours to restore. I am using Ola Hallogren's routines on the primary server with the default 5% reorg & 30% rebuild levels but I'm wondering if switching to only rebuild and increasing the % will help speed up the restores. I'm not really concerned with how long the defrag takes on the primary but how fast they can be restored on the secondary. Does bulk-logged help vs full recovery?

  • Is your LS secondary in Standby so people can read from it? – Erik Darling Apr 5 '17 at 15:35
  • Yes it's in standby. – Dave M. Apr 5 '17 at 15:41
  • That can increase the restore time by quite a bit. You may also want to back off a bit on that aggressive index maintenance ( brentozar.com/archive/2016/01/… ), or at least use the @Delay param to spread out index maintenance over more log backups since you don't care how long it takes on the primary. – Erik Darling Apr 5 '17 at 15:46
  • Thanks will I test only flipping to standby after the nightly restores are done. – Dave M. Apr 5 '17 at 16:11
3

If you want to improve the speed of your tlog restores, you will want to look into the bulk-logged recovery model. I would never suggest running a database under this recovery model exclusively, though switching to it during your maintenance window will help significantly in your case.

The key here is to be very explicit on the approach you take when switching recovery models. From the source, anytime you wish to utilize the bulk-logged recovery model, you should follow these steps:

  1. Before you switch to the bulk-logged recovery model, take a tlog backup
  2. Switch the recovery model of the db to bulk-logged
  3. Perform your minimally logged operations (e.g. index maintenance, etc.)
  4. Switch the database back to the full recovery model
  5. Immediately take another tlog backup

Here's a handy little infographic that outlines the approach:

Proper TLog Backups with Bulk-Logged Recovery Model

In an ideal situation, MS also encourages the following:

  • Users are currently not allowed in the database, or their activity is kept to a minimum.
  • All modifications made during bulk processing are recoverable without depending on taking a log backup; for example, by re-running the bulk processes.

These are not hard-line requirements, but good practice recommendations and the reason behind them is that all activity that completes during the period where the database is in bulk-logged mode (once a minimally logged operation occurs) will either have to be redone if you roll back to the tlog backup taken in step 1 or will be committed if you restore the tlog backup taken in step 5. The yellow window identified in the infographic is an all-or-nothing sort of process, and you are unable to do any point-in-time recovery during that time-frame. The moment you convert the database back to the full recovery model and take another tlog backup is the moment you can once again utilize point-in-time recovery.

Finally, there are some restrictions to using the bulk-logged recovery model, such as considerations for databases with read-only filegroups and online recovery scenarios, so do some testing to make sure you're not causing yourself more headache than necessary.

I've used the bulk-logged recovery model for years and it's quite helpful so long as you understand how to use it properly.

  • I've also used BULK_LOGGED with log shipping. We re-partitioned a few really large tables by using CREATE INDEX...WITH (ONLINE=ON, DROP_EXISTING=ON) ON NewPartScheme(PartCol1). BULK_LOGGED was able to minimally log that operation (which took several hours) and log shipping only 'fell behind' during the duration fo the minimally logged index build. Afterwards, set it to FULL and, voila, log shipping caught right up. – Kris Gruttemeyer Apr 6 '17 at 18:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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