2

I understand that a Microsoft SQL Server Database has two components:

  1. A database file
  2. A transaction log file

Whenever I make a change to a table, does it get written to both the database file AND the transaction log file? Or does it just go to the transaction log and then convert to the database file after a while?

Furthermore: when I do a backup of the transaction log file, does it clear ALL logs? Or does it just remove some?

3
  • 2
    I'd recommend reading this to help you understand the concept of the transaction log, the write-ahead log protocol, and the purpose/result of log backups.
    – swasheck
    Jan 14, 2016 at 18:19
  • 2
    or this
    – Tom V
    Jan 14, 2016 at 19:14
  • Or this book - it was free some time ago, most likely still is.
    – Marian
    Jan 14, 2016 at 20:13

5 Answers 5

3

Whenever I make a change to a table, does it get written to both the database file AND the transaction log file?

It is written to both, but it is written differently to each. The changes are made to the data pages in memory and are eventually flushed to disk via the checkpoint process. the changes are sent to a log buffer and hardened to disk at some point, though before the data file is written to disk to keep with the write ahead logging protocol.

Furthermore: When I do a backup of the transaction log file, does it clear ALL logs? Or does it just remove some?

A log backups allows VLFs of the log to me marked for reuse if nothing is actively depending upon them. Nothing is physically cleared, just marked to allowed to be reused.

2

enter image description here

The transaction log backup does not physically clear all logs.

When the checkpoint occurs all log records is written to the data file. So, the virtual log files can be reused (it is known as truncating the transaction log).This case just for simple recovery model.

In the full or bulk-logged recovery models, the transaction log will need to be extended. You should keep your transaction log to a manageable size because it does not truncate after transaction log backup.

1

The transaction log allows for two features:

  1. Performance. It allows for sequential writes to finish the 'commit' phase more quickly than if the database engine had to write to all the random pages necessary for the transaction(s). Changes are written to the log file sequentially (faster than random writes) and then flushed during the 'commit' phase, at which point the transaction will persist, even if there is a power failure or crash of the system. These writes are then subsequently collected, sorted, and written to the database file in a more sequential order than would otherwise be possible (because writes from multiple transactions are collected and written to the database file together). If those writes are interrupted by a crash or power failure, the database system uses the information in the log (which was flushed before the commit) to detect the problem and re-write the changes that weren't flushed to the data file, thus ensuring the integrity of the database and the persistence of all committed transactions.
  2. Replication. Because transaction logs contain all the changes made to the database, they can be shipped to another computer with a copy of the same database and applied there for failover/replication purposes (though this only works if no other changes have been made).
0

Research recovery model. Default - simple - contains transactions in action. Full recovery records transactions since last log backup; in case you want to restore back to before the transaction ( corruption ) occurred [need to have a full backup first!]

Simplistically: Two files: two states. MDF - truth. LDF intended truth.

Backing up log commits the intended truth to file (MDF)(commits the committed transactions). Truths (transactions) completed are marked as done, so are overwritten in the log, and the file doesn't grow.

So, when you have SIMPLE recovery model and delete every row from every table in one transaction - every impacted row will be in the transaction and the file will grow. If you commit the transaction, the MDF will be empty but the size will be the same, the log will empty, the file will be same size; and you will have no data.

You cannot restore the database to the point in time before this transaction in SIMPLE recovery model ( unless you made a full backup just prior to the Great Purge) BUT if you had FULL RECOVERY mode, and a full backup, you could make a tail-end log backup; and RESTORE the full, and log backups to a point in time before the Great Purge. (STOPAT 09:59) :)

The logs there for recovery and consistency... read up on ACID transactions too.

0

The short answer is - after you made some changes to a table all this information are recorded into transaction log immediately, but all changes that you made are recorded into data file during the checkpoint. After the transaction log backup, all virtual log files marked to be reused.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.