Description:
Sometimes you may notice your restore job on the secondary server will be taking much more time than usual, so what is wrong??
This is because of long running transactions (uncommitted transactions) spanning multiple log backups and the LS being configured as STANDBY…
Detailed Explanation:
• When restoring transaction log with the “STAND BY” option, SQL Server will go over the transaction log and save all uncommitted transactions to a file with .tuf(Transaction UNDO File) extension.
• You will not see the uncommitted data in the secondary DB 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.
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 are in the tuf file and then continue to restore the next transaction log.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.
• So with the above explanation let us assume a rebuild index on a big table that has run for 45 mins and let’s say 3 log backups were taken in this duration, so first log backup uncommitted transactions will be written to tuf file while restoration and when the second log backup is being restored, the sql server will redo all the uncommitted transactions from the tuf file and will continue the restoring the log backup but the second log backup also has uncommitted transactions so SQL server will again UNDO the index operation and will save the uncommitted transactions to tuf file and this process continues until transaction is committed in further log backups causing the delay in LOG RESTORE…
I hope above explanation had helped you understand the reason behind slow restore on secondary stand by log shipping server.
References: Dan's madebysql Blog.
Sometimes you may notice your restore job on the secondary server will be taking much more time than usual, so what is wrong??
This is because of long running transactions (uncommitted transactions) spanning multiple log backups and the LS being configured as STANDBY…
Detailed Explanation:
• When restoring transaction log with the “STAND BY” option, SQL Server will go over the transaction log and save all uncommitted transactions to a file with .tuf(Transaction UNDO File) extension.
• You will not see the uncommitted data in the secondary DB 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.
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 are in the tuf file and then continue to restore the next transaction log.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.
• So with the above explanation let us assume a rebuild index on a big table that has run for 45 mins and let’s say 3 log backups were taken in this duration, so first log backup uncommitted transactions will be written to tuf file while restoration and when the second log backup is being restored, the sql server will redo all the uncommitted transactions from the tuf file and will continue the restoring the log backup but the second log backup also has uncommitted transactions so SQL server will again UNDO the index operation and will save the uncommitted transactions to tuf file and this process continues until transaction is committed in further log backups causing the delay in LOG RESTORE…
I hope above explanation had helped you understand the reason behind slow restore on secondary stand by log shipping server.
References: Dan's madebysql Blog.
No comments:
Post a Comment