Using container is pretty straightforward, you just need to start it:

Once the container is running, you can connect directly from your host machine or attach to the container and use the built-in mssql-tools:

Follow the official guide for RHEL or Fedora. The steps for adding appropriate repo, installing and configuring the server are straightforward:

You also probably would need to install SQL Server client tools:

and connect locally:

If you want to connect from outside the VM, open port 1433:

Once the server is up and running, we can create a test database and table in it:

A transaction log isn’t one giant monolithic space — it’s divided into Virtual Log Files (VLFs). SQL Server decides how many VLFs to create based on the log size. You can get VLF sequence numbers and other information about the database by calling sys.dm_db_log_info procedure:

Example output containing a VLF sequence number (unique per file), begin offset (where it starts in the file) and size in MB looks like this:

Besides the VLF sequent number, another interesting thing is the VLF offset. The first VLF actually doesn’t start at the very beginning of the log file, but after 8192 bytes of the file. The first 8kB are reserved for the log file header.

VLFs header contains information whether VLFs is active or not. VLFs are used in a circular fashion and unused VLFs can be reused for newly created VLFs. VLF cannot be reused when it’s in an active state. This typically happens when it contains records which are part of an uncommitted transaction or the records weren’t archived yet or processed by other parts of the system (copied to the change tables, replicated to another server etc.). VFLs are converted from active to inactive state by the process called log truncation. Exact behavior when and how VLFs are being reused depends also on the recovery model of the given database. For SIMPLE recovery model VLFs are eventually reused after the checkpoint operation, while in FULL recovery model checkpoint operation doesn’t result into TX log truncation.

You can find out current recovery mode of the database by running following SQL command:

Physical structure of the VLFs on the disk is the same as the VLFs rows in the above listing. This can be seen also from the VLF offsets vlf_begin_offset. However, the logical structure of VLFs can be different. The sequence of the VLFs is determined by the vlf_sequence_number. 0 means that VLS haven’t been used yet.

Very similar output to the query above can be obtained using DBCC tool by running

More about DBCC utility will be mentioned later in this post.

Each VLF is split into blocks - the smallest physical write unit in the log. Block size ranges from 512 B to 60 KB, growing in 512 B increments until the maximum size is reached. Blocks are the actual units written to disk, usually at transaction commit or checkpoint.

The blocks contain actual transaction log records. Log records are single atomic changes done to the database, e.g. insert, update, TX commit etc. The log records are stored in the block in the same order as they were executed in the database. Thus the records for different transactions can be mixed within the block. Also, when the block is written to disk, it can contain records from uncommitted transactions.

Overall structure of the SQL server transaction log file is schematically depicted on the following figure.

Blocks as well as records in the blocks have assigned unique sequence numbers. Block sequence number is 4 bytes long, while record sequence number is only 2 bytes long. When we put it together with a VLF sequence number, each record can be uniquely identified by triple numbers:

This unique record identifier is known as log sequence number, usually shortcutted as LSN. This is the number which Debezium uses for storing offsets - what is the latest change seen by Debezium and what is the latest committed transaction processed by Debezium.

Each table or index is stored in one or more partitions. The maximum number of partitions each table or index can have is 15,000. The subset of the table or index stored in a single partition is called hobt, which is a shortcut from Heap or B-tree. Heap is called a table without clustered index, which data is not organized according to the index. For tables with clustered index, table data is organized according to the index and is actually index rows.

A partition contains the data pages where the actual rows live. There are three main types of data pages. In-row data pages, which store fixed-size data or data which fits into one page. Row-overflow data pages store variable-length data types such as varchar or nvarchar when they don’t fit entirely in-row. LOB data pages store large objects (LOBs), such as xml or large binary data. Pages of the same type within a partition are grouped into allocation units.

Pages are elementary storage unit in SQL Server and are stored in the .mdf files we discussed earlier. Each .mdf file is divided into 8 KB chunks, and each chunk is what we call a page. A page begins with a 96-byte header, which stores metadata such as page number, page type, amount of free space remaining in the page, number of the allocation unit which owns the page etc. After the header, the data rows are stored. The location of each row within the page is determined by a row offset. A row offset is a two-byte value that indicates how many bytes from the start of the page the row begins at. Offsets are stored in reverse order at the end of the page, forming what’s often called a row offset array. This design makes it easy for SQL Server to find, insert, or move rows within a page without rewriting the entire page. The structure of the page is depicted on the following figure:

Variable-length and LOB objects often cannot fit into a single page, and since rows cannot span multiple pages, these large values are stored outside the in-row data allocation unit. Instead, they are placed in pages managed by either row-overflow or LOB allocation units.

Pages themselves are grouped into so-called extents, which are the basic units SQL Server uses to manage disk space. Each extent consists of 8 pages, covering a total of 64 KB. Extents can be of two types. Uniform extents where all 8 pages belong to the same object and mixed extents where the pages may belong to different objects. SQL Server tracks extent usage with special allocation maps. The Global Allocation Map (GAM) records whether an extent is free or allocated. The Shared Global Allocation Map (SGAM) identifies mixed extents that still contain free pages.

A deeper discussion of these mechanisms is beyond the scope of this blog post, but you can explore more details in the official Pages and Extents Architecture Guide.

To inspect the structure and content of individual pages, SQL Server provides the DBCC utility. Many of the functions we need are undocumented but well described in community resources.

The first useful command is DBCC IND, which returns page information for a given table. The syntax of the command is as follows:

index_id can be taken from sys.indexes, or -1 shows all indexes and IAMs (Index Allocation Maps) and -2 shows only IAMs.

For our products table:

results into

The first record represents the IAM page, while the second corresponds to the actual data page containing table rows. The key values here are PageFID and PagePID, which we’ll use in the next command: DBCC PAGE.

Another undocumented function of DBCC utility is DBCC PAGE, which dumps the content of the requested page. Before running DBCC PAGE, you must enable trace flag 3604 to send the output to the client:

The syntax of DBCC PAGE command is

where file_number and page_number are the PageFID and PagePID from DBCC IND. print_option controls detail level and can have values 0–3. Adding WITH TABLERESULTS formats the output in tabular form.

For our data page table:

we get

The Page Header section reveals useful metadata such as:

With print_option set to 1 or 3, DBCC PAGE also shows the byte representation of row contents in the DATA section. You can easily identify variable string column there. For brevity only the first row is shown here:

At the end of the output you can also see the offset map, dumped at the same (reverse) order, as written on the disk: