第十五周翻译

  本文是楼梯系列的一部分：SQL Server中事务日志管理的阶梯.当事情进展顺利时，不需要特别意识到事务日志是如何工作的。您只需要确信每个数据库都有正确的备份机制。当事情出错时，对事务日志的理解对于采取纠正措施是很重要的，特别是当需要对数据库进行实时恢复时。托尼·戴维斯给出了每个DBA都应该知道的正确的细节水平。

第1级：事务日志概述

事务日志是SQLServer存储与日志文件关联的数据库上执行的所有事务和数据修改的记录的文件。如果发生导致SQLServer意外关闭(例如实例或硬件故障)的灾难，则使用事务日志恢复数据库，并保持数据完整性。重新启动后，数据库进入读取事务日志的恢复过程，以确保将所有有效的已提交数据写入数据文件(前滚)，并撤消(回滚)任何部分未提交事务的影响。简而言之，事务日志是SQL Server确保数据库完整性和事务的ACID属性(特别是持久性)的基本手段。

DBA在管理事务日志方面的一些最重要职责如下：

选择正确的恢复模型-SQLServer提供三种数据库恢复模型：完全(默认)、简单和批量日志。DBA必须根据数据库的业务需求选择适当的模型，然后建立适合于该模式的维护过程。

·执行事务日志备份-除非在简单模式下工作，否则DBA执行事务日志的定期备份至关重要。一旦在备份文件中捕获了日志记录，就可以将日志记录应用到完整的数据库备份中，以便执行数据库恢复，从而重新创建以前存在的数据库，例如在失败之前。

·监视和管理日志增长-在繁忙的数据库中，事务日志的大小可以迅速增长。如果没有定期备份，或者大小不适当，或者分配了不正确的增长特征，事务日志文件就会被填满，从而导致臭名昭著的“9002”(事务日志完整)错误，从而使SQLServer成为“只读”m。

·优化日志吞吐量和可用性——除了基本的维护（如备份）之外，DBA必须采取措施确保事务日志的充分性能。这包括硬件考虑，以及避免诸如日志碎片这样的情况，这会影响事务的性能。

在此楼梯系列中，我们将详细考虑这些核心维护任务中的每一个。

在此，在第一级，我们将首先概述SQLServer如何使用事务日志，以及它影响DBA的寿命的两种最重要的方法，即数据库恢复和恢复以及磁盘空间管理。

