—Introduction
2m 18s
—Problems From Misconceptions
3m 17s
—Course Structure
2m 2s
Understanding Logging
- 26m 21s
—Introduction
0m 51s
—Basic Terminology
2m 53s
—Why is Logging Required?
2m 24s
—Where are Logging and Recovery Used?
3m 21s
—Where are Logging and Recovery Used? (2)
1m 52s
—Write-Ahead Logging
2m 23s
—Example Data Modification
1m 15s
—First Steps: Reading and Initial Locking
2m 2s
—What It Looks Like: Initial Locking
1m 12s
—Next Steps: Changing and Logging
1m 6s
—What It Looks Like: Modifications
0m 58s
—Next Steps: Committing the Changes
1m 48s
—What It Looks Like: Committing
0m 52s
—The Transaction Has Committed - Now What?
1m 28s
—Summary
1m 49s
Transaction Log Architecture
- 26m 48s
—Introduction
1m 0s
—Creating or Growing a Transaction Log File
2m 43s
—Demo: Transaction Log File Zeroing
3m 48s
—Virtual Log Files
1m 45s
—How Many VLFs Do You Get?
1m 40s
—VLF Sequence Numbers
1m 27s
—VLFs and Log Blocks
1m 37s
—VLFs and Log Blocks (2)
2m 5s
—Log Sequence Numbers (LSNs)
1m 56s
—DBCC LOGINFO
2m 15s
—DBCC SQLPERF (LOGSPACE)
0m 39s
—Demo: DBCC LOGINFO
4m 20s
—Summary
1m 27s
—Introduction
1m 37s
—What are Log Records?
2m 32s
—Log Record Contents
2m 27s
—Log Space Reservation
2m 51s
—Log Record Types
2m 35s
—Lock Logging
1m 30s
—Log Records in Transactions
2m 48s
—Examining Log Records
1m 54s
—Demo: Examining Log Records
6m 58s
—Modifying Row Contents
2m 14s
—Modifying Row Contents (2)
1m 5s
—Demo: Modifying Row Contents
5m 56s
—COMPENSATION Log Records
2m 29s
—Rolling Back a Transaction
1m 24s
—Demo: COMPENSATION Log Records
5m 43s
—Rollbacks and Differential Backups
1m 51s
—Log Record Patterns of Interest
1m 51s
—Demo: Table Creation Pattern
5m 48s
—Demo: Index Root Page Creation Pattern
3m 57s
—Demo: Index Page Split Pattern
5m 23s
—Demo: Ghost Records Pattern
2m 50s
—Tempdb Behavior
2m 6s
—Summary
1m 17s
—Introduction
1m 2s
—Why Do Checkpoints Exist?
1m 47s
—Clean vs. Dirty Pages
1m 50s
—Demo: sys.dm_os_buffer_descriptors
4m 59s
—Checkpoint Mechanism
1m 59s
—Checkpoint Mechanism (2)
2m 0s
—Checkpoint and the Log
2m 21s
—Checkpoint Log Records
1m 29s
—Demo: Checkpoint Log Records
2m 52s
—Automatic Checkpoints
2m 10s
—Indirect Checkpoints
1m 30s
—Internal Checkpoints
1m 57s
—Manual Checkpoints
1m 18s
—Checkpoint Monitoring
2m 44s
—Demo: Monitoring Checkpoints
4m 38s
—Tempdb Behavior
2m 6s
—Summary
1m 27s
Transaction Log Operations
- 1h 24m
—Introduction
1m 11s
—Making a VLF Active
2m 2s
—Log Space Reservation
2m 25s
—Moving Through the Transaction Log
1m 4s
—Tracking Uncommitted Transactions
1m 16s
—Demo: Tracking Uncommitted Transactions
3m 22s
—Transaction Log Clearing
2m 32s
—Tracking Transaction Log Space Usage
1m 4s
—Circular Nature of the Transaction Log
1m 27s
—Wrapping with Multiple Files
0m 46s
—Demo: Circular Nature of the Transaction Log
4m 43s
—If the Transaction Log Fills Up...
1m 23s
—Why Did the Transaction Log Fill Up?
2m 57s
—Demo: Full Transaction Log
2m 19s
—Demo: Runaway Transaction Log
5m 11s
—Minimize the Impact of Logging
2m 49s
—Demo: Index Key Column Update
5m 25s
—Demo: The Cost of Page Splits
3m 35s
—Demo: Nested Transactions
2m 58s
—Transaction Log Waits, Latches, and Spinlocks
1m 23s
—Transaction Log Extended Events
1m 41s
—Flushing Log Blocks to Disk
2m 44s
—Transaction Log Writes
2m 4s
—Monitoring Transaction Log I/O
2m 53s
—Demo: Transaction Log I/O Latencies
5m 52s
—Sequential Transaction Log Reads
1m 35s
—Random Transaction Log Reads
1m 44s
—Tuning Transaction Log Throughput
3m 14s
—Tuning the I/O Subsystem
1m 52s
—SQL Server 2014: Delayed Durability
4m 18s
—Demo: Delayed Durability
4m 9s
—Summary
1m 53s
Recovery and Crash Recovery
- 44m 10s
—Introduction
0m 58s
—Recovery
2m 21s
—Redo Portion of Recovery
1m 24s
—Undo Portion of Recovery
1m 57s
—Demo: Undo
3m 30s
—Recovery During Restore
2m 14s
—Recovery for Database Mirroring
1m 30s
—Crash Recovery
2m 55s
—Where Does Recovery Stop?
3m 38s
—Finding the End of the Transaction Log
1m 1s
—Finding the End of the Transaction Log (2)
2m 14s
—Crash Recovery Visualized
2m 57s
—Parallel Crash Recovery
2m 15s
—Demo: Parallel Crash Recovery
4m 56s
—What if Crash Recovery is Interrrupted?
2m 10s
—Crash Recovery and RTO
2m 31s
—Fast Recovery
1m 54s
—Tempdb Behavior
2m 6s
—Summary
1m 31s
Recovery Models and Minimal Logging
- 36m 35s
—Introduction
0m 59s
—FULL Recovery Model
2m 5s
—Minimal Logging
2m 51s
—Minimally-Logged Operations
1m 27s
—Demo: Efficient vs. Miminal Logging
3m 44s
—BULK_LOGGED Recovery Model
1m 19s
—Log Backups and BULK_LOGGED
1m 56s
—SIMPLE Recovery Model
1m 18s
—Switching Recovery Models
2m 33s
—Demo: Is That Database Really in FULL?
3m 12s
—Demo: Runaway Transaction Log
5m 11s
—Deferred Drop and TRUNCATE TABLE
2m 54s
—Demo: Deferred Drop and TRUNCATE TABLE
3m 23s
—Tempdb Behavior
2m 6s
—Summary
1m 30s
Transaction Log Provisioning and Management
- 40m 35s
—Introduction
0m 52s
—VLF Fragmentation: Too Many VLFs?
2m 19s
—Survery: Transaction Log File Size vs. VLFs
1m 16s
—Too Few VLFs?
1m 45s
—Tempdb Transaction Log
2m 28s
—VLF Management: Initial File Creation
1m 25s
—Demo: Creating a Transaction Log in Stages
3m 0s
—Log File Provisioning
3m 16s
—Estimating Transaction Log Size
3m 0s
—Configuring Transaction Log Auto-Growth
2m 6s
—Survey: Transaction Log Size Management
1m 19s
—Tracking Transaction Log Space Usage
1m 4s
—Monitoring Transaction Log Health
1m 22s
—Transaction Log File Shrinking
2m 16s
—Removing VLF Fragmentation
1m 50s
—Removing VLF Fragmentation (2)
1m 19s
—Demo: Removing VLF Fragmentation
4m 7s
—Multiple Transaction Log Files
1m 46s
—Demo: Undroppable Transaction Log File
2m 28s
—Summary
1m 28s
Transaction Log Backups
- 48m 46s
—Introduction
1m 0s
—Transaction Log Backups
1m 44s
—Full Database Backup Only Strategy
0m 52s
—Full Database Backup Plus Log Backups Strategy
1m 24s
—Initial Log Backup
1m 34s
—Subsequent Log Backups
1m 19s
—The Transaction Log Eventually Wraps
1m 43s
—Concurrent Log and Data Backups
2m 1s
—Demo: Backups and Log Clearing
4m 37s
—Continuity of the Log Backup Chain
2m 37s
—Preventing Manual Transaction Log Clearing
1m 4s
—Log Backups After Minimally-Logged Operations
1m 46s
—Tail-Of-The-Log Backups
2m 33s
—Demo: Tail-Of-The-Log Backup
2m 39s
—Demo: Tail-of-theLog After Minimally-Logged
4m 38s
—Hack-Attach Tail-Of-The-Log Backups
1m 19s
—Demo: Hack-Attach Tail-Of-The-Log Backup
5m 45s
—Looking Into Log Backups
1m 38s
—Demo: Looking Into Log Backups
6m 48s
—Summary
1m 35s
Corruption and Other HA/DR Topics
- 42m 43s
—Introduction
1m 20s
—Damaged/Missing Log with Clean Shutdown
1m 34s
—Demo: Recreating the Transaction Log
4m 8s
—Damaged/Missing Log without Clean Shutdown
1m 3s
—EMERGENCY Mode
1m 38s
—EMERGENCY-Mode Repair
2m 0s
—Demo: EMERGENCY-Mode Repair
5m 45s
—Damaged/Missing Log of Attached Database
2m 18s
—Database Snapshots
1m 59s
—Reverting From a Database Snapshot
1m 43s
—Demo: Reverting From a Database Snapshot
4m 10s
—Maintaining Synchronous Database Copies
3m 20s
—Principles of Database Mirroring
1m 45s
—Considerations for Database Mirroring and Availability Groups
1m 48s
—Transaction Log in Principal and Mirror
1m 24s
—Log Shipping
2m 34s
—Transactional Replication
1m 46s
—Summary
1m 3s
—Overall Course Summary
1m 14s