第十五周翻译

SQL Server中事务日志管理的阶梯，第1级：事务日志概览

由Tony Davis所创作，于2013/10／30（第一次出版的时间是：2011／06／17）

这一系列

本文是楼梯系列的一部分：SQL Server中事务日志管理的阶梯。

当事情进行顺利时，没有必要特别注意事务日志是如何工作的，或者它是如何工作的。您只需要确信每个数据库都有正确的备份机制。当事情出错时，理解事务日志对于采取纠正措施是非常重要的，尤其是在需要及时更新数据库时！Tony Davis给出了每个DBA应该知道的正确级别。

级别1：事务日志概览

事务日志是一个文件，其中SQL Server将在日志文件关联的数据库上执行的所有事务和数据修改的记录存储在一起。如果发生导致SQL Server意外关闭的灾难，例如实例或硬件故障，则事务日志用于恢复数据库，其数据完整性很强。重新启动时，数据库进入一个恢复过程，在这个过程中，事务日志被读取，以确保所有有效的、提交的数据被写入到数据文件（向前滚动），并且撤消任何部分未提交事务的影响（回滚）。简而言之，事务日志是SQL Server确保数据库完整性和事务的酸性属性，尤其是持久性的基本手段。

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

·选择正确的恢复模式–SQL Server三数据库恢复模式：提供完整的（默认），简单的和大容量日志。DBA必须根据数据库的业务需求选择合适的模型，然后建立适合于该模式的维护过程。

·执行事务日志备份–除非在简单模式下工作，这是至关重要的，DBA执行定期备份事务日志。一旦在备份文件中捕获，日志记录随后可以应用于完整的数据库备份，以便执行数据库还原，从而重新创建数据库，如在以前的时间点上存在的数据库，例如，在故障之前。

·监测在一个繁忙的数据库事务日志管理日志的增长–可以迅速扩大规模。如果没有定期备份，或者如果大小不合适，或者分配了不正确的增长特性，事务日志文件就会填满，导致臭名昭著的“9002”（事务日志满）错误，这使SQL Server进入“只读”模式（如果在恢复期间发生），则进入“资源挂起”模式。

·优化日志的吞吐量和可用性–除了以备份的基本维护，DBA必须采取步骤，确保有足够的性能的事务日志。这包括硬件考虑，以及避免诸如日志碎片之类的情况，这种情况可能影响事务的性能。

在这个楼梯系列中，我们将详细地考虑这些核心维护任务中的每一个。在第一层中，我们将首先概述SQL Server如何使用事务日志，以及它影响DBA生命的两个最重要的方式，即数据库恢复和恢复，以及磁盘空间管理。

SQL Server如何使用事务日志

在SQL Server中，事务日志的物理文件，确定常规，虽然没有强制性，由扩展LDF。它是在创建数据库时自动创建的，以及主要的数据文件，通常由中密度纤维板扩展名标识，但也可以使用任何扩展来存储数据库对象和数据本身。事务日志，虽然通常作为单个物理文件实现，但也可以作为一组文件实现。然而，即使在后一种情况下，它仍然由SQL Server作为一个单一的顺序文件处理，因此，SQL Server不能并且不并行地写入多个日志文件，因此从将事务日志作为多个文件实现是没有性能优势的。在第7级中更详细地讨论了这个问题，即对事务日志进行大小调整和扩展。

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

注意：事务日志不是审核跟踪。它不提供对数据库所做的更改的审计跟踪；它不保存对数据库执行的命令的记录，而是结果如何更改数据。

当进行数据修改时，希望从缓存中读取相关数据页，或者从磁盘中检索它们，如果它们还没有在缓存中。在数据缓存中修改数据，并在日志缓存中创建描述事务影响的日志记录。当事务提交时，日志记录将写入到磁盘上的事务日志中。但是，当数据库检查点出现时，实际更改的数据可能不会写入到磁盘。缓存中的任何页面，都是从磁盘读取的，因此缓存中的数据值与磁盘上的不同，称为脏页。这些脏页可能包含两个：