SQLServer如何使用事务日志

  在SQLServer中，事务日志是一个物理文件，通常由扩展LDF标识，尽管不是强制的。它是在创建数据库时自动创建的，以及通常由MDF扩展标识的主数据文件，不过也可以使用任何扩展来存储数据库对象和数据本身。事务日志虽然通常实现为单个物理文件，但也可以实现为一组文件。但是，即使在后一种情况下，SQL Server仍然将其视为一个顺序文件，因此，SQLServer不能也不与多个日志文件并行写入，因此将事务日志实现为多个文件没有任何性能优势。在第7级中将更详细地讨论这一点-调整和增长事务日志。

  每当T-SQL代码对数据库对象(DDL)或它包含的数据进行更改时，不仅在数据文件中更新数据或对象，而且在事务日志中将更改的细节记录为日志记录。每个日志记录都包含有关执行更改的事务的ID、事务启动和结束时、更改了哪些页面、所做的数据更改等详细信息。

  注意：事务日志不是审计跟踪。它不提供对数据库所做更改的审计跟踪；它不保存对数据库执行的命令的记录，只记录数据如何因此发生变化。

   当进行数据修改时，希望相关数据页从数据缓存中读取，或者如果数据页尚未在缓存中，则首先从磁盘中检索。在数据缓存中修改数据，在日志缓存中创建用于描述事务效果的日志记录。提交事务时，日志记录将写入磁盘上的事务日志。但是，在稍后发生数据库检查点之前，可能不会将实际更改的数据写入磁盘。缓存中自从磁盘读取以来已修改的任何页，因此缓存中的数据值与磁盘上的数据值不同，因此称为脏页。这些脏页可能包含以下两种内容：•已提交并“硬化”到事务日志文件但尚未提交到数据文件的数据由开放事务修改的数据，即尚未提交(或回滚)的数据

  定期数据库检查点进程扫描数据缓存并将所有脏页刷新到磁盘，此时修改反映在物理数据文件和日志文件中。即使在事务仍然打开的情况下也会发生这种情况；在检查点期间，与打开的事务相关的脏页被刷新到磁盘，SQLServer始终确保在脏页被刷新到数据文件之前，将与这些打开的事务相关的日志记录从日志缓存刷新到事务日志文件。

  注意：另一个扫描数据缓存的进程-LazyWriter-也可能会将脏数据页写入检查点之外的磁盘，如果由于内存压力而被迫这样做的话。

  这里要注意的一点是，日志缓冲区管理器总是保证在将数据页写入物理数据文件之前，将更改描述（日志记录）写入磁盘上的事务日志。这种机制称为提前写入日志记录。它本质上是SQL Server确保事务持久性的机制（参见数据库事务的酸性属性）。 

  通过始终首先写入日志文件的更改，SQL Server有一种机制的基础，这种机制可以保证所有已提交事务的影响最终都会反映在数据文件中，并且磁盘上任何源自不完全事务的数据修改，即那些既没有发出COMMIT也没有发出回滚的事务，最终也不会反映在数据文件中。

  如果数据库崩溃，例如，在提交特定事务(T1)之后，但在受影响的数据写入数据文件之前，则在重新启动时，启动数据库恢复过程，试图协调事务日志文件和数据文件的内容。它将读取事务日志文件，并确保包含事务T1(记录在日志文件中)的所有操作都“前滚”(重做)，以便将它们反映在数据文件中。

  同样，数据库崩溃后，恢复过程将“回滚”(撤消)与未提交事务关联的数据库中的任何数据更改，方法是从日志文件中读取相关操作，并对数据执行反向物理操作。

  通过这种方式，如果发生崩溃，SQLServer可以将数据库返回到一致状态。更普遍地说，回滚(撤消)进程发生在以下情况：·为显式事务发出ROLLBACK命令 ·发生错误并打开XAC_ABORT

  ·如果数据库检测到数据库与发起交易的客户之间的通信中断。

 在这种情况下，读取与中断事务有关的日志记录，或显式发出ROLLBACK命令的日志记录，并回滚更改。通过这些方法，SQLServer可以确保与事务关联的所有操作作为一个单元获得成功，或者它们都失败。因此，事务日志是SQL Server在日常操作中确保数据一致性和完整性的基本手段之一。

   然而，事务日志发挥着另一个至关重要的作用，因为它提供了一种机制，可以在发生灾难时将数据库恢复到以前的时间点。通过适当的计划和管理，您可以使用这些日志文件的备份来恢复所有数据，直到数据损坏或无法使用为止。

   事务日志和数据库恢复

  如前所述，事务日志文件存储一系列日志记录，按事务启动的时间顺序排列，这将提供针对该数据库发出的修改和事务的历史记录。每个日志记录都包含有关执行更改的事务的ID、事务启动和结束时、更改了哪些页面、所做的数据更改等详细信息。事务日志文件中的日志记录被组织成多个部分，称为VirtualLogFiles(VLFS)-在第2级事务日志体系结构中详细介绍了这些部分。

 SQLServer的预写日志机制保证修改的描述(即日志记录)在被修改的数据本身写入数据文件之前写入VLF。因此，日志记录可能包含关闭(已提交)事务或打开(未提交)事务的详细信息，而且在每种情况下，事务修改的数据可能已经写入，也可能没有写入数据文件，这取决于是否发生了检查点。

  注意：通过定期将脏页从缓存刷新到磁盘，数据库检查点进程控制SQLServer在数据库恢复操作期间需要完成的工作量。如果SQLServer必须为大量提交的与脏页相关的事务进行前滚更改，那么恢复过程可能会非常漫长。

  在恢复过程中，任何与打开的事务相关的日志记录都可能是回滚操作所必需的，并且始终是所谓的活动VLF的一部分，并且将始终保留在日志文件中。与封闭事务相关的日志记录也将是活动VLF的一部分，直到到达与开放事务关联的整个VLF中没有日志记录的点，此时VLF变为非活动。

  这些非活动VLF中的日志记录本质上提供了先前完成的数据库事务的“历史”，并且这些不活动的VLFs发生了什么变化取决于数据库的恢复模型。我们将在这个楼梯的3到6级中详细讨论这些恢复模型，但是这里的关键点是，如果您正在使用完整的（或大容量日志）数据库恢复模型，那么事务日志将日志记录保留在不活动的VLFs中，直到日志备份被占用（更多地在这个Sor）上。直截了当地）。

  通过备份事务日志，我们可以将活动日志中的所有日志记录(包括这些非活动VLFS中的日志记录)捕获到备份文件中。然后，可以使用这些日志备份将数据库恢复到以前的时间点；希望时间点非常接近发生“灾难”的时间点。在发生这种灾难时，可以将日志备份文件应用到还原的完整数据库备份文件的副本中，并且在数据库恢复期间，在完全备份之后发生的任何事务都将“前滚”，以恢复数据库并将数据还原到给定的时间点，从而最大限度地减少任何数据丢失。当然，这假设您不仅进行了这些日志备份，而且还将它们转移到了一个安全的位置。如果您的日志备份文件与活动日志文件位于同一驱动器上，并且该驱动器崩溃，则可能会丢失所有备份。

  当数据库处于简单恢复模型(在第3级和第4级中详细介绍)时，活动VLFS中的日志记录将被保留，因为回滚操作可能需要它们。但是，当出现检查点时，不活动的VLFS将被截断，这意味着这些VLFS中的日志记录可以立即用新的日志记录覆盖。这就是为什么在简单恢复中操作的数据库被称为自动截断模式。在此模式下，日志中不维护“历史记录”，因此无法在日志备份中捕获它，并将其用作还原过程的一部分。

  控制日志文件的大小

  希望前面的讨论表明，对于大多数以完全恢复模式操作的生产数据库，有必要定期备份事务日志文件，以便能够将数据库恢复到特定的时间点。

  但是，在完全(或BULK_LOGLOG)模式下进行这些日志备份还有第二个重要原因，那就是控制日志的大小。请记住，对于每个修改SQLServer数据库中的数据或对象的事务，日志记录都会写入日志文件。在繁忙的系统中，有许多并发的事务，或者写了大量数据的事务，事务日志的大小可以非常快地增长。

  在完全(或BULK_LOGLOG)模式下工作时，在备份文件中捕获非活动VLFS中日志记录的副本是使这些VLFS符合截断条件的唯一操作，这意味着日志记录占用的空间可以重用。

  关于事务日志的截断和大小的简要说明：有一个常见的误解，即截断日志文件意味着日志记录被删除，文件的大小减小。它没有；截断日志文件只是将空间标记为可重用的行为。在每个不同的恢复模型的上下文中，将在后续级别中更详细地讨论截断。

    因此，在完全(或BULK_LOGLOG)模式下执行常规事务日志备份至关重要的原因之一是控制日志的大小。

 备份事务日志的简要示例

