Advanced SQL Server Transaction Log Analysis
4.7 (16 ratings)
Instead of using a simple lifetime average, Udemy calculates a course's star rating by considering a number of different factors such as the number of ratings, the age of ratings, and the likelihood of fraudulent ratings.
107 students enrolled
Wishlisted Wishlist

Please confirm that you want to add Advanced SQL Server Transaction Log Analysis to your Wishlist.

Add to Wishlist

Advanced SQL Server Transaction Log Analysis

Deciphering the Contents of the SQL Server Transaction log
4.7 (16 ratings)
Instead of using a simple lifetime average, Udemy calculates a course's star rating by considering a number of different factors such as the number of ratings, the age of ratings, and the likelihood of fraudulent ratings.
107 students enrolled
Created by Mike West
Last updated 1/2017
English
Curiosity Sale
Current price: $10 Original price: $20 Discount: 50% off
30-Day Money-Back Guarantee
Includes:
  • 1 hour on-demand video
  • 4 Articles
  • 1 Supplemental Resource
  • Full lifetime access
  • Access on mobile and TV
  • Certificate of Completion
What Will I 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.
View Curriculum
Requirements
  • The student will need a solid background in SQL Server.
  • This is an advanced course.
Description

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. 

Who is the target audience?
  • Seasoned SQL Server DBAs who want to learn how to read the contents of the transaction log.
Students Who Viewed This Course Also Viewed
Curriculum For This Course
+
Introduction and Architecture
6 Lectures 07:51

What's is this course about? 

Let's find out in this lecture. 

Preview 01:26

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.

Preview 01:48

What's WAL again? 

Let's learn the basics of how a page is written to disk. 

Preview 01:39

In this lecture let's learn how the transaction log is designed. 

This will set the stage for the rest of the course. 

Preview 02:02

Course downloads. 

Course Downloads
00:07

Summary
00:49

Quiz
10 questions
+
Spelunking the Transaction Log
8 Lectures 21:54

We will be doing a lot of manual checkpointing in this course. 

Let's find what that is. 

What Really is a Checkpoint?
01:14

Let's learn how concurrent transactions are written out to a log file. 

Writing Transactions to the Log
01:39

In this lesson let's start working with fn_dblog. 

Let's start using fn_dblog.
05:46

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. 

INSERT Logging
02:41

Let's INSERT one row and look and take a detailed look at that transactions. 

Let's Get the Page of our INSERT
05:20

Let's take a look at the logging associated with INSERTING a single row into a table. 

Get Actual INSERT Row B-Trees (Indexes)
02:34

In this lecture let's learn about the logging associated with a single update statement. 

Get Actual UPDATED Row B-Tree (Indexes)
01:59

Summary
00:41

Quiz
10 questions
+
Deep Dives
6 Lectures 18:04

We've been using NULLs as parameters throughout the course. 

In this lesson let's learn how to pass LSNs into fn_dblog. 

Preview 03:28

Both of these are fully logged operations.

In this video we'll learn several key points about logging for both commands. 

Truncate VS Delete
05:36

In this lesson let's find out who dropped my object. 

Who Dropped My Object?
04:12

In this lecture let's move from analyzing live data to analyzing the information inside a transaction log backup. 

Analyzing the Log Backup
02:32

We can analyze a .bak file the same way we can a .trn file. 

Let's learn how to do that in this lecture. 

Analyzing the .bak File
01:51

Summary
00:25

Quiz
5 questions
About the Instructor
Mike West
4.1 Average rating
2,636 Reviews
43,507 Students
40 Courses
SQL Server and Machine Learning Evangelist

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

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

Personal

Born in Ohio, raised and educated in Pennsylvania, I currently reside in Atlanta with my wife and two children.