Recent Course Review:
Thanks Mike. Absolutely fantastic. Learnt so much. Clearly presented with excellent use of technology to demonstrate the key points. It covered many aspects of SQL Server not just SQL syntax and gave practical demonstrations of every subject covered. Brilliant. – Mr Dave Morgan
Welcome to Advanced SQL Server Transaction Log Analysis.
In this course, we are going to take a deep dive into analyzing the transaction log.
The SQL Server transaction log contains the history of every action that modified anything in the database.
Before data is written or saved on disk a copy or record of that transaction is first written to the transaction log.
This is part of the D in the ACID properties. Recall that D stands for durability and durability is the ACID property which guarantees that transactions that have committed will survive permanently.
For example, if a flight booking reports that a seat has successfully been booked, then the seat will remain booked even if the system crashes.
This process in SQL Server is accomplished by WAL or Write Ahead Logging.
SQL Server uses a write-ahead log (WAL), which guarantees that no data modifications are written to disk before the associated log record is written to disk.
No matter what you’ve read about non-logged operations the truth is that every user transactions that modifies an internal SQL Server object is logged.
In the course we will learn how WAL works for SQL Srever and some internal architecture on how this is accomplished.
Thanks for your interest in Advanced SQL Server Transaction Log Analysis and we will see you in the course.
What's is this course about?
Let's find out in this lecture.
The transaction log capture what's happening inside SQL Server like it's capturing your thoughts.
Let's take a look at that in this lecture.
What's WAL again?
Let's learn the basics of how a page is written to disk.
In this lecture let's learn how the transaction log is designed.
This will set the stage for the rest of the course.
We will be doing a lot of manual checkpointing in this course.
Let's find what that is.
Let's learn how concurrent transactions are written out to a log file.
In this lesson let's start working with fn_dblog.
In this lesson let's learn how transactions are written to the log.
Additionally, we will try to make some sense of the column information provided.
Let's INSERT one row and look and take a detailed look at that transactions.
Let's take a look at the logging associated with INSERTING a single row into a table.
In this lecture let's learn about the logging associated with a single update statement.
We've been using NULLs as parameters throughout the course.
In this lesson let's learn how to pass LSNs into fn_dblog.
Both of these are fully logged operations.
In this video we'll learn several key points about logging for both commands.
In this lesson let's find out who dropped my object.
In this lecture let's move from analyzing live data to analyzing the information inside a transaction log backup.
We can analyze a .bak file the same way we can a .trn file.
Let's learn how to do that in this lecture.
I've been a production SQL Server DBA most of my career.
I've worked with databases for over two decades. I've worked for or consulted with over 50 different companies as a full time employee or consultant. Fortune 500 as well as several small to mid-size companies. Some include: Georgia Pacific, SunTrust, Reed Construction Data, Building Systems Design, NetCertainty, The Home Shopping Network, SwingVote, Atlanta Gas and Light and Northrup Grumman.
Experience, education and passion
I learn something almost every day. I work with insanely smart people. I'm a voracious learner of all things SQL Server and I'm passionate about sharing what I've learned. My area of concentration is performance tuning. SQL Server is like an exotic sports car, it will run just fine in anyone's hands but put it in the hands of skilled tuner and it will perform like a race car.
Certifications are like college degrees, they are a great starting points to begin learning. I'm a Microsoft Certified Database Administrator (MCDBA), Microsoft Certified System Engineer (MCSE) and Microsoft Certified Trainer (MCT).
Born in Ohio, raised and educated in Pennsylvania, I currently reside in Atlanta with my wife and two children.