SQL Server Page Structure and Transaction Log Architecture Explained

Lesson 1 covered how SQL Server manages memory and CPU. This lesson covers the physical layer underneath both: how data is actually laid out on disk, and how every single change — no matter how it got there — is guaranteed durable before it’s ever considered committed.

Pages and Extents

This 8KB figure isn’t arbitrary trivia — it’s the exact unit Chapter 4’s STATISTICS IO “logical reads” count is measured in. When that number reports 500 logical reads, it means 500 8KB pages were touched — tying this architecture lesson directly to a diagnostic number you’ll read constantly for the rest of this course.

The Transaction Log: Write-Ahead Logging

-- Check log space usage — a classic "why is my log huge" starting point
DBCC SQLPERF(LOGSPACE);

-- Check log file growth/autogrowth settings
SELECT name, size/128 AS size_mb, growth, is_percent_growth
FROM sys.database_files
WHERE type_desc = 'LOG';

SQL Server uses Write-Ahead Logging (WAL): a change is written to the transaction log before the data page itself is modified on disk, and a transaction is only considered committed once its log record is durably written. This is literally how Durability (the “D” in ACID from the Developers & DBAs course) is implemented — not a separate feature, but the mechanism underneath it.

Why Log Architecture Explains Real Symptoms

A transaction log that won’t shrink, a database stuck in “log full” errors during a bulk load, a mysteriously slow bulk delete — these all trace back to how logging works: every logged operation, including large deletes, must be written to the log before it’s considered durable, row by row. A DELETE affecting a million rows generates roughly a million log records, regardless of how fast the actual data-page changes would otherwise be.