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

Authored by: Paul Randal
Duration: 7h 36m
Level: Intermediate
Released: 11/16/2012
Features: Closed captions

Course Rating:

You are currently not signed in. Please sign in to access subscriber-only content.

Currently using: {{getCurrentPlayerName()}} [Change]

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

SQL Server: Logging, Recovery, and the Transaction Log

Categories

Most Popular

New Releases

Products

Support

Features

About

Community