Progress Updates

A colleague not too long ago introduced me to sys.dm_exec_requests which has a percent_complete column and has significantly changed our view of longer processes and lends a degree of confidence where before we would be sitting there wondering if it was time to reboot.

 I have, however, encountered a notable problem, on a reasonable sized database restore (this will depend on infrastructure but I've seen this above 100Gb) progress sits at 0% for what seems like at eternity.

In fact the timing before progress starts getting logged has seemed to be to be about how long it would take to create the file and space required and do almost all of the restore process.

I have now discovered more detail that might be useful next time I encounter the "problem" because it appears that recovery is split into 5 phases :

1. Copy of data from the backup to database pages.
2. Discovery 
3. Analysis 
4. Redo 
5. Undo 

where on Enterprise edition (2005+) the database is available to users from the end of phase 4 (using Fast Recovery) and on old versions & other editions the database is available after the undo phase (more on that here : fast-recovery-with-sql-server .

I think I will find that SQL is reporting 0% while the data is being copied back to the data files and given that this will be happening as a transaction it remains 0% until completed which explaims the behaviour being seen.

It seems that the view sys.dm_tran_database_transactions will give further detail during  the undo phase and where the database_transaction_log_bytes_reserved column will show decreasing volumes for the given database.

Further, where there are several transactions to undo the database_transaction_next_undo_lsn column will provide additional detail.