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
More
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
More
Introduction
7m 39s
Log Records
1h 9m
Checkpoints
38m 16s
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.

Learn by doing. Use hands-on exercise files to follow along with the course and practice concepts as you’re watching them. Sign up now to enhance your training.

Start free trial

Course transcripts are the written form of the course audio. You can use transcripts to just read the audio, or to search for terms or phrases to find where they are discussed in the training. Sign up to try them now.

Start free trial

Ask questions, read feedback from other learners and share your input, all in our discussion forums. Sign up now to join our community of learners, course authors and friendly support staff.

Start free trial

You’ve completed the course but did you master it? Test yourself with questions that gauge your understanding of the material and show you what to brush up on. Sign up to get started.

Start free trial
Table of Contents
Introduction
7m 39s
Log Records
1h 9m
Checkpoints
38m 16s
Description
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.
Exercise Files

Learn by doing. Use hands-on exercise files to follow along with the course and practice concepts as you’re watching them. Sign up now to enhance your training.

Start free trial
Transcript

Course transcripts are the written form of the course audio. You can use transcripts to just read the audio, or to search for terms or phrases to find where they are discussed in the training. Sign up to try them now.

Start free trial
Discussion

Ask questions, read feedback from other learners and share your input, all in our discussion forums. Sign up now to join our community of learners, course authors and friendly support staff.

Start free trial
Knowledge Check

You’ve completed the course but did you master it? Test yourself with questions that gauge your understanding of the material and show you what to brush up on. Sign up to get started.

Start free trial
Course info
Level
Intermediate
Rating
(251)
Duration
7h 45m
Released
16 Nov 2012
Course authors

Paul is the CEO of SQLskills.com, a Microsoft Regional Director and a SQL Server MVP. He worked on the SQL Server Storage Engine team at Microsoft from 1999 to 2007. Among other things, he wrote DBCC CHECKDB/repair for SQL Server 2005 and was responsible for the Core Storage Engine during SQL Server 2008 development.

Share course