SQL SERVER – Understanding the Basics of Write Ahead Logging (WAL) Protocol

The journey for IT started way back in my college days more than a decade back. It was a booming time and it was exciting to see everyone eyeing the Computer Science stream. Almost every other person in those days always wanted to take this new trend. Riding the waves was one part of excitement, while the other was to be unique in whatever we do. It is easy to get stereotyped with someone and we can never be different from the herd. I always believed in doing the basics right – this blog project that I started more than 8 years was to bring these basic learnings back. I am glad I have been doing this. Let us learn about Write Ahead Logging.

SQL SERVER - Understanding the Basics of Write Ahead Logging (WAL) Protocol background_logs When I was at my hometown, I planned to visit my college and spend some quality time with the students and live some moments back. I always dream of giving time back to the institution that made me who I am now. I think at this moment I request every reader to spend a couple of hours in a year with students from your college. I just wish I had the same exposure in my days back then.

During my last visit, I introduced myself as a database expert (based on what people say). One of the students asked me how durability is achieved in modern databases? Do I know anything about it and how it is done?

This question got me thinking and I immediately said, the first place to search will be my blog. I quickly checked to figure out I haven’t written about this subject. To complete the conversation in the real college style, I took the whiteboard and explained the simple WAL protocol. After a couple of hours of session on various topics, I got out exhausted – after getting out, I thought of writing back here.

The concept of Write Ahead Logging is very common to database systems. This process ensures that no modifications to a database page will be flushed to disk until the associated transaction log records with that modification are written to disk first. Why do we do this? The simple answer is to maintain the ACID properties of a transaction.

What if the database modifications were flushed first and a power failure occurred before the transaction log were written? Well, if the entire transaction was committed and all changes to all pages were also written to disk, it wouldn’t be a problem. But what if the page changes were flushed to disk due to a lazy write to free up buffers and the page changes were part of an active transaction? Transactional consistency would be comprised. The database page on disk will contain changes that are part of an uncommitted transaction (because the log records don’t exist to roll back the change).

This is the reason we write to the Log file first and hence this term is called “Write ahead logging”. Once the transaction gets persisted in the log first and when a power outage happens. The data files and data pages can be appropriately rolled forward (in case of committed transactions) or rolled back (in case of failed/rollback transactions) in the event of abrupt shutdown.

More information on this topic can be found in the SQL Book Online under the topic “Write-Ahead Transaction Log”.

Other sources on this topic include: INF: SQL Server 7.0 and SQL Server 2000 Logging and Data Storage

Reference: Pinal Dave (https://blog.sqlauthority.com)

SQL Log, SQL Server
Previous Post
SQL SERVER – Error while starting SQL Server logFileSize <= BlkToByteOffset ((ULONG)-1)
Next Post
SQL SERVER – How to Identify Locked Table in SQL Server?

Related Posts

2 Comments. Leave new

Leave a Reply