理解SQL Server的日志和恢复
理解SQL Server的日志和恢复
--王成辉翻译整理，转贴请注明出自微软BI开拓者
www.windbi.com--
原帖地址SQL Server一些最容易误解的部分是它的日志和恢复机制。事务日志的存在以及如果不正确管理可能引发问题的事实貌似会让很多非自愿的DBA感到困惑。为什么事务日志的无限增长是可能的？为什么在系统崩溃后让数据库online有时要花那么长的时间？为什么日志不能彻底被关掉？为什么我不能正确的恢复我的数据库？事务日志究竟是什么，它们为什么会存在？这是我在SQL Server论坛和新闻组上重复看到的所有问题，所以在这篇文章里，我将提供日志和恢复系统的一个综述，并解释为什么它是SQL Server存储引擎的主要部分。我会解释事务日志的结构，数据库的3种恢复模式是怎样改变事务日志的行为及日志记录过程本身的。其间我也会提供事务日志管理最佳实践相关资源的一些链接。日志是什么？日志和恢复不只是SQL Server才有的概念——所有商业的关系型数据库管理系统（RDBMS）都必须有，用来支持事务的ACID属性。ACID即原子性、一致性、隔离性和永久性，它是事务处理系统（如RDBMS）的基本属性。你可以在ACID Properties section of the MSDN Library这里读多更多的相关信息。从数据库的存储结构方面来说，RDBMS里的操作会在物理层面和逻辑层面记日志（或记录）。存储结构的每个改变都有它自己的日志记录，它描述了被更改的结构以及更改本身是什么。它用这样的方式来实现，即在必要时更改可以重做或者取消。日志记录存储在一个叫事务日志的特定文件里——稍后我将详细描述它，但现在你可以把它考虑成一个顺序存取的文件。
一系列单个或多个这样的更改会（事实上总是这样）用事务组织在一起——它提供了数据库所做更改的一个基本单元（原子性）。事务要么成功（提交）要么失败被取消（回滚）。第一种情况下，来自事务的操作保证会在数据库里得到反映。在第二种情况下，操作保证不会在数据库里得到反映。SQL Server里的事务要么是显示的要么是隐式的。显示事务是用户或应用程序执行了BEGIN TRANSACTION这个T-SQL语句，表明会话的一组相关的更改要开始了。显示事务在执行COMMIT TRANSACTION语句提交成功后，表明一组更改成功结束。如果执行的是ROLLBACK TRANSACTION语句，那个会话从BEGIN TRANSACTION语句以来所做的所有更改会被取消（回滚），事务被忽略。事务也可能被外部事件如数据库磁盘空间不够或者服务器崩溃等强制回滚，稍后我会解释。
隐式事务是用户或应用程序在执行T-SQL语句之前没有明确执行BEGIN TRANSACTION语句。然而，数据库所有的更改都必须是事务型的，存储引擎会暗地里自动启动一个事务。当T-SQL语句完成时，存储引擎自动提交包含在用户语句里启动的事务。你可能会认为这是不必要的，因为单个T-SQL语句不会对数据库的存储结构产生大量的更改，但考虑类似ALTER INDEX REBUILD语句类似的操作。尽管这个语句没有包含在一个显示事务里，但它可能对数据库产生巨大的更改。所以必须有一种机制确保发生故障时（例如，语句被取消），所有的更改都被取消。
作为一个例子，考虑当单个表在隐式事务下被更改的情形。想象一个有整型列c1和char行列c2的堆表。它有10000条记录，一个用户提交了下面的一条update语句：
UPDATE SimpleTable SET c1 = 10 WHERE c2 LIKE '%Paul%';
会发生下面的操作：- SimpleTable表的数据页会从磁盘读到内存（Buffer Pool）里以便能搜索匹配的记录。结果有3个数据库估计5条记录满足WHERE子句。
- 存储引擎自动开始一个隐式事务。
- 3个数据页和这5条记录被锁定允许去更改。
- 在内存中对这3个数据页上的5条数据进行更改。
- 更改也会记录在磁盘上事务日志的日志记录里。
- 存储引擎自动提交隐式事务。
注意我没有列出3个更改完的数据页写回磁盘的步骤。这是因为它们还不必那样去做。只要描述更改的日志记录在磁盘上的事务日志里，那么更改就会得到保护。如果随后页需要再次读取或更改，那么页最新的副本已经存在内存中，还没在磁盘上。当下次checkpoint操作发生时或者如果另外的页要求它们在buffer pool里使用的内存时，数据页才会写会硬盘。Checkpoint存在的2大理由——批量写I/O提升性能以及减少系统崩溃后恢复的时间。就性能而言，如果每当数据页被更改的时候都强制写入磁盘，那么在繁忙的系统上发生的大量写I/O可能很容易就堵塞I/O子系统。周期性的写脏页更好于页更改的时候立即写。我将立刻讨论checkpoint。
对checkpoint一个常见的误解是它们仅写那么已提交的事务里更改的页。这是不对的——checkpoint总是写所有的脏页，不管更改页的事务提交与否。
预写日志是在更改自身写之前描述更改的日记记录写入磁盘的机制。它提供了ACID属性中的持久性。只要描述更改的日志记录在磁盘上，如果发生了崩溃，日志记录（以及因此而产生的更改自身）能被恢复，事务的影响不会丢失。什么是恢复？日志存在支持了SQL Server里各种各样的操作。它确保如果发生崩溃，在崩溃后已提交的事务将正确的反映在数据库里。确保未提交的事务在崩溃后正确的回滚，不反映在数据库里。它确保取消一个执行中的事务成为可能，并回滚它所有的操作。它允许使用事务日志的备份副本以便数据库能被恢复，并且事务日志备份重演以便数据库能到事务一致性的某个特定的时间点。它还支持一些依赖于读取事务日志的特点如复制、数据库镜像以及CDC。
大多数日志的使用包括一种机制叫做recovery（译者注：一般翻译为恢复，而restore有时候也翻译为恢复，不过翻译为还原好些。这2者是不同的）。Recovery（恢复）是让日志记录里描述的更改在数据库里重演或取消的过程。重演日志记录称为恢复的REDO（或前滚）阶段。取消日志记录称为恢复的UNDO（回滚）阶段。换句话说，恢复将确保事务和所有组成它的日志记录要么前滚，要么回滚。
恢复最简单的形式是当单个事务被取消，这时事务会被取消，数据库上没有任何影响。最复杂的形式是从崩溃中恢复——当SQL Server崩溃，事务日志必须恢复以便把数据库带到一个事务一致性的点。这意味着所有在崩溃那一刻已提交的所有事务必须前滚以确保它们的更改在数据库里是永久性的。而所有在崩溃那一刻还在执行没有提交的事务必须回滚以确保它们的更改在数据库里没有影响。
这是因为没有措施使SQL Server里的事务在崩溃后能继续。这样，如果部分完成的事务不被回滚的话，数据库就可能处于不一致的状态（甚至可能结构被破坏，取决于是什么样的事务）。那么恢复怎样知道去做什么呢？所有的恢复过程依赖于每个日志记录都有日志序列号（LSN）的时间戳这一事实。日志序列号是一直递增的，由3部分数字组成，它唯一的定义了事务日志里日志记录的位置。事务里的每条日志记录都按顺序存储在事务日志里，并且包含了事务ID和事务的前一个日志记录的LSN。换句话说作为事务一部分的要记录的每个操作都有一个“链接”指向它之前的操作。
对于单个事务被回滚的简单例子，恢复机制可以很容易很快速地按照日志操作链从最近的操作回到第一个操作并按照相反的顺序回滚操作所做的更改。被事务影响的数据页要么在buffer pool里，要么在磁盘上。不论哪种情况，可用的页的映像被保证是这样：即事务的更改要反映在页上，然后必须回滚。
在崩溃恢复期间，机制就更复杂了。当事务提交而数据库页没写到磁盘的事实意味着磁盘上的一组数据页正确地反映事务日志里说描述的一组改变这个原则没有得到保证——不管对于已提交事务还是未提交事务。然而，还有我还没提及的最终让人迷惑的一点——所有数据页在它们的页头都有一个字段，该字段包含了反映该页最后日志记录的LSN。这允许恢复系统去决定对于必须恢复的特定日志记录要做些什么：- 对于来自已提交事务的日志记录来说，当它们数据页的LSN大于或等于日志记录的LSN时，什么也不需要做。日志记录的更改已经永久的反映到磁盘的页上了。
- 对于来自已提交事务的日志记录来说，当它们数据页的LSN小于日志记录的LSN时，日志记录必须被重做以确保事务更改是永久的。
- 对于来自未提交事务的日志记录来说，当它们数据页的LSN大于或等于日志记录的LSN时，日志记录必须被回滚以确保事务更改不是永久的。
- 对于来自未提交事务的日志记录来说，当它们数据页的LSN小于日志记录的LSN时，什么也不需要做。日志记录的更改没有永久性的写到磁盘的页上，所以就不需要回滚了。
崩溃恢复会读遍事务日志并确保所有已提交事务的所有更改在数据库里得到反映，而所有未提交事务的所有更改在数据库不得到反映——对应于REDO和UNDO过程。一旦崩溃恢复完成，数据库事务上就是一致的了并可用于使用了。
我早起提及的checkpoint操作的用途之一就是减少崩溃恢复所花的时间。通过周期性的把所有的脏数据写到磁盘，因为已提交事务但整个映像还没写到磁盘的已更改的页的数量减少了。这样也就减少额崩溃恢复期间要做REDO恢复的页的数量。事务日志如果事务日志是完整的崩溃恢复才是可能的。事实上，事务日志是数据库最重要的部分——它是万一发生崩溃时必定用来描述数据库所有更改的唯一地方。如果崩溃后事务日志丢失或损坏，那么崩溃恢复不能完成，导致数据库置疑。那样的话，数据库就必须从备份中还原或者使用次可取的方式来恢复比如紧急模式修复。（这些过程已超出本文的范围但会在以后的文章里进行深入的剖析。）事务日志是每个数据库要正确运转所必需的特殊文件。它拥有从登录后产生的日志记录并再次用于在恢复期间进行读取（或者我前面提到的任何其他用途）。加上日志记录本身要占用空间，如果事务被取消并要求回滚的话，事务也会为潜在需要的任何日志记录保留事务日志里的空间。这就是你观察到一个事务更新了50M的数据可能产生100M事务日志空间的原因。
当创建一个新的数据库时，事务日志是空的。随着事务的发生，日志记录会依次写到事务日志里，这意味着创建多个事务日志文件不会得到性能的提升——这是个常见的误解。事务日志将依次使用每个日志文件。并发事务的日志记录会散置在事务日志里。记住单个事务的日志记录是通过它们的LSN链接的，所以没必要把一个事务的所有日志记录在日志里分组放在一块。几乎可以把LSN想像成一个时间戳。
图1显示了事务日志的物理结构。在内部它被分为更小的块，称为虚拟日志文件（VLF）。这样就有简单的方式更容易进行事务日志的内部管理。当一个VLF填满后，日志会自动使用事务日志里的下一个VLF。最后你会认为事务日志会用完空间，但这是事务日志和数据文件不同的地方。.gif)
图1 事务日志的物理结构
事务日志实际上是个循环使用的文件——事务日志开始的那些日志记录会被截断或清除。当日志记录达到事务日志的结尾时，它又会回绕到起点并开始重写以前在那里的日志。那么日志记录怎样被截断以使它们占用的空间得到重用呢？如果满足所有下列条件那么事务日志里的日志记录就不再需要了：- 作为事务日志一部分的事务已被提交。
- 它更改的数据页已经全部被checkpoint写到磁盘上。
- 日志记录对备份（完全备份、差异备份或日志备份）来说不再需要了。
- 对任何其他需要读日志的功能如镜像复制来说日志不再需要。
仍然需要的日志记录称做是活动的，至少有一个活动日志记录的VLF也称为是活动的。事务日志经常会被检查看在一个完整的VLF里的所有日志记录是否是活动的。如果所有都是不活动的，则那个VLF被标记为可截断（意味着一旦事务日志回绕的话VLF能够被重写）。当VLF被截断时，它不会以任何方式被重写或归零——它仅仅被标记为可截断并且以后能够重用。
这个过程称为日志截断——不要和实际收缩事务日志大小相混淆。日志截断从来不会改变事务日志的物理大小——仅仅改变事务日志的那部分是否是活动的。图2显示了图1里截断发生后的事务日志情况。
.gif)
图2 日志截断后的事务日志
活动的VLF组成了逻辑日志——事务日志包含所有活动日志记录的那部分。数据库自身知道崩溃恢复应该从哪里开始读取日志活动部分里的日志记录——起始于日志里最老的活动事务，即MinLSN（这是存储在数据库根页里的）。
崩溃恢复不知道在哪里停止读取日志记录，所以它会一直持续到事务日志的归零部分（如果事务日志还没回绕的话）或者到那些校验位不符合先前日志记录顺序的日志记录。当VLF变为截断的并且新的VLF变为变为活动的时候，事务日志就在物理日志记录文件里移动，并且最后又将回绕到起点，如图3所示。
.gif)
图3 事务日志循环的本性
日志截断是否会发生可以查看下列2个条件之一：- 在SIMPLE恢复模式下checkpoint发生时或者其他恢复模式下做了完全备份。
- 当日志备份完成。
记住日志截断不是必然的，因为有很多原因必须要保持日志记录是活动的。当日志截断不发生时，VLF不被截断，最后事务日志不得不增长（或者添加另一个事务日志文件）。过多的事务日志增长可能通过众所周知的VLF碎片引起性能问题。移除VLF碎片有时可能导致日志相关的活动的性能的显著提升。
有2个常见的因素阻止日志截断：- 长时间运行的活动事务。从第一个日志记录到最先的活动事务的整个事务日志直到事务提交或忽略才被截断。
- 切换到FULL恢复模式，做一个完全备份，然后从来不做任何日志备份。整个事务日志将保持活动，直到做了一个日志备份为止。
恢复模型正如你说看到的，事务日志的行为部分依赖于数据库所使用的恢复模型。有3种恢复模型可用，它们对事务日志的行为有影响，或者对操作是怎样被记录的有影响，或者对二者都有影响。FULL恢复模式意味着每个操作的每一部分都会记录，称为完全记录。一旦在FULL恢复模式下做了数据库的完全备份，事务日志就不会自动截断，直到做了日志备份。如果你不想利用日志备份，又有能力恢复数据库到特点的时间点，那么不要使用FULL恢复模式。然而，如果你要使用数据库镜像，那么别无选择，因为镜像仅在FULL恢复模式下被支持。
BULK_LOGGED恢复模式和FULL恢复模式有相同的事务日志截断规则，不过它允许一些操作部分地记录日志，称为最小日志。例如索引重建和一些大容量导入操作——在FULL恢复模式下整个操作都是要记录日志的。
但是在BULK_LOGGED恢复模式下仅仅记录那些分配单元的更改，这大大减少了日志记录的数量，随之也减少了潜在的事务日志的增长。
最后，SIMPLE恢复模式实际上和BULK_LOGGED恢复模式有着相同的日志记录行为，但是事务日志截断行为是不同的。在SIMPLE恢复模式下日志备份是不可能的，它意味着当checkpoint发生时日志会被截断。
小结本文对SQL Server的运作原理的关键部分实际上做了更加学术性的解释。希望解除了你以前有的一些错误观念。如果这是你第一次看日志和恢复的介绍，这里有一些从本文提炼出来的关键点：- 不要创建多个日志文件，因为它不会引起性能的提升。
- 了解你数据库正在使用的恢复模式以及它们在事务日志上的影响——尤其是当checkpoint发生时事务日志是否会自动截断。
- 了解可能导致事务日志增长的潜在因素，以及如何控制它。
- 当全面诊断一个事务日志时要知道去哪儿找到帮助。
上一篇：理解SQL Server备份
拓狼 最后编辑于 2010-07-05 03:55:44
虽有智慧,不如乘势;虽有鎡基,不如待时。
君子学以聚之,问以辨之,宽以居之,仁以行之。
独学而无友，则孤陋而寡闻。
