-
Title
Will the SQL transaction log truncation make the physical log file smaller? -
Description
The truncation process does not reduce the size of a physical log file
-
Resolution
During the truncation process, only the active portion of the online SQL Server transaction log file is scanned. Some parts of the scanned portion are marked as inactive and they will be used as free space to write down new transactions. There isn’t a change in the online transaction log size because the inactive parts remain intact, nothing is deleted or removed
Every SQL Server transaction log is made of Virtual Log Files (VLFs). During the truncation process, only the Logical log is scanned. A Logical log is made of active VLFs. A Log Sequence Number (LSN) is used to uniquely identify every transaction in the online transaction log. The MinLSN is the starting point of the oldest active transaction in the online transaction log
The online SQL Server transaction log file is circular by internal organization. When logging reaches the end of the transaction log, it starts again from the beginning by overwriting the parts marked as inactive
Blue represents the truncated part of the online transaction log ready to be overwritten
Find more information on the SQL Server transaction log topic in the 10 most important SQL Server transaction log myths article.