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
Course info
Rating
(283)
Level
Intermediate
Updated
Nov 16, 2012
Duration
7h 45m
Table of contents
Introduction
Understanding Logging
Transaction Log Architecture
Log Records
Checkpoints
Transaction Log Operations
Recovery and Crash Recovery
Recovery Models and Minimal Logging
Transaction Log Provisioning and Management
Transaction Log Backups
Corruption and Other HA/DR Topics
Description
Course info
Rating
(283)
Level
Intermediate
Updated
Nov 16, 2012
Duration
7h 45m
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.

About the author
About the author

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.

More from the author
More courses by Paul Randal
Section Introduction Transcripts
Section Introduction Transcripts

Understanding Logging
Paul Randal: Hi, this is Paul Randal from SQLskills. com. And I'm recording this course for Pluralsight. This is the understanding logging recovery and the transaction log course for SQL Server and this is module two, understanding logging. Just like in many parts of SQL Server, the logging recovery mechanisms and the transaction log itself are complicated and can be hard to understand without a kind of basic terminology and building blocks. So that's how I'd like to start this course off with this particular module. We're going to look at a basic terminology of what logging recovery are; we're going to discuss why logging is required; we're going to look at where logging and recovery are used inside SQL Server; and I'm going to walk through an example of a data modification to see how the logging mechanism contributes to that data modification being made.

Transaction Log Architecture
Paul Randal: Hi, this is Paul Randal from SQL Skills. com, and I'm recording this course for Puralsight. This is the SQL Server: Understanding Logging, Recovery, and the Transaction Log course, and this is Module 3: Transaction Log Architecture. Introduction. When SQL Server is generating log records to describe changes that have been made to a database, they have to be stored somewhere, and that somewhere is the transaction log. The transaction log comprises usually just one file, sometimes multiple files, but usually just one file, and understanding how the architecture of the transaction log works is really critical to understand how the logging mechanism overall works. So in this module what we're going to discuss is first of all the physical architecture of the transaction log, the parts that make up the transaction log file itself, a very important concept called Log Sequence Numbers, more commonly known as LSNs, and then a couple of DBCC commands to get information about the log, including the undocumented DBCCLOGINFO command.

Log Records
Paul Randal: Hi, this is Paul Randal from SQLskills. com and I'm recording this course for Pluralsight. This is the SQL Server: Understanding Logging, Recovery, and the Transaction Log course, and this is Module 4 where we're going to talk about Log Records. Introduction; so we've talked about logging and where it's used. We've talked a little bit about recovery and we've talked about the physical architecture of the transactional log. Log records themselves are right at the core of the logging and recovery mechanisms. These are what are storing the details of what changes are to the database. Now although you don't need to understand log record details to understand what's going on with logging and recover. It's well worth getting a little bit of knowledge about what's being stored. And they're also extremely interesting. I actually spent several years working on log records and understanding the various patterns that are generated by SQL Server while I was writing parts of DBCC CHECKDB for SQL Server 2000, because in SQL Server 2000, DBCC CHECKDB used to actually run crash recovery inside itself to figure out a transactionally consistent view of the database. In this module then, we're going to talk about the contents of log records, the different kinds of log records, and how they kind of string together in transactions. I'm going to show you how to look at log records using the FNDB Log Table Valued Function. We're going to talk about a special kind of log record called Compensation Log Records that are generated during transaction rollbacks and we're going to look at a few patterns that exist inside the transactional log from common operations like creating a table or doing a page split.

Checkpoints
Hi. This is Paul Randal from SQLSkills. com and I'm recording this course for Pluralsight. This is the SQL Server: Understanding logging, recovery and the transaction log course. And this is module 5, Checkpoints. Introduction. We've talked about logging recovery and we've talked about log records. Logging is what writes the description of changes to a database to disk. The actual data file pages, though, are written to disk at a later point. They're not written when the transaction commits. They're written by operations called checkpoints. Occasionally, however, the buffer pool might be under memory pressure and so it might have to forcibly write data file pages out to disk before it's time for a checkpoint to occur. And this is done by a background process called the lazywriter. But in this module, we're going to talk about checkpoints. We'll discuss what checkpoints actually are and how they work. We'll talk about the log records generated when a checkpoint actually occurs. We'll look at the four different kinds of checkpoints there are inside SQL Server. And we'll discuss how you can monitor checkpoints.

Transaction Log Operations
Hi this is Paul Randal from SQLskills. com, and I am recording this course for Pluralsight. This is the SQL Server: Understanding Logging Recovery, and the Transaction Log course, and this is Module 6: Transaction Log Operations. Introduction. Now that we've talked a lot about log architecture, log records, and things like check-points, it's good for us to move on to how the transaction log actually operates because this is going to allow you to troubleshoot problems when they occur and figure out what to do to alleviate the problems you're experiencing with the transaction log. In this module, and it's quite a big one, we're going to discuss VLF management and how the transaction log likes to be circular in nature, we're going to discuss transaction log space and log space reservation, we're going to discuss the log clearing mechanism, one of the points that there are many, many misconceptions around in the world, we're going to talk about why the transaction log can fill up and what you can do about it, we're going to talk about log reads, writes, and log block flushing, and finally we're going to talk about tuning the transaction log throughput, so an awful lot of stuff to cover in this module. It's going to pull a lot of stuff together that we've talked about in previous modules.

