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 Responses to WAL: The concept that makes recovery models & backups make sense (Dear SQL DBA Episode 32)

  1. Jeff March 3, 2017 at 7:11 am #

    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.

    • Kendra Little March 3, 2017 at 7:14 am #

      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.

  2. Martin Guth March 7, 2017 at 12:06 am #

    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 🙂 )

    Martin

    • Martin Guth March 7, 2017 at 12:14 am #

      Sorry…the message of the roll forward and roll backs happens during CHECKDB not during backups…. I confused that over another because checkdb is part of my daily backup job.

Trackbacks/Pingbacks

  1. Training Plan for Junior DBAs Learning SQL Server - by Kendra Little - March 2, 2017

    […] 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 […]

Leave a Reply