How SQL Server apply the log during recovery or logshipping

Question

Hi,

We have a log shipping setup between 2 machines and it is taking a long time to restore the logs (i.e. log shipping restore process is slow) and we are wondering on the logic of SQL Server log restore. SQL Server stores the before and after image of Page as well as store the actual operation/statement that is done on the database so during restore do it have to apply all the logical operations on the secondary database i.e. for example if we have 2 identical hardware machines and update operation on 1 table(having 6 indexes) takes 10 min on machine A (assuming only 1 operation going on at this time), and we do log backup then Will it takes 10 min on secondary machine B to apply that log?

Thanks

--Harvinder

Answer 1

There could be multiple reasons why the restore of log backup on secondary is slow. below article has some information on why it could be slow

http://www.sqlskills.com/blogs/paul/why-could-restoring-a-log-shipping-log-backup-be-slow/

Answer 2

We have a log shipping setup between 2 machines and it is taking a long time to restore the logs (i.e. log shipping restore process is slow) and we are wondering on the logic of SQL Server log restore. SQL Server stores the before and after image of Page as well as store the actual operation/statement that is done on the database so during restore do it have to apply all the logical operations on the secondary database i.e. for example if we have 2 identical hardware machines and update operation on 1 table(having 6 indexes) takes 10 min on machine A (assuming only 1 operation going on at this time), and we do log backup then Will it takes 10 min on secondary machine B to apply that log?

>>Hi there is no logic behind of restore for logshipping where it is similar to Normal restore only incase if we try for restoring like full, differential and transactions or full with N.... Number of t-logs backups,as we know that Logshipping is one of the part of HA .

the other one which you said->Incase if you have the same Identical  H/W it doesn't mean that the time it will Be equal for restore operation in terms of time->No its not. because it varies different factors as well-so

check with your storage,Network and OS team to just verify everything is normal during your worried time.

Also check 1.what other activities runs apart from restore at the instance level
                      2.Check errorlogs is there any error related disk/storage or any other issues.
                      3.check event logs
                      4.verify any recent modification done
                      5.Any time difference .

ref other ->http://sqlserverpedia.com/wiki/Log_Shipping_Performance_Issues_%28SQL_Server_2000%29

---

Thanks, Rama Udaya.K (http://rama38udaya.wordpress.com) ---------------------------------------- Please remember to mark the replies as answers if they help and UN-mark them if they provide no help,Vote if they gives you information.

Answer 3

Thanks for the information. Assuming normal restore/recovery (nothing to do with log shipping) and we are restoring full and 20 log files on test database, How SQL Server apply the log backups:

1)If there is a update statement on table affecting all the rows(10M rows) on primary server in one of the log backup files, Will it apply the same update statement during restore/recovery and update all the indexes or is there is a different algorithm used during restore/recovery and restore will be faster as compared to applying same update on test system?

 

Answer 4

please watch this video which gives some insight about the restore internals. You may ask further questions if you have any after watching this video

http://technet.microsoft.com/en-us/sqlserver/gg508895

Answer 5

Thanks, I had watched this video and it is very good but can you specify where it mention what we are trying to find out:

"Will it apply the same update statement during restore/recovery and update all the indexes or is there is a different algorithm used during restore/recovery"

Answer 6

Simple answer to it: On Restore SQL does not run the statements again. What happens though is that every page that has been touched by a statement in the TLog needs to get updated. Technically what SQL does (and please guys correct me if I am wrong...) is that SQL scans the transaction log, loads the changed page into Buffer Pool, updates the page and then checkpoints it back out to disc. There are of course lots of optimization in that process, but from a high level perspective that's the way it's done.