·数据一直致力于和“硬”的事务日志文件，但尚未到数据文件

·数据通过公开交易即那些尚未提交修改（或回滚）

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

注：另一个过程，数据缓存，扫描LazyWriter，也可以写脏数据页的磁盘，一个检查点外，如果被迫这样做的内存压力。

这里要注意的重要一点是，日志缓冲区管理器总是保证在数据页写入物理数据文件之前，将更改描述（日志记录）写入磁盘上的事务日志。这种机制称为写前日志。

总是先写更改日志文件,SQL Server的基础机制,可以保证所有已提交的事务的影响最终将反映在数据文件,并修改磁盘上的任何数据,来自不完整的交易,即那些提交和回滚的最终发行不反映在数据文件中。

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

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

这样，在发生崩溃时，SQL Server可以将数据库返回到一致的状态。更一般的情况是，如果:

•为显式事务发出回滚命令

•发生错误，打开XACT_ABORT

•如果数据库检测到数据库与发起事务的客户机之间已经断开了通信。

在这种情况下，将读取与中断事务相关的日志记录，或显式地发出回滚命令的日志记录，以及回滚的更改。在这些方面，SQL Server确保所有与事务关联的操作成功，作为一个单元，或者它们都失败。因此，事务日志表示SQL Server在正常的日常操作中确保数据一致性和完整性的基本方法之一。

但是，事务日志扮演另一个重要角色，它提供了一个机制，数据库可以在灾难发生时恢复到以前的时间点。通过适当的计划和管理，您可以使用这些日志文件的备份来恢复所有的数据，直到它被损坏或无法使用。

 

事务日志和数据库恢复

正如前面所讨论的，事务日志文件存储了一系列日志记录，根据事务开始时的顺序，它提供了针对该数据库的修改和事务的历史记录。每个日志记录包含关于执行更改的事务ID的详细信息，当该事务开始和结束时，更改了哪些页面、所做的数据更改等等。事务日志文件中的日志记录被组织成多个部分，称为虚拟日志文件(VLFs)——在第2级事务日志体系结构中，这些内容包含了更多的细节。

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

注意:通过定期刷新从缓存到磁盘的脏页，数据库检查点流程控制数据库恢复操作期间需要做的工作SQL Server的数量。如果SQL Server必须将大量已提交事务的更改滚动到脏页上，那么恢复过程可能会非常漫长。

与开放事务相关的任何日志记录都可能需要在恢复期间进行回滚操作，并且始终是被称为活动VLF的一部分，并且将始终保留在日志文件中。与闭事务相关的日志记录也将是一个活动的VLF的一部分，直到在整个VLF中没有日志记录的情况下，与一个打开的事务相关联，此时VLF将变得不活跃。

这些不活动的VLFs中的日志记录实质上提供了预先完成的数据库事务的“历史”，而这些不活动的VLFs的变化取决于数据库的恢复模型。我们将详细讨论这些复苏模型级别3 - 6的楼梯,但这里的关键是,如果您使用的是完整的(或批量记录)数据库恢复模型,那么事务日志保留在不活跃的甚低频日志记录,直到一个日志备份(不久)。

通过备份事务日志，我们可以将活动日志中的所有日志记录(包括这些不活动的VLFs中的日志记录)捕获到备份文件中。然后可以使用这些日志备份将数据库恢复到以前的时间点;希望在很长一段时间内，都能看到一些“灾难”发生的时刻。这样的灾难时,日志备份文件可以应用于恢复完全数据库备份文件的副本,和完整备份之后发生的任何交易将“向前滚”,在数据库恢复,恢复数据库和恢复数据到一个给定的时间点上,所以任何数据损失最小化。当然，这假设您不仅使用了这些日志备份，还将它们转移到安全的位置。如果日志备份文件与live日志文件在同一驱动器上，并且驱动器崩溃，那么您可能会丢失所有备份。

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

 

控制日志文件的大小

希望前面的讨论已经清楚地表明，对于正在完全恢复模型中运行的大多数生产数据库来说，需要定期对事务日志文件进行备份，以便使数据库恢复到特定的时间点。

