Today we have a guest editorial from Kendra Little as Steve is away on his vacation. This editorial was originally published on Jan 20, 2020.

Every so often, I like to return to the concept of “Write Ahead Logging” (WAL) and write or talk about why understanding it is essential for those who work with relational databases. This isn’t a new concept, but that’s exactly why I go back and write about it: this is an old thing that many folks may not learn about if they tend to learn on the job and don’t have a computer science background (or if they have that background but it’s faded into the recesses of their memory). And when looking at the internet, it’s not always easy to tell which older blog posts and topics are still relevant.

Write-ahead logging is still extremely relevant: it’s a core concept for SQL Server and many other database systems.

The “ahead” in “write-ahead logging” means that SQL Server writes to the transaction log ahead of other database files. When a modification occurs, the change will be made in memory and then written to the transaction log file. If the write to the transaction log file doesn’t complete, the transaction cannot commit.

SQL Server’s database recovery model controls how the write-ahead transaction log is managed in important ways, including how much information is written to the transaction log, and how long data will remain in the transaction log.

There are a few reasons why understanding transaction log basics and recovery models is essential for both database administrators and developers:

• Understanding these concepts is needed to properly implement and maintain working backups and high availability/disaster recovery protection
• Understanding write-ahead logging and recovery models helps you quickly identify risky troubleshooting advice which may cause data loss – essential for DBAs in a high-pressure scenario
• Performance of write operations can vary dramatically with minimal logging, but you need to understand how to get it as well as the implications of your recovery model

If you haven’t yet spent time learning about recovery models and transaction logs in SQL Server, carve out a few hours of time in 2020 to deepen your knowledge on these essential concepts. A good place to start is our Stairway to Transaction Log Management.