Thanks for starting this series, Tony. It will be good to have a complete transaction log reference
Could you clarify this:
Data is modified in the data cache, and the log records to describe the effects of the transaction are created in the log cache. When a transaction is committed, the log records are written to the transaction log, on disk.
I tried running your million row insert within a transaction, and even before the commit the log was at 99.74MB and 93.46% full. This makes sense because the transaction creates such memory pressure that it couldn't be contained by RAM. The log records must be written as the transaction occurs, not at commit time as you claim.
How do log records "describe the effects of the transaction"?
Also, I'd like to see more about how a checkpointed but uncommitted transaction gets rolled back. I always thought that the log contained both before and after images of the row, but you talk about "reversing entries" being generated. I can see how a delete can reverse an insert, but how can an update reverse an update without knowing the old values?
Good luck with the stairway