question

narendba avatar image
narendba asked

Transaction log backup vs tuf file in log shipping?

Hi, I just want to know one thing, If we take a t-log backup, Is it backing up the Uncommited transactions as well? What is the use of .tuf file in standby mode and what's difference between normal transaction log backup in logshipping with norecovery mode and log backup in stand by mode in log shipping?
sql-server-2005
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
KenJ avatar image
KenJ answered
The log backup does include uncommitted transactions. It rolls these back during recovery to make the restored database transactionally consistent. With the two types of log shipping restores you mention, there is no difference in transaction log backups - the difference is the state in which the restore leaves the database. With standby mode, the uncommitted transactions are rolled back to make the database transactionally consistent so it can be read. The transaction log records to reopen these transactions are stored in the undo file. When a subsequent log backup is restored, these transactions are replayed to their open state prior to the restore. With norecovery mode, the undo phase of recovery is skipped (You can read about the three phases of recovery here - [ https://technet.microsoft.com/en-us/library/ms191455(v=sql.105).aspx][1]). Uncommitted transactions are left uncommitted and the database remains in a transactionally inconsistent state. Subsequent log backups can be restored without reopening these transactions, so no undo file is required. Books online has a good high-level overview of log restores. Steps 10 and 11 touch on this topic - [ https://msdn.microsoft.com/en-us/library/ms177446.aspx][2] [1]: https://technet.microsoft.com/en-us/library/ms191455(v=sql.105).aspx [2]: https://msdn.microsoft.com/en-us/library/ms177446.aspx
5 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

KenJ avatar image KenJ commented ·
Your understanding of no recovery is correct. For standby, the transaction T1 is literally rolled back and the log records to reopen the transaction are stored in the tuf file. When the next log backup comes to the secondary, the log records in the tuf file are reapplied before the restore starts and the tuf file is no longer relevant. If the next log backup is also restored with standby, it will create its own tuf file which may or may not contains log records for transaction T1 depending on whether it had committed by that time. We're probably in agreement on standby, but I didn't quite follow your example through to the end.
2 Likes 2 ·
narendba avatar image narendba commented ·
Hi, Thanks for your valuable time and comment on below my understandings. For example I have T1 transaction, it need to update 1000 records. While log backup is running the T1 transaction updated only 500 records then it's uncommited transaction and it's backedup with the state of 500 records updated information. As per your comment log shipping with norecovery mode the log backup moved to secondary and restored the log backup on database with 500 updated records and it's not accessible to user as of now. When ever we are trying to recover the database on secondary then it's going to roll back the T1 transaction as it's not committed and partially executed on database right? Where as in 'STANDBY' mode in log shipping the log backup moved to secondary and it's just maintain the uncommitted transaction T1 details in 'TUF' file and when ever the next log backup come to secondary and 'TUF' file will check the T1 transaction is committed or rollback on next log backup file and change the status of the T1 transaction in TUF file right?
1 Like 1 ·
narendba avatar image narendba commented ·
I am bit confused on standby mode which you explained. Lets conclude this. As you said the uncommitted transactions rolled back in secondary server in logshipped stand by database and saved them in 'tuf' file. But how the 'tuf' file knows to reopen or applay the uncommitted transactions before the next backup restored in secodary database.
1 Like 1 ·
KenJ avatar image KenJ commented ·
SQL Server knows where the tuf file is and that it has to reapply the transactions when the next restore statement is executed. the tuf file itself doesn't know - it's just a file.
0 Likes 0 ·
narendba avatar image narendba commented ·
"reapply the transactions when the next restore statement is executed"... It means If T1 is a uncommitted transaction in first log backup file and it stored in 'tuf' file before it rolled back in scondary server.... when ever the second log backup file comes to secondary sql server will check the 'T1' transaction status is commit in secondary log backup file then it just take it from tuf file and reapply it right? If it's in rolled backup state in second log backup then it's just delete the T1 transaction from tuf file right?
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.