T-log file and Virtual Log Files (VLFs) and Log blocks and Write-ahead transaction logging

Transaction log file : The database transaction log maps over one or more physical files. Conceptually, the log file is a string of log records. Physically, the sequence of log records is stored efficiently in the set of physical files that implement the transaction log. There must be at least one log file for each database.

The SQL Server transaction log operates logically as if the transaction log is a string of log records. Each log record is identified by a log sequence number (LSN). The log sequence number (LSN) of this first log record is known as the minimum recovery LSN (MinLSN).

The basic structure of an LSN is [VLF ID:Log Block ID:Log Record ID]

Here's an example of an LSN:00000031:00000da0:0001, look at the output of sys.dm_db_log_info DMV and examine the vlf_create_lsn column.

Many types of operations are recorded in the transaction log. These operations include:

• The start and end of each transaction.

• Every data modification (insert, update, or delete). Modifications include changes by system stored procedures or data definition language (DDL) statements to any table, including system tables.

• Every extent and page allocation or deallocation.

• Creating or dropping a table or index.

Virtual Log Files (VLFs)

The SQL Server Database Engine divides each physical log file internally into several virtual log files (VLFs). Virtual log files have no fixed size, and there's no fixed number of virtual log files for a physical log file. The Database Engine chooses the size of the virtual log files dynamically while it's creating or extending log files. The Database Engine tries to maintain a few virtual files. The size of the virtual files after a log file has been extended is the sum of the size of the existing log and the size of the new file increment. The size or number of virtual log files can't be configured or set by administrators.

VLF's creation formula has been changed for SQL Server 2014 (12.x) and later versions

·       If growth < 64 MB, create 4 VLFs that cover the growth size (for example, for 1-MB growth, create 4 VLFs of size 256 KB).

·       If growth > 64 MB up to 1 GB, create 8 VLFs that cover the growth size (for example, for 512-MB growth, create 8 VLFs of size 64 MB).

·      If growth > 1 GB, create 16 VLFs that cover the growth size for example, for 8-GB growth, create 16 VLFs of size 512 MB).

What is the impact of too many VLF’s? 

One or more databases take a very long time to finish recovery during SQL Server startup.

Restoring a database takes a very long time to complete.

Attempts to attach a database take a very long time to complete.

When you try to set up database mirroring, you encounter error messages 1413, 1443, and 1479, indicating a timeout.

You encounter memory-related errors like 701 when you attempt to restore a database.

Log blocks: Each VLF contains one or more log blocks. Each log block consists of the log records.

A log block is the basic unit of I/O for transaction logging.

In summary, a log block is a container of log records that's used as the basic unit of transaction logging when writing log records to disk.

Log Truncation: Log truncation deletes inactive virtual log files from the logical transaction log of a SQL Server database, freeing space in the logical log for reuse by the physical transaction log. If a transaction log is never truncated, it will eventually fill all the disk space that is allocated to its physical log files. 

However, before the log can be truncated, a checkpoint operation must occur. 

A checkpoint writes the current in-memory modified pages (known as dirty pages) and transaction log information from memory to disk. When the checkpoint is performed, the inactive portion of the transaction log is marked as reusable. Thereafter, a log truncation can free the inactive portion.           

Write-ahead transaction logging : SQL Server uses a write-ahead logging (WAL) algorithm, which guarantees that no data modifications are written to disk before the associated log record is written to disk. This maintains the ACID properties for a transaction.

To understand how the write-ahead log works, it's important for you to know how modified data is written to disk. SQL Server maintains a buffer cache (also called a buffer pool) into which it reads data pages when data must be retrieved. When a page is modified in the buffer cache, it isn't immediately written back to disk; instead, the page is marked as dirty. A data page can have more than one logical write made before it's physically written to disk. For each logical write, a transaction log record is inserted in the log cache that records the modification. The log records must be written to disk before the associated dirty page is removed from the buffer cache and written to disk.