Recovery and Crash Recovery
Paul Randal: Hi this is Paul Randal with SQlSkills. com and I'm recording this course for Pluralsight. This is the SQL Server Understanding, Logging, Recovery and the transaction log course and this is module seven, Recovery and Crash Recovery. Introduction. As we've talked about several times now, one of the most important uses of logging is to enable recovery to work especially crash recovery after an unexpected system crash or even a recovery after a restore sequence is finished or after, say, a database mirroring failover or an availability group replica failover. The whole point of being able to log changes is so that transactions are durable and to be able to make sure the uncommitted transactions, at the time of a crash, are not present in the database in any way whatsoever. So in this module we're going to talk about the recovery mechanism itself and how it works, where recovery starts and where recovery ends, how SQL Server discovers where the end of the transaction log is.

Recovery Models and Minimal Logging
Hi, this is Paul Randal with SQLskills. com and I'm recording this course for Pluralsight. This is the SQL Server: Understanding Logging, Recovery, and the Transaction Log course. Now, this is Module 8: Recovery Models and Minimal Logging. Introduction. There are three recovery models in SQL Server and they change how the transaction log behaves and how some operations are logged. Specifically, some operations become minimally-logged. There are, however, lots and lots of misconceptions about how these three recovery models work and their effect on SQL Server. So in this module, we're going to discuss the three different recovery models: FULL, BULK_LOGGED and SIMPLE. Efficient versus minimal logging. Switching recovery models between the various recovery models and how that breaks or doesn't break the log backup chain. We're also going to talk about the very persistent myth that the TRUNCATE TABLE command is non-logged. It's actually not. It's fully-logged but very efficiently-logged.

Transaction Log Provisioning and Management
Hi, this is Paul Randal with SQLskills. com. And I'm recording this course for Pluralsight. This is the SQL Server: Understanding Logging, Recovery, and the Transaction Log course and this is Module 9, Transaction Log Provisioning and Management. Introduction. In this module, we're going to talk about transaction log provisioning. Anytime you create a new database, you want to make sure that your transaction log is properly sized. You also want to make sure that it's created in the correct way to avoid performance problems. You're going to have to set up auto-growth and make sure that autoshrink's not turned on. You're going to have to monitor the transaction log to make sure you're not seeing any problems as well. So in this module, we're going to discuss transaction log provisioning and configuration, transaction log growth and shrinking, what they mean and when you might want to do them, VLF fragmentation, what that means and how you can get rid of it.

Transaction Log Backups
Hi, this is Paul Randal from SQLskills. com and I'm recording this course for Pluralsight. This is the SQL Server: Understanding Logging, Recovery, and the Transaction Log course and this is Module 10: Transaction Log Backups. Introduction. In most backup strategies that I've seen, transaction log backups are extremely important an integral part. Transaction log backups allow you to recover right up to the point of a crash with a minimum or zero data loss. It's really important therefore that you understand how they work and what some of the lesser-known tricks are as these can really help when a disaster occurs. In this module, we're going to talk about transaction log backups and what they are and how they affect the transaction log itself. We'll discuss the log backup chain and if it gets broken, how you can restart it. We'll talk about the special case of tail-of-the-log backups in a variety of situations and I'll also show you how you can examine information in the transaction log that are inside transaction log backups.

Corruption and Other HA/DR Topics
Hi, this is Paul Randal with SQLskills. com. I am recording this course for Pluralsight. This is the SQL Server: Understanding Logging, Recovery, and the Transaction Log course. And this is module 11, the final module. And we're going to talk about corruption and other high availability and disaster recovery topics. Introduction, we've talked about nearly everything to do with logging and recovery so far in this course. One of the things that we haven't talked about is how log and recovery are used in the various high availability technologies that SQL Server has. As long as everything is running fine there's no problems, but what happens if the log becomes damaged or lost or corrupt in some way, how do you cope? There's a variety of different scenarios that you'll have to cope with. And we're going to talk about what your options are for each of those in this module. Now, it's worth saying that almost all the cases of damage through transaction logs or data files for that matter that I've seen over the years have been from a fault in the I/O sub system rather than SQL Server itself. So, corruption and transaction log damage can occur. And you'll be a hero in your workplace if you know what to do when transaction logs are damaged and how to recover from them. In this module then we're going to talk about dealing with a damaged or missing transaction log. And we're also going to talk about the various high availability technologies that make use of log and recovery in SQL Server.