Share via

Why TUF file is required in Log shipping when uncommitted transactions are already in memory?

Chirag Sachdeva 281 Reputation points
2023-04-10T14:18:55.13+00:00

I know that while restoring the log backup, un-committed transactions will be recorded to the undo file and only committed transactions will be written to disk there by making users to read the database. When we restore next Tlog backup SQL server will fetch the un-committed transactions from undo file and check with the new Tlog backup whether the same is committed or not. If its committed the transactions will be written to disk else it will be stored in undo file until it gets committed or rolled back. But why there is need to store uncommitted transactions in TUF file as uncommitted transactions are already in memory of primary server. Please help

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
14,494 questions
Accepted answer
  1. Erland Sommarskog 120.3K Reputation points MVP
    2023-04-10T21:25:35.1+00:00

    The memory on the primary server is not available on the log-shipping replica. And for that matter, the data from the uncommitted transactions does not have be in memory. If there is memory pressure, this pages may be flushed from the cache. Keep in mind that data is written to disk, even if the transaction is not committed. Yes, this means that there will be more work to do in case of a rollback, but the assumption is that transactions commit more often than they roll back.

    In the next transaction-log backup that is shipped there may be more data for this transaction, and there may also be the commit-mark. But the uncommitted transactions from the previous log-backup are not present in this backup. This backup only holds log records created after the previous back. This is how the log backup works.

    Keep in mind that the primary database has no knowledge the the logs are used for log shipping.

    1 person found this answer helpful.
    0 comments

1 additional answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Seeya Xi-MSFT 16,581 Reputation points
    2023-04-11T06:15:11.6266667+00:00

    Hi @Chirag Sachdeva,

    The Transaction Undo File (TUF) is used in log shipping as a mechanism to store uncommitted transactions that are present in the transaction log backup being shipped to the secondary server.

    The secondary server applies these transaction log backups in sequence to bring the database to the same state as the primary server.

    However, if the secondary server were to apply a transaction log backup that contained uncommitted transactions, it could lead to data inconsistency. For example, if the primary server were to experience a failure before the uncommitted transactions were committed, the database would not be in a consistent state. To prevent this from happening, the uncommitted transactions are stored in the TUF file on the primary server and shipped to the secondary server along with the transaction log backups.

    Best regards,

    Seeya

    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

    2 people found this answer helpful.

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.