Slow Transaction Log restore in standby mode (Log Shipping)

I am always saying that crisis situations
are good for your knowledge.

They can contribute to your
understating how things are working.




Consider the following, you have Log Shipping configured in Standby mode and
you noticed that sometimes the 'Restoring…' takes long time than usual.

So, why restoring 100-200 MB transaction log takes 30-40 minutes on the
secondary server in Logged Shipped database?

In short, due to long running transactions and Standby mode.

Now more in details:

First we need to understand what Standby means and how it works.
When you restore transaction log(s) you have 3 options:

1) WITH RECOVERY
2) WITH NORECOVERY
3) WITH STANDBY

In Log shipping, you can choose between options 2 or 3.

When restoring transaction log(s) with option "RECOVERY", the SQL Server will
undo All uncommitted transactions, the database will be accessible but you will not
be able to restore more transaction logs.

When restoring transaction log(s) with option "NORECOVERY", the SQL Server
will leave the uncommitted transactions as is and you will not be able to access the
database.

When restoring transaction log(s) with the "STANDBY" option, the SQL Server will
go over the transaction log and save all uncommitted transactions to a standby data
file with .tuf extension. (TUF stands for Transaction Undo File). The database will be
accessible and in read-only mode.

You can ask why it needs the tuf file. Well, when you restore with STANDBY mode
the database stays accessible, right? Meaning you can connect to it and view the
data  (just read, because it in Read-Only mode).
But how it possible, what about uncommitted transactions?

You will not see the uncommitted data even if you read the data in
"READ Uncommitted Isolation Level". Thus is, because the SQL Server
performs UNDO for all uncommitted transactions and saves their data to
the tuf file. It simply saves the entire content of the pages that it had to undo
and in some cases the SQL Server can save not only the data rows but the all pages.

The tuf file will be used when you restore the next transaction log file.
First, the SQL Server will REDO all the uncommitted transactions that in the tuf file
(apply the content of the undo file to the data files) and only then continue to restore
the next transaction log.

Important note: the tuf file will contain the uncommitted transactions not just from
the latest transaction log but uncommitted transactions in all previous transaction
logs that have been restored.

Now after we understand how the STANDBY option works, let`s see what happening
when you rebuild indexes in the primary server and suppose that the rebuild of some
table spreads over more than one backed up transaction log. For example:
the rebuild takes 20 minutes and you backup the transaction log every 5 minutes.

I think you already got it. The secondary server upon restoring the first transaction
log that contains the start of the index rebuild will save the uncommitted data to
the tuf file, in our case, if we rebuild clustered index, it will save a lots of data to
the tuf file. And upon restoring the next transaction log, it will redo all the
uncommitted transactions saved in the tuf file and continue the restoring, but in
our case the index operation is not finished in the second transaction log file,
so SQL Server will again UNDO the rebuild index and save it to the standby file
(tuf file), now it will contain uncommitted data of the 2 transaction logs. And this
process continues and continues until the last transaction log with index rebuild
data will be restored.

I hope it helped you to understand the reason for the slow restore of the transaction
logs in the secondary logged shipped database.

Thanks for reading.