Does backing up a database shrink the transaction log size? 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:


Truncates the transaction log, and
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!
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).