Matching and Mixing
Backing up the transaction log is not the same as truncating the transaction log file, and truncating the transaction log file is not the same as shrinking the transaction log file. Oh yes, and backing up the transaction log file does not have to trigger a truncation. Depending on the current load the database engine might decide to set a checkpoint, but to wait a bit with the truncation.
Explaining
The transaction log file is where the database engine stores modifications made to the data in a database, regardless of whether the database is in SIMPLE recovery model or in the FULL recovery model. (Important)
Now the database's transaction log file is not just one continuous storage container, but a collection of Virtual Log Files (VLFs) which are created in a sequential order inside the Transaction Log (TLog) file. The size of the VLFs varies depending on which version of SQL Server you are currently using and also on the initial size you selected during the creation of the TLog file and also which size you selected (if any) for the auto-growth setting of the TLog file.
References:
- Important change to VLF creation algorithm in SQL Server 2014 (SQLSkills.com)
- Initial VLF sequence numbers and default log file size (SQLSkills.com)
- Inside the Storage Engine: More on the circular nature of the log
(SQLSkills.com)
...and maybe in the reverse order
When data is modified in the database, the Database Engine will write these changes into the TLog of the corresponding database to uphold transactional consistency. This is also know as ACID - Atomicity, Consistency, Isolation, Durability. The actual transcations of these changes are stored in the VLFs of the TLog (file). When a VLF is full the newest transactions will be stored in the next available VLF in sequential order.
Exceptions
However if the end of the TLog file is reached, the modifications will be stored in the first VLF at the beginning of the TLog file. (explained in Inside the Storage Engine: More on the circular nature of the log)
When no available VLFs are free to store new transactions and if the auto-growth setting is configured, the Database Engine will grow the TLog file by the amount defined and create additional VLFs depending on the size defined in the auto-growth settings and the formula explained in Important change to VLF creation algorithm in SQL Server 2014. Further transactions can then be stored in the next VLF inside the TLog file.
Backing Up The TLog File
When you trigger a backup of the TLog file all you are doing is telling the database engine to
- have a look at the TLog file
- determine when the last transaction log backup occurred (LSN : Log Sequence Number; for further research)
- set a Checkpoint in the TLog file (Database Checkpoints (SQL Server))
- store a backup copy of the TLog file on disk/tape while keeping track of the previous LSN and the last commited LSN just before the backup finishes
- transfer all the modifications to the "database"
- mark the VLFs as reusable
So far no space has been released inside the TLog file for the database Engine to reuse...
Automatic Truncation of The TLog File
...but if the Database Engine has some cycles to spare and is not under very heavy pressure it will occasionally have a look at the TLog file, notice the Checkpoint and release the VLFs for reuse. The space inside the TLog file is still used by the VLFs (same size, same location) but they are free to be reused.
This is documented in Transaction Log Truncation:
Except when delayed for some reason, log truncation occurs automatically as follows:
- Under the simple recovery model, after a checkpoint.
- Under the full recovery model or bulk-logged recovery model, after a log backup, if a checkpoint has occurred since the previous backup. For more information, see "Log truncation under the full and bulk-logged recovery models," later in this topic.
There are some cases when this doesn't happen:
Although automatic, log truncation can be delayed by a variety of factors. For information about what can delay log truncation, see Factors That Can Delay Log Truncation.
Visualising Log Truncation
Log truncation can be observed when you query the TLog size using SQL statements or the Database Space report in the SSMS UI. You might observe that the used space inside the TLog file might only be 1% of the available TLog file size.
To Shrink or Not To Shrink
The general recommendation is not to shrink the TLog file, because it grew for a certain reason and will possibly grow again to the size it once was. But that is a story for another post. There are some good reasons, one being when you are re-creating the size of the VLFs inside your TLog file.
Answering your questions
Inline right under your assumptions and quesitons
My understanding was that backing up a database:
- Truncates the transaction log, and
This is a wrong assumption. Backing up your database (FULL, DIFFERENTIAL) does nothing with the TLog files. A FULL backup will create a consistent state of your database together with the committed transactions form the TLog file. A DIFF backup will create a consistent state of all past TLog backups since the last FULL backup of your database.
However, a TLOG Backup will create a backup of the committed transactions from the TLog file, setting a checkpoint and possibly (when not under heavy load) releasing the VLFs for reuse.
No, when considering the FULL and DIFF backups.
No, when considering the TLOG backups, but it will free up the VLFs inside the TLog file, if the database engine has some time to spare.
What does truncating logs actually do to the log file (LDF)? This process is supposed to prevent disks from getting full.
Truncating the logs allows for the VLFs to be reused. That's all.
This process can have the benefit of preventing the TLog file from growing IF auto-growth settings have been set.
If no auto-growth settings have been set, because your requirements engineering process determined that the TLog file would have a fixed size, then the worst case here is that the TLog fills up because no TLog backup occurs and hence no VLFs are being freed. The TLog can't grow and the VLFs are not freed up to allow for further transactions to be written to the TLog file (or VLFs internally).