但是，有一个重要的原因是，在使用完全(或隔离日志)模式时，要使用这些日志备份，这是为了控制日志的大小。请记住，日志记录是为每一个修改SQL Server数据库中的数据或对象的事务写入日志文件的。在一个繁忙的系统中，有许多并发事务，或者是编写大量数据的事务，事务日志可以快速增长。

当在FULL(或bulk_log)模式下工作时，在一个备份文件中捕获不活动的VLFs中日志记录的副本，这是唯一会使那些VLFs符合截断条件的操作，这意味着日志记录占用的空间可以用于重用。

关于截断和事务日志大小的简要说明:有一种常见的误解，即截断日志文件意味着删除日志记录，并且文件大小减少。它不;日志文件的截断仅仅是标记空间以便重用的行为。截断，在每个不同的恢复模型的上下文中，将在后续的级别中进行更详细的讨论。

总是先写更改日志文件,SQL Server的基础机制,可以保证所有已提交的事务的影响最终将反映在数据文件,并修改磁盘上的任何数据,来自不完整的交易,即那些提交和回滚的最终发行不反映在数据文件中。

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

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

这样，在发生崩溃时，SQL Server可以将数据库返回到一致的状态。更一般的情况是，如果:

•为显式事务发出回滚命令

•发生错误，打开XACT_ABORT

•如果数据库检测到数据库与发起事务的客户机之间已经断开了通信。

在这种情况下，将读取与中断事务相关的日志记录，或显式地发出回滚命令的日志记录，以及回滚的更改。在这些方面，SQL Server确保所有与事务关联的操作成功，作为一个单元，或者它们都失败。因此，事务日志表示SQL Server在正常的日常操作中确保数据一致性和完整性的基本方法之一。

但是，事务日志扮演另一个重要角色，它提供了一个机制，数据库可以在灾难发生时恢复到以前的时间点。通过适当的计划和管理，您可以使用这些日志文件的备份来恢复所有的数据，直到它被损坏或无法使用。

 

事务日志和数据库恢复

正如前面所讨论的，事务日志文件存储了一系列日志记录，根据事务开始时的顺序，它提供了针对该数据库的修改和事务的历史记录。每个日志记录包含关于执行更改的事务ID的详细信息，当该事务开始和结束时，更改了哪些页面、所做的数据更改等等。事务日志文件中的日志记录被组织成多个部分，称为虚拟日志文件(VLFs)——在第2级事务日志体系结构中，这些内容包含了更多的细节。

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

注意:通过定期刷新从缓存到磁盘的脏页，数据库检查点流程控制数据库恢复操作期间需要做的工作SQL Server的数量。如果SQL Server必须将大量已提交事务的更改滚动到脏页上，那么恢复过程可能会非常漫长。

与开放事务相关的任何日志记录都可能需要在恢复期间进行回滚操作，并且始终是被称为活动VLF的一部分，并且将始终保留在日志文件中。与闭事务相关的日志记录也将是一个活动的VLF的一部分，直到在整个VLF中没有日志记录的情况下，与一个打开的事务相关联，此时VLF将变得不活跃。

这些不活动的VLFs中的日志记录实质上提供了预先完成的数据库事务的“历史”，而这些不活动的VLFs的变化取决于数据库的恢复模型。我们将详细讨论这些复苏模型级别3 - 6的楼梯,但这里的关键是,如果您使用的是完整的(或批量记录)数据库恢复模型,那么事务日志保留在不活跃的甚低频日志记录,直到一个日志备份(不久)。

通过备份事务日志，我们可以将活动日志中的所有日志记录(包括这些不活动的VLFs中的日志记录)捕获到备份文件中。然后可以使用这些日志备份将数据库恢复到以前的时间点;希望在很长一段时间内，都能看到一些“灾难”发生的时刻。这样的灾难时,日志备份文件可以应用于恢复完全数据库备份文件的副本,和完整备份之后发生的任何交易将“向前滚”,在数据库恢复,恢复数据库和恢复数据到一个给定的时间点上,所以任何数据损失最小化。当然，这假设您不仅使用了这些日志备份，还将它们转移到安全的位置。如果日志备份文件与live日志文件在同一驱动器上，并且驱动器崩溃，那么您可能会丢失所有备份。

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

 