为了简要说明我们在第一层中讨论的一些概念，我们将介绍一个非常简单的示例，用于备份在完全恢复模式下运行的数据库的事务日志。每个进程和命令的详细信息将在以后的级别中更详细地讨论。

  在清单1.1中，我们在SQLServer 2008实例上创建了一个新的TestDB数据库，然后使用DBCCSQLPERF(LOGSPACE)命令立即获得日志文件的大小。

  清单1.1：新TestDB数据库的初始日志文件大小

 正如您所看到的，日志文件当前大小约为1MB，并且大约满了30%。

  注意：在实例上创建的用户数据库的初始大小和增长特征取决于模型数据库的属性，每个数据库将使用的默认恢复模型(在本例中为FULL)也是如此。我们将在级别7中更详细地讨论这些属性的影响-调整和增长事务日志。

  可以通过简单地在磁盘上定位物理文件来确认文件的大小，如图1.1所示。

  图1.1：TestDB的数据和日志文件

   现在让我们为TestDB执行数据文件的备份，如清单1.2所示(首先需要创建“备份”目录)。请注意，此备份操作确保数据库真正处于完全恢复模式下运行；在第三级-事务日志、备份和恢复-中将详细介绍此操作。

    清单1.2：TestDB的初始完全备份

  由于这个备份操作，数据或日志文件的大小没有改变，或者使用日志空间的百分比没有变化，这也许并不奇怪，因为数据库中还没有用户表或数据。让我们纠正这个问题，并在这个数据库上创建一个名为LogTest的表，用100万行数据填充它，并重新检查日志文件大小，如清单1.3所示。这个脚本的作者在SQLServerCentral.com

 

论坛上经常看到，作者是JeffModen，它是在他的善意许可下复制的。不要担心代码的细节，这里唯一重要的是我们插入了很多行。这段代码可能需要几秒钟才能在机器上执行，这并不是因为代码效率低下；而是所有的工作都在幕后进行，编写数据和日志文件。

  1.3：在TestDB中向LogTest表中插入100万行

    请注意，日志文件大小已膨胀到将近110 MB，日志已满91%(在您的系统上，数字可能略有不同)。如果我们要插入更多的数据，那么它就必须再次增大大小，以容纳更多的日志记录。同样，可以从物理文件(数据文件已增长到64 MB)中确认大小的增加。

  通过重新运行清单1.2，我们可以在此时再次备份数据文件，这将不会对日志文件的大小或文件中使用的空间百分比产生任何影响。但是，现在让我们备份事务日志文件并重新检查值，如清单1.4所示。

  清单1.4：备份TestDB的事务日志

  日志文件仍然是相同的物理大小，但是通过备份该文件，SQL Server能够截断日志，从而使日志文件中的“非活动”VLFS中的空间可供重用；可以添加更多的日志记录，而无需物理地增长该文件。当然，我们还将日志记录捕获到备份文件中，因此，如果需要将TestDB数据库还原到以前的状态，则可以使用该文件作为数据库恢复过程的一部分。

  总结

在第一层中，我们介绍了事务日志，并解释了SQL Server如何通过预写日志机制来维护数据的一致性和完整性。我们还描述并简要演示了DBA如何将事务日志文件的内容捕获到备份文件中，然后可以重用该备份文件作为恢复过程的一部分来恢复数据库。最后，我们强调了备份在控制事务日志大小方面的重要性。

  在下一个级别，我们将更深入地研究事务日志的体系结构。

   资源：TLogStairway_Level1.sql本文是SQL Server楼梯中事务日志管理的一部分，注册到我们的RSS提要，并在楼梯中发布一个新级别后立即收到通知！