8

I am trying to gather some knowledge on SQL databases and I have some questions about the transaction log file (LDF).

First off, when you create a database, you have to define an Initial File Size for both the database and the log file. From what I can see, once the files are created on the disk, they will be of that specified size regardless if there is actual data in the database or if there has been any transactions (logs).

My understanding was that backing up a database:

  1. Truncates the transaction log, and
  2. Shrinks the size

of the LDF file on the disk by "emptying" the logs inside it.

Now it seems that I was not understanding this correctly because the log file seems to be of fixed size. My actual question goes like this:

What does truncating logs actually do to the log file (LDF)? This process is supposed to prevent disks from getting full.

Please do correct me if I am not understanding some concepts correctly.

Thank you!

20

The transaction log file (LDF) is made up of lots of virtual log files (VLFs) inside. Think of it like a cabinet with several pull-out drawers. You could choose a large cabinet, or a small one - but it's still going to be a fixed size with just different numbers of drawers.

As SQL Server works, it puts your transactions into drawers (VLFs.) It starts at one end of your cabinet, fills up the first drawer, then when space runs out in that drawer, it moves on to the next drawer.

When you back up the transaction log, what you're really doing is:

  • Finding the first drawer that has transactions in it that haven't been backed up yet
  • Copying those transactions elsewhere
  • If that drawer is no longer actively in use (because SQL Server has moved on to the next drawer), then you're marking that drawer as available to reuse (think of it as throwing all the contents out)

Backups don't change the size of your cabinet (log file).

  • Hi Brent, thank you for clearing that up for me, appreciate it! So basically, you cannot reduce the size of the "cabinet", you can only empty the drawers. Now I read a lot of posts of people talking about shinking the log size. Would that refer to shrinking the size of the VLFs (content of the drawer) and actually shinking the size of the LDF file on the disk? From what I can now understand, you cannot shink the "cabinet". Thanks! – Anthony Apr 10 '18 at 11:11
  • @Anthony yep, you can reduce the size of the cabinet by shrinking the log file size, but generally, that's a very bad idea (for the reasons you've read about.) – Brent Ozar Apr 10 '18 at 12:06
  • I would veto the statement that shrinking a TLog is bad. Tlog access is fairly seq. in nature. There is no data reorganization needed when shrinking the log (like for data files. you can only release space past the last active pointer). So this does not cause any fragmentation at all. – Heiko Hatzfeld Apr 10 '18 at 13:47
  • 1
    @HeikoHatzfeld growing it back out is a blocking operation and doesn't leverage Instant File Initialization, so it's terribly slow. Veto overridden. ;-) – Brent Ozar Apr 10 '18 at 16:55
  • Its nothing you do on a regular basis but only once after you noticed you didn't setup a correct backup plan... And the growth factor should be a constant, reasonable size. Under those conditions its bearable. – Heiko Hatzfeld Apr 11 '18 at 4:47
6

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.

  • Shrinks the size

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).

4

While Brent Ozar has already given you explanation about how transaction log file looks like I will focus on your certain questions

My understanding was that backing up a database:

  • Truncates the transaction log, and
  • Shrinks the size

Full backup does nothing to transaction logs in any recovery model. In full recovery model when you take transaction log backup then it truncates the log. Please note if a long running transaction is still there holding the VLF's or as per Brent's explanation still needs the drawers other transaction cannot re-utilize the drawer or in technical terms it would not be truncated so that it can be re-utilized.

It also does not shrinks the transaction log. For shrinking the logs you have to use dbcc shrinkfile command

What does truncating logs actually do to the log file (LDF)? This process is supposed to prevent disks from getting full.

It makes the log file reusable so that other transactions can use it or as per Brent's analogy someone else can use the drawers to keep there stuff.

After going through the answer I strongly recommend you to read about transaction log on SQLSKILLS.com

  • Thanks Shanky, really appreciate you explanations! So when backups truncate logs, they don't actually reduce the size of the LDF file, they only "make room" for other transactions to be processed inside that log file. Am i correct in assuming this? Thanks! – Anthony Apr 10 '18 at 11:33
  • You are absolutely correct. – Shanky Apr 10 '18 at 11:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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