Sql-server – Is it possible to estimate the restore progress in SQL Server 2014 from pages read/written

log-shippingrestoresql serversql server 2014

For context – SQL 2014 EE, we're doing tran log backups from our main production box and restoring to a R/O instance. The log is usually around 50GB.

So I'm familiar with the structure of SQL Server restores and know the percentage complete estimate only refers to the data copy phase. For us, this generally runs pretty quickly but the redo phase is where the time is taken.

From sp_whoisactive I can easily get pages read/written by the log restore. What I'm wondering is if I know the size of the log (which I do), can I estimate the progress of the restore from those numbers?

I intend to give is a quick go, but if someone has tried this already and it's a fools errand I'll skip it!

Best Answer

Look at percent_complete and estimated_completion_time in sys.dm_exec_requests. If it is in fact populated for restoring logs (I have only tested full database restores), it is going to be based on the same math you want to try and calculate manually.

SELECT percent_complete, estimated_completion_time
  FROM sys.dm_exec_requests
  WHERE session_id = <SPID running the backup>;

Obviously neither approach can predict pending concurrency / I/O interruptions, so it is nowhere near any sort of guarantee - just a ballpark guess. It also won't be possible to predict how much I/O will be required for any undo/redo operations, since these are not directly related to the size of the log.

Related Question