» » Pluralsight - SQL Server Logging, Recovery, and the Transaction Log

Information of news
  • Author: kityvn
  • Date: 10-06-2015, 16:45
10-06-2015, 16:45

Pluralsight - SQL Server Logging, Recovery, and the Transaction Log

Category: Tutorials

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.

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

Pluralsight - SQL Server  Logging, Recovery, and the Transaction Log

Pluralsight - SQL Server  Logging, Recovery, and the Transaction Log

Pluralsight - SQL Server  Logging, Recovery, and the Transaction Log

I recommends Buy premimum account for High speed+parallel downloads!





Site BBcode/HTML Code:
Dear visitor, you went to the site as unregistered user.
We recommend you Sign up or Login to website under your name.
Would you like to leave your comment? Please Login to your account to leave comments. Don't have an account? You can create a free account now.

Tag Cloud

archive of news

free html hit counter