控制日志文件的大小

希望前面的讨论已经清楚地表明，对于正在完全恢复模型中运行的大多数生产数据库来说，需要定期对事务日志文件进行备份，以便使数据库恢复到特定的时间点。

但是，有一个重要的原因是，在使用完全(或隔离日志)模式时，要使用这些日志备份，这是为了控制日志的大小。请记住，日志记录是为每一个修改SQL Server数据库中的数据或对象的事务写入日志文件的。在一个繁忙的系统中，有许多并发事务，或者是编写大量数据的事务，事务日志可以快速增长。

当在FULL(或bulk_log)模式下工作时，在一个备份文件中捕获不活动的VLFs中日志记录的副本，这是唯一会使那些VLFs符合截断条件的操作，这意味着日志记录占用的空间可以用于重用。

关于截断和事务日志大小的简要说明:有一种常见的误解，即截断日志文件意味着删除日志记录，并且文件大小减少。它不;日志文件的截断仅仅是标记空间以便重用的行为。截断，在每个不同的恢复模型的上下文中，将在后续的级别中进行更详细的讨论。

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

 

因此，当在FULL(或散货)模式中工作时，执行常规事务日志备份是至关重要的，其中一个原因是控制日志的大小。

 

一个备份事务日志的简单示例

 

为了简要说明我们在第一个层次中讨论的一些概念，我们将介绍一个简单的例子，它支持在完全恢复模式下运行数据库的事务日志。每个过程和命令的详细信息将在随后的级别中详细讨论。

 

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

 

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

 

正如您所看到的，日志文件目前大约有1 MB大小，大约30%满。

 

注意:在实例上创建的用户数据库的初始大小和增长特征是由模型数据库的属性决定的，这是每个数据库将要使用的默认恢复模型(在本例中是完整的)。我们将在7级调整和增长事务日志中详细讨论这些属性的影响。

 

只需将物理文件定位到磁盘上即可确认文件的大小，如图1.1所示。

 

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

 

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

 

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

 

由于这个备份操作，或者使用了日志空间的百分比，所以数据或日志文件的大小没有变化，这可能不足为奇，因为数据库中没有用户表或数据。让我们把这个设置好，并在这个数据库上创建一个名为LogTest的表，填满100万行数据，再检查日志文件大小，如清单1.3所示。这个脚本的作者，经常在SQLServerCentral.com论坛上看到，是Jeff Moden，他得到了他的许可。不要担心代码的细节;这里唯一重要的是我们插入了很多行。这段代码可能需要几秒钟才能在您的机器上执行，这并不是因为代码效率低下;所有的工作都在后台进行，写入数据和日志文件

 

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

 

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

现在，让我们执行TestDB数据文件的备份，如清单1.2所示(您首先需要创建“备份”目录)。注意，这个备份操作确保数据库真正在完全恢复模式下运行;在第3级——事务日志、备份和恢复。

 

清单1.2:TestDB的初始完整备份

由于这个备份操作的结果，或者使用的日志空间的百分比，数据或日志文件的大小没有变化，这一点也不奇怪，因为数据库中没有用户表或数据。让我们把它放在这个数据库中，在这个数据库中创建一个名为LogTest的表，用100万行数据填充它，并重新检查日志文件的大小，如清单1.3所示。这个脚本的作者经常在SQLServerCentral.com论坛上看到，他是Jeff Moden，并在他的许可下复制了这一脚本。不要担心代码的细节;这里唯一重要的是我们插入了很多行。这段代码可能需要几秒钟的时间才能在您的机器上执行，这并不是因为代码效率低下;所有的工作都在后台进行，写入数据和日志文件。

 

清单1.3:在TestDB中插入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订阅源，当我们在楼梯上发布一个新级别的时候，就会得到通知！