Sql-server – Log shipping restore speed & index rebuild vs reorganize

index-tuninglog-shippingsql server

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?

Best Answer

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.

Related Question