SQL Server: Logging, Recovery, and the Transaction Log
Learn everything there is to know about how logging, recovery, and the transaction log work so you can avoid and recover from transaction log problems, applicable for anyone responsible for SQL Server, from SQL Server 2005 onwards
What you'll learn
The logging and recovery mechanism is one of the most misunderstood parts of SQL Server, but it's one of the most critical for you to understand. There are a lot of log management problems you can run into that can cause workload performance problems and even application outages. This comprehensive course, written by someone who has actually worked on the SQL Server logging and recovery code itself, explains everything practical there is to know about how logging and recovery work, and how to avoid and recover from problems with the transaction log. The course follows a building-blocks approach, starting with a simple definition and example of how logging is used, and then covering transaction log architecture and log recorss themselves. Using this knowledge, the course then moves into details of checkpoint operations, general transaction log operations including log clearing and VLF management, and how recovery and crash recovery work. The three recovery models are described in detail, as well as how to create and configure transaction logs for optimal performance, including monitoring transaction log performance. The course ends with a detailed module on transaction log backups, tail-of-the-log-backups, and examining transaction log contents, and then a module on dealing with transaction log corruption and how logging and recovery are used in high-availability technologies. The course is perfect for anyone who has to manage SQL Server and wants to avoid common transaction log problems, as well as those looking for in-depth coverage of everything to do with the transaction log. The information in the course applies to all versions from SQL Server 2005 onwards.
Table of contents
- Introduction 1m
- Basic Terminology 3m
- Why is Logging Required? 2m
- Where are Logging and Recovery Used? 3m
- Where are Logging and Recovery Used? (2) 2m
- Write-Ahead Logging 2m
- Example Data Modification 1m
- First Steps: Reading and Initial Locking 2m
- What It Looks Like: Initial Locking 1m
- Next Steps: Changing and Logging 1m
- What It Looks Like: Modifications 1m
- Next Steps: Committing the Changes 2m
- What It Looks Like: Committing 1m
- The Transaction Has Committed - Now What? 1m
- Summary 2m
- Introduction 1m
- Creating or Growing a Transaction Log File 3m
- Demo: Transaction Log File Zeroing 4m
- Virtual Log Files 2m
- How Many VLFs Do You Get? 2m
- VLF Sequence Numbers 1m
- VLFs and Log Blocks 2m
- VLFs and Log Blocks (2) 2m
- Log Sequence Numbers (LSNs) 2m
- DBCC LOGINFO 2m
- DBCC SQLPERF (LOGSPACE) 1m
- Demo: DBCC LOGINFO 4m
- Summary 1m
- Introduction 2m
- What are Log Records? 3m
- Log Record Contents 2m
- Log Space Reservation 3m
- Log Record Types 3m
- Lock Logging 2m
- Log Records in Transactions 3m
- Examining Log Records 2m
- Demo: Examining Log Records 7m
- Modifying Row Contents 2m
- Modifying Row Contents (2) 1m
- Demo: Modifying Row Contents 6m
- COMPENSATION Log Records 2m
- Rolling Back a Transaction 1m
- Demo: COMPENSATION Log Records 6m
- Rollbacks and Differential Backups 2m
- Log Record Patterns of Interest 2m
- Demo: Table Creation Pattern 6m
- Demo: Index Root Page Creation Pattern 4m
- Demo: Index Page Split Pattern 5m
- Demo: Ghost Records Pattern 3m
- Tempdb Behavior 2m
- Summary 1m
- Introduction 1m
- Why Do Checkpoints Exist? 2m
- Clean vs. Dirty Pages 2m
- Demo: sys.dm_os_buffer_descriptors 5m
- Checkpoint Mechanism 2m
- Checkpoint Mechanism (2) 2m
- Checkpoint and the Log 2m
- Checkpoint Log Records 1m
- Demo: Checkpoint Log Records 3m
- Automatic Checkpoints 2m
- Indirect Checkpoints 2m
- Internal Checkpoints 2m
- Manual Checkpoints 1m
- Checkpoint Monitoring 3m
- Demo: Monitoring Checkpoints 5m
- Tempdb Behavior 2m
- Summary 1m
- Introduction 1m
- Making a VLF Active 2m
- Log Space Reservation 2m
- Moving Through the Transaction Log 1m
- Tracking Uncommitted Transactions 1m
- Demo: Tracking Uncommitted Transactions 3m
- Transaction Log Clearing 3m
- Tracking Transaction Log Space Usage 1m
- Circular Nature of the Transaction Log 1m
- Wrapping with Multiple Files 1m
- Demo: Circular Nature of the Transaction Log 5m
- If the Transaction Log Fills Up... 1m
- Why Did the Transaction Log Fill Up? 3m
- Demo: Full Transaction Log 2m
- Demo: Runaway Transaction Log 5m
- Minimize the Impact of Logging 3m
- Demo: Index Key Column Update 5m
- Demo: The Cost of Page Splits 4m
- Demo: Nested Transactions 3m
- Transaction Log Waits, Latches, and Spinlocks 1m
- Transaction Log Extended Events 2m
- Flushing Log Blocks to Disk 3m
- Transaction Log Writes 2m
- Monitoring Transaction Log I/O 3m
- Demo: Transaction Log I/O Latencies 6m
- Sequential Transaction Log Reads 2m
- Random Transaction Log Reads 2m
- Tuning Transaction Log Throughput 3m
- Tuning the I/O Subsystem 2m
- SQL Server 2014: Delayed Durability 4m
- Demo: Delayed Durability 4m
- Summary 2m
- Introduction 1m
- Recovery 2m
- Redo Portion of Recovery 1m
- Undo Portion of Recovery 2m
- Demo: Undo 4m
- Recovery During Restore 2m
- Recovery for Database Mirroring 2m
- Crash Recovery 3m
- Where Does Recovery Stop? 4m
- Finding the End of the Transaction Log 1m
- Finding the End of the Transaction Log (2) 2m
- Crash Recovery Visualized 3m
- Parallel Crash Recovery 2m
- Demo: Parallel Crash Recovery 5m
- What if Crash Recovery is Interrrupted? 2m
- Crash Recovery and RTO 3m
- Fast Recovery 2m
- Tempdb Behavior 2m
- Summary 2m
- Introduction 1m
- FULL Recovery Model 2m
- Minimal Logging 3m
- Minimally-Logged Operations 1m
- Demo: Efficient vs. Miminal Logging 4m
- BULK_LOGGED Recovery Model 1m
- Log Backups and BULK_LOGGED 2m
- SIMPLE Recovery Model 1m
- Switching Recovery Models 3m
- Demo: Is That Database Really in FULL? 3m
- Demo: Runaway Transaction Log 5m
- Deferred Drop and TRUNCATE TABLE 3m
- Demo: Deferred Drop and TRUNCATE TABLE 3m
- Tempdb Behavior 2m
- Summary 2m
- Introduction 1m
- VLF Fragmentation: Too Many VLFs? 2m
- Survery: Transaction Log File Size vs. VLFs 1m
- Too Few VLFs? 2m
- Tempdb Transaction Log 2m
- VLF Management: Initial File Creation 1m
- Demo: Creating a Transaction Log in Stages 3m
- Log File Provisioning 3m
- Estimating Transaction Log Size 3m
- Configuring Transaction Log Auto-Growth 2m
- Survey: Transaction Log Size Management 1m
- Tracking Transaction Log Space Usage 1m
- Monitoring Transaction Log Health 1m
- Transaction Log File Shrinking 2m
- Removing VLF Fragmentation 2m
- Removing VLF Fragmentation (2) 1m
- Demo: Removing VLF Fragmentation 4m
- Multiple Transaction Log Files 2m
- Demo: Undroppable Transaction Log File 2m
- Summary 1m
- Introduction 1m
- Transaction Log Backups 2m
- Full Database Backup Only Strategy 1m
- Full Database Backup Plus Log Backups Strategy 1m
- Initial Log Backup 2m
- Subsequent Log Backups 1m
- The Transaction Log Eventually Wraps 2m
- Concurrent Log and Data Backups 2m
- Demo: Backups and Log Clearing 5m
- Continuity of the Log Backup Chain 3m
- Preventing Manual Transaction Log Clearing 1m
- Log Backups After Minimally-Logged Operations 2m
- Tail-Of-The-Log Backups 3m
- Demo: Tail-Of-The-Log Backup 3m
- Demo: Tail-of-theLog After Minimally-Logged 5m
- Hack-Attach Tail-Of-The-Log Backups 1m
- Demo: Hack-Attach Tail-Of-The-Log Backup 6m
- Looking Into Log Backups 2m
- Demo: Looking Into Log Backups 7m
- Summary 2m
- Introduction 1m
- Damaged/Missing Log with Clean Shutdown 2m
- Demo: Recreating the Transaction Log 4m
- Damaged/Missing Log without Clean Shutdown 1m
- EMERGENCY Mode 2m
- EMERGENCY-Mode Repair 2m
- Demo: EMERGENCY-Mode Repair 6m
- Damaged/Missing Log of Attached Database 2m
- Database Snapshots 2m
- Reverting From a Database Snapshot 2m
- Demo: Reverting From a Database Snapshot 4m
- Maintaining Synchronous Database Copies 3m
- Principles of Database Mirroring 2m
- Considerations for Database Mirroring and Availability Groups 2m
- Transaction Log in Principal and Mirror 1m
- Log Shipping 3m
- Transactional Replication 2m
- Summary 1m
- Overall Course Summary 1m