Have you ever wondered why SQL Server logging works the way it does? While I have long understood the importance behind the SQL Server Transaction Log, I have never actually understood exactly why it works the way it does, despite having attended Paul Randal’s (Blog/Twitter) session at PASS Summit last year. Over the last few months I have been working on writing a book on troubleshooting the most common problems that I see asked on the forums, and as a part of that I have been digging into the way that the SQL Server Transaction Log works.
It’s not really a secret that SQL Server implements the Write-Ahead Logging protocol. This is documented in the Books Online topic, Write-Ahead Transaction Log, but there’s a lot more behind the subject than is actually covered in the BOL topic. If you are up for some interesting reading, I’d recommend grabbing a copy of the “ARIES: A Transaction Recovery method Supporting Fine-Granularity Locking and Partial Rollbacks Using Write-Ahead Logging” paper by Chunder Mohan.