Advanced SQL Server Transaction Log Analysis
What you’ll learn
By the end of this course you’ll be able to read any SQL Server transaction log.
You’ll also be able to make forensic level analysis of the events in a SQL Server.
The student will need a solid background in SQL Server.
This is an advanced course.
COURSE REVIEWS: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”A very succinct introduction to the SQL Server transaction log. A must for anyone wishing to understand the function of the transaction log and learn how to explore its contents within the space of an hour or two.” — Stylianos”Great” — Richard “The explanation was very simple and easy to understand.” — Mansoor”Very good forensic procedures.” — Thomas”Thanks very much, nice course” — Stanislaw”Great explanation and very representative examples.” — Roman “I am learning a lot about SQL Server Transaction Log Analysis.” — Debora”Concise review of basic SQL premises along with log analysis at current and file levels. Good course.” — Mike H”Excellent material and quick to the point!” — Nick Y. COURSE INTRODUCTION: 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.
Section 1: Introduction and Architecture
Lecture 1 Course Introduction. What’s in this course again?
Lecture 2 The Transaction Log is like a stream of consciousness
Lecture 3 Write Ahead Logging – A Simple Diagram
Lecture 4 Transaction Log Architecture High Level Overview
Lecture 5 Course Downloads
Lecture 6 Summary
Section 2: Spelunking the Transaction Log
Lecture 7 What Really is a Checkpoint?
Lecture 8 Writing Transactions to the Log
Lecture 9 Let’s start using fn_dblog.
Lecture 10 INSERT Logging
Lecture 11 Let’s Get the Page of our INSERT
Lecture 12 Get Actual INSERT Row B-Trees (Indexes)
Lecture 13 Get Actual UPDATED Row B-Tree (Indexes)
Lecture 14 Summary
Section 3: Deep Dives
Lecture 15 Passing Parameters Into fn_dblog
Lecture 16 Truncate VS Delete
Lecture 17 Who Dropped My Object?
Lecture 18 Analyzing the Log Backup
Lecture 17 Analyzing the .bak File
Lecture 20 Summary
Seasoned SQL Server DBAs who want to learn how to read the contents of the transaction log.
Udemy | English | 0h 48m | 275.41 MB
Created by: Mike West