Transaction Log Structure
Intro
This post shows the internal logical structure of the SQL Server Transaction Log. The details here target SQL Server 2000 to 2017. Note that SQL Server 2019 will likely include Accelerated Database Recovery, aka ADR, which drastically changes some features of the transaction log, and recovery in general.
I tried explaining transaction log structure to these guys.
They just won’t listen.
Every database has its own transaction log. The transaction log is not a simple text log of actions taken by SQL Server. It is a critical binary log containing every alteration made to the database. All changes, including “metadata” operations such as renaming a table, are written to the transaction log. This applies to all recovery models, however you can reduce the detail captured when using Bulk-Logged Recovery. See Microsoft’s Documentation on recovery models for further details.
Structure
Regardless of the physical size of the transaction log, internally they contain multiple Virtual Log Files, or VLFs. Virtual Log Files consists of 1 or more Log Blocks. Log Blocks vary in size from 512 bytes to 60 KBytes. Each Log Block has one or more Log Records. A single log record is the smallest unit-of-allocation inside a SQL Server Transaction Log.
See Optimal Log File Growth and Virtual Log Files for details about sizing Virtual Log Files.
As SQL Server consumes space in the transaction log, existing VLFs will be re-used, if they are not already in use. New VLFs are added in an atomic operation that blocks all ongoing transactions while the log is grown. Once a log grows to the maximum of either the size specified by the MAXSIZE parameter, or the size of the disk, or 2 Terabytes, whichever is less, all user transactions will be blocked unless an existing VLF can be reused, or a new log file can be added to the database. Once a VLF contains only fully committed or rolled-back transactions, the VLF is marked for truncation. Truncated VLFs are re-used in round-robin fashion. VLF Truncation in Simple Recovery Model shows how that works.
Visuals!
The following is a visual representation showing how VLFs consist of Blocks, and Log Records. All red blocks and records exist inside VLF #1. All green blocks and records exist inside VLF #8.
╔════════╦════════╦════════╦════════╦════════╦════════╦════════╦════════╗ ║ VLF #1 ║ VLF #2 ║ VLF #3 ║ VLF #4 ║ VLF #5 ║ VLF #6 ║ VLF #7 ║ VLF #8 ║ ╚═══╦════╩════════╩════════╩════════╩════════╩════════╩════════╩════╦═══╝ ║ ║ ╔╩═════════╦══════════╦══════════╦══════════╦══════════╗ ║ ║ Block #1 ║ Block #2 ║ Block #3 ║ Block #4 ║ Block #5 ║ ║ ╚═══╦══════╩══════════╩══════════╩══════════╩═════════╦╝ ║ ║ ║ ║ ╔╩══════════╦═══════════╦═══════════╦═══════════╗ ║ ║ ║ Record #1 ║ Record #2 ║ Record #3 ║ Record #4 ║ ║ ║ ╚═══════════╩═══════════╩═══════════╩═══════════╝ ║ ║ ║ ║ ╔════════════╦════════════╦════════════╦═════╩══════╗ ║ ║ Record #17 ║ Record #18 ║ Record #19 ║ Record #20 ║ ║ ╚════════════╩════════════╩════════════╩════════════╝ ║ ║ ║ ╔═══════════╦═══════════╦═══════════╦═══════════╦═════════╩═╗ ║ Block #36 ║ Block #37 ║ Block #38 ║ Block #39 ║ Block #40 ║ ╚═══╦═══════╩═══════════╩═══════════╩═══════════╩═══════════╝ ║ ╔╩════════════╦═════════════╦═════════════╦═════════════╗ ║ Record #157 ║ Record #158 ║ Record #159 ║ Record #160 ║ ╚═════════════╩═════════════╩═════════════╩═════════════╝
Please don’t construe the numbers to mean there are always an equal number of log records per block, or an equal number of blocks per VLF. I’ve only numbered them to show that each block and record is unique.
Log Sequence Numbers indicate the VLF, Block, and Record. Their format is vlf_number:block_offset:record_slot_number. Remus Rusanu has a great post showing how Log Sequence Numbers work.
SQL Server has a couple of methods for looking at the structure of the transaction log. DBCC LOGINFO (<database_name>) works for all versions. The slightly more friendly SELECT * FROM sys.dm_db_log_info(DB_ID()); can be used on SQL Server 2016 SP2+. See the Docs for sys.dm_db_log_info here.
Output from DBCC LOGINFO for my master database looks like:
╔════════════════╦════════╦══════════╦═════════════╦════════╦════════╦════════╦════════════════════╗
║ RecoveryUnitId ║ FileId ║ FileSize ║ StartOffset ║ FSeqNo ║ Status ║ Parity ║ CreateLSN ║
╠════════════════╬════════╬══════════╬═════════════╬════════╬════════╬════════╬════════════════════╣
║ 0 ║ 2 ║ 253952 ║ 8192 ║ 208 ║ 0 ║ 64 ║ 0 ║
║ 0 ║ 2 ║ 262144 ║ 262144 ║ 210 ║ 0 ║ 64 ║ 0 ║
║ 0 ║ 2 ║ 262144 ║ 524288 ║ 209 ║ 0 ║ 64 ║ 208000000036000139 ║
║ 0 ║ 2 ║ 262144 ║ 786432 ║ 211 ║ 0 ║ 64 ║ 210000000042900116 ║
║ 0 ║ 2 ║ 262144 ║ 1048576 ║ 212 ║ 0 ║ 64 ║ 210000000044700451 ║
║ 0 ║ 2 ║ 262144 ║ 1310720 ║ 213 ║ 0 ║ 64 ║ 211000000001700829 ║
║ 0 ║ 2 ║ 262144 ║ 1572864 ║ 214 ║ 0 ║ 64 ║ 211000000013700001 ║
║ 0 ║ 2 ║ 262144 ║ 1835008 ║ 215 ║ 0 ║ 64 ║ 211000000013900901 ║
║ 0 ║ 2 ║ 12845056 ║ 2097152 ║ 216 ║ 0 ║ 64 ║ 212000000014000005 ║
║ 0 ║ 2 ║ 12845056 ║ 14942208 ║ 217 ║ 0 ║ 64 ║ 212000000014000005 ║
║ 0 ║ 2 ║ 12845056 ║ 27787264 ║ 218 ║ 2 ║ 64 ║ 212000000014000005 ║
║ 0 ║ 2 ║ 12845056 ║ 40632320 ║ 0 ║ 0 ║ 0 ║ 212000000014000005 ║
║ 0 ║ 2 ║ 12845056 ║ 53477376 ║ 0 ║ 0 ║ 0 ║ 212000000014000005 ║
║ 0 ║ 2 ║ 12845056 ║ 66322432 ║ 0 ║ 0 ║ 0 ║ 212000000014000005 ║
║ 0 ║ 2 ║ 12845056 ║ 79167488 ║ 0 ║ 0 ║ 0 ║ 212000000014000005 ║
║ 0 ║ 2 ║ 12845056 ║ 92012544 ║ 0 ║ 0 ║ 0 ║ 212000000014000005 ║
╚════════════════╩════════╩══════════╩═════════════╩════════╩════════╩════════╩════════════════════╝
I’ve highlighted the currently active VLF in red above. As you can see, the status is 2, which indicates “active”. Possible statuses1 are:
If you have a question, or a comment, please don’t hesitate to post it in the “Leave a Reply” section at the bottom of the page.
Also, be sure to check out the posts in our series on database recovery!
1 – What is the plural form of status