SQL Server 中的每个 数据库都有一个事务日志，除数据文件外，还以一个或多个事务日志文件的形式实现。事务日志存储数据库中所做更改的信息，允许 SQL Server 在意外关闭或崩溃时将数据库恢复到事务一致的状态。数据库中的每一次数据修改都会存储在事务日志中，而低事务日志延迟对于良好的系统性能至关重要。
在本章中，我将介绍 SQL Server 如何记录事务以及事务日志的内部工作原理。接下来，我将介绍事务日志配置的几种最佳实践，并讨论如何解决 "事务日志已满 "的情况。最后，我将讨论如何排除事务日志性能不足的故障。
事务日志内部结构
SQL Server 使用事务日志将每个数据库保持在事务一致的状态，这意味着在事务中完成的数据修改必须全部提交或回滚。SQL Server 绝不允许只应用未提交事务中的一个子集更改，从而导致数据在事务上不一致。
每个数据页都会保留修改它的最后一条日志记录的 LSN。在恢复过程中，SQL Server 可以比较日志记录和数据页的 LSN，并找出最近的更改是否保存到了数据文件中。日志记录中存储的信息足以在需要时撤销或重做操作。
SQL Server 使用超前写入日志（WAL），它保证日志记录总是在脏数据页保存到数据库之前写入日志文件。眼尖的读者可能会注意到，在第 3 章中，我提到日志记录是与数据修改同步保存的，而数据页是在检查点过程中异步保存的。虽然从概念上讲这是正确的，但在这里我将说得更精确一些：SQL Server 将日志记录缓存在称为日志缓冲区的小型内存缓存中，分批写入日志记录，以减少日志写入 I/O 操作的次数。
Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month, and much more.
O’Reilly covers everything we've got, with content to help us build a world-class technology community, upgrade the capabilities and competencies of our teams, and improve overall team performance as well as their engagement.
Julian F.
Head of Cybersecurity
I wanted to learn C and C++, but it didn't click for me until I picked up an O'Reilly book. When I went on the O’Reilly platform, I was astonished to find all the books there, plus live events and sandboxes so you could play around with the technology.
Addison B.
Field Engineer
I’ve been on the O’Reilly platform for more than eight years. I use a couple of learning platforms, but I'm on O'Reilly more than anybody else. When you're there, you start learning. I'm never disappointed.
Amir M.
Data Platform Tech Lead
I'm always learning. So when I got on to O'Reilly, I was like a kid in a candy store. There are playlists. There are answers. There's on-demand training. It's worth its weight in gold, in terms of what it allows me to do.
O’Reilly covers everything we've got, with content to help us build a world-class technology community, upgrade the capabilities and competencies of our teams, and improve overall team performance as well as their engagement.
