WAL: The concept that makes recovery models & backups make sense (Dear SQL DBA Episode 32)

When you’re a Junior DBA, it’s really hard to take in all the information out there. Learn about write ahead logging: the concept that can help you make sense of recovery models and backup strategies in SQL Server. This is a foundational concept that can help you understand how SQL Server works, and how to take care of a SQL Server instance.

In this week’s episode, I tell the story of the “Wow!” moment when I learned about write ahead logging from Kimberly Tripp of SQLSkills.com way back when I was a Junior DBA. I give my own take on how write ahead logging works in SQL Server.

Predictably, my version has goofy drawings.

Watch this week’s 18 minute episode, and subscribe to my YouTube channel if you’d like to see more. You can also get future episodes in the audio podcast if you like to listen on the go.

Note: I do some storytelling at the beginning of this episode. The tech content starts 8 minutes in if you’d like to skip straight to that part.

Further reading

Junior DBA Training Plan: https://www.littlekendra.com/2016/03/29/training-plan-for-junior-dbas-learning-sql-server/

Learn about the Write Ahead Transaction Log on TechNet: https://technet.microsoft.com/en-us/library/ms186259.aspx

8 Comments. Leave new

  • […] I explain write ahead logging and talk about how I learned this back when I was a Junior DBA in episode 32 of Dear SQL DBA […]

  • Great topic this week. I think there is always something in this area that even seasoned DBAs can learn.

    To follow up on your resources, Gail Shaw’s book is free for download from the Red gate site: http://www.red-gate.com/library/sql-server-transaction-log-management and I found it to be very helpful in learning about WAL and VLFs etc.

    • Very cool!

      I see she co-wrote that with Tony Davis — they did the stairway together. I’m going to have to mention him in the next show, he does such great writing and I wish I’d mentioned his name as well.

  • Hi Kendra,

    wow, another great topic in your podcast! I once learned about WAL a long long time ago in a database fundamentals lecture at university…nowadays it makes more sense to learn this again and combine it with things I already know about SQL Server.

    During backups and service restarts I often see messages in the sql server log saying “10 transactions rolled forward in database ….” or “10 transactions rolled back in database …”. Given that transactions are written to the transaction log first and then to the data file….what does this message mean exactly? Originally I thought that would be content in the data file to be corrected…but now I think that this is wrong as CHECKPOINT only writes out data from completed transactions (does it?). I remember something like “REDO/UNDO” in combination with the transaction log…could you help me getting the terms clear? Where does the roll forward and roll back actually happen? I would guess in the transaction log itself but don’t really understand why this is necessary.

    Sorry for throwing out that kind of confused question at you…I am totally happy to sort it out myself if you could give me a little hint where to look at (the stairway? the book? MSDN?).

    Have a great time ahead in Portugal (Lisbon is great but Algarve Coast even better 🙂 )



Share a Comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: