Transaction Log Internals - Pro SQL Server Internals - page 578

■ transaction log truncation does not reduce the size of the log file on disk. truncation means that parts of
transaction log (one or more VLFs) are marked as inactive and ready for reuse. it clears up the internal space in the log,
keeping log file size intact.

Note

A transaction log is a wraparound file. When the end of the logical log file reaches the end of physical file, the log
wraps around it, as shown in Figure 29-9 .

Figure 29-9. A Transaction Log is a wraparound file

SQL Server creates new virtual log files every time the log grows. The number of VLFs depends on the newly
allocated space size as shown in Table 29-1 .

Table 29-1. Allocation size and number of VLF created

Allocation Size Number of VLF Created

<64MB 4 VLFs

64MB - 1GB 8 VLFs

>1GB 16 VLFs

You can examine virtual log files with the DBCC LOGINFO command. Figure 29-10 illustrates the output of such
a command running against the master database on one SQL Server instance. It shows that the database has one
physical log file with FileId = 2 and three virtual log files. Other columns indicate the following:
Status is the status of VLF. Values 0 and 2 indicate inactive and active VLFs respectively.
FileSize is the size of the VLF in bytes.
StartOffset is the starting offset of the VLF in the file.
CreateLSN is the LSN at the moment when the VLF was created. Zero means that the VLF
was created at the database creation time.
FSeqNo is the order of usage of the VLFs. The VLF with the highest FSeqNo is the file where
the current log records are written.
Parity can be one of two possible values: 64 and 128. SQL Server switches the parity
value every time a VLF is reused. SQL Server uses the parity value to detect where to stop
processing log records during a crash recovery.