DATABASE SYSTEM CONCEPTS6.19

该系列
本文是SQL Server中“阶层系列：事务日志管理的阶段”的一部分

当事情进展顺利的时候，不需要特别意识到事务日志的作用或工作原理。你只需要确信每个数据库都有正确的备份机制。当事情出错时，对事务日志的理解对于采取纠正措施很重要，尤其是在需要时间点恢复数据库的情况下，迫切需要！托尼戴维斯给出了每个DBA应该知道的正确的细节级别。

级别1：事务日志概述
事务日志是一个文件，SQL Server在该文件中存储了与日志文件关联的数据库上执行的所有事务和数据修改的记录。如果发生导致SQL Server意外关闭的灾难（如实例或硬件故障），则事务日志将用于恢复数据库，并保证数据的完整性。重新启动时，数据库进入恢复过程，在该过程中读取事务日志以确保将所有有效的已提交数据写入数据文件（前滚），并且撤消（回滚）任何部分未提交的事务。简而言之，事务日志是SQL Server确保数据库完整性和事务的ACID属性（特别是耐久性）的基本手段。

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

•选择正确的恢复模式 - SQL Server提供三种数据库恢复模式：FULL（默认），SIMPLE和BULK LOGGED。 DBA必须根据数据库的业务需求选择适当的模型，然后建立适合该模式的维护程序。
•执行事务日志备份 - 除非以SIMPLE模式工作，否则DBA定期备份事务日志至关重要。一旦捕获到备份文件中，日志记录可以随后应用于完整数据库备份，以便执行数据库还原，并重新创建数据库，使其在之前的时间点存在，例如在故障发生之前。
•监控和管理日志增长 - 在繁忙的数据库中，事务日志的大小可能会快速增长。如果不经常备份，或者大小不合适，或者分配了不正确的增长特性，则事务日志文件可能会填满，导致臭名昭着的“9002”（事务日志已满）错误，从而使SQL Server进入“只读”模式（或者进入“资源未决”模式，如果它在恢复过程中发生的话）。
•优化日志吞吐量和可用性 - 除了进行基本维护（如进行备份）之外，DBA还必须采取措施确保事务日志的性能良好。这包括硬件考虑因素，以及避免可能影响事务性能的日志碎片等情况
在这个Stairway系列中，我们将详细考虑这些核心维护任务中的每一个。在这里，在第一级，我们将首先概述SQL Server如何使用事务日志，以及影响DBA生命周期的两种最重要的方式，即数据库恢复和恢复以及磁盘空间管理。

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

每当T-SQL代码对数据库对象（DDL）或其包含的数据进行更改时，不仅数据或对象在数据文件中更新，而且更改的详细信息都将记录为事务中的日志记录登录。每个日志记录都包含有关执行更改的事务标识的详细信息，该事务开始和结束时，哪些页面发生更改，发生了哪些数据更改等等。

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


当进行数据修改时，希望从数据缓存中读取相关数据页面，或者先从磁盘中检索相关数据页面（如果它们尚不在缓存中）。在数据缓存中修改数据，并在日志缓存中创建描述事务效果的日志记录。提交事务时，日志记录将写入磁盘上的事务日志。但是，实际更改的数据可能不会写入磁盘，直到稍后发生数据库检查点时为止。高速缓存中的任何页面在从磁盘读取后已被修改，以便高速缓存中的数据值与磁盘上的数据值不同时称为脏页。这些脏页面可能包含两个：

•已提交并“强化”到事务日志文件但尚未到达数据文件的数据
•由未决事务修改的数据，即尚未提交（或回滚）的数据
定期数据库检查点进程扫描数据高速缓存并将所有脏页面刷新到磁盘，此时修改会反映在物理数据文件以及日志文件中。即使在交易仍然开放的情况下也会发生这种情况;在检查点期间，与打开事务相关的脏页面会刷新到磁盘，SQL Server会始终确保将与这些打开事务相关的日志记录从日志高速缓存刷新到事务日志文件，然后将脏页面刷新到数据文件。

注意：扫描数据高速缓存LazyWriter的另一个进程也可能会将脏数据页写入磁盘，而不是在检查点之外（如果被内存压力强制执行的话）。

这里需要注意的重要一点是日志缓冲区管理器始终保证在将数据页面写入物理数据文件之前，将更改描述（日志记录）写入磁盘上的事务日志。这种机制被称为预写式日志记录。它本质上是SQL Server确保事务处理持久性的机制（请参阅数据库事务的ACID属性）。

通过总是首先将更改写入日志文件，SQL Server具有一种机制的基础，该机制可以确保所有已提交事务的影响最终会反映在数据文件中，并且磁盘上任何源自未完成事务的数据修改，即那些既没有COMMIT也没有ROLLBACK的数据文件最终没有反映在数据文件中。


例如，如果数据库在提交某个事务（T1）之后但在将受影响的数据写入数据文件之前发生崩溃，则在重新启动时，将启动数据库恢复过程，该过程将尝试协调事务日志文件的内容和数据文件。它将读取事务日志文件，并确保记录在日志文件中的所有组成事务T1的操作“前滚”（重做），以便它们反映在数据文件中。

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

以这种方式，SQL Server可以在发生崩溃时将数据库返回到一致状态。更一般地说，回滚（撤消）过程发生如果：

•为明确的事务发出ROLLBACK命令
•发生错误并且XACT_ABORT已打开
•如果数据库检测到数据库与引发事务的客户端之间的通信中断。
在这种情况下，将读取与中断事务有关的日志记录，或者显式发出ROLLBACK命令的日志记录，并且更改将回滚。通过这些方式，SQL Server可以确保与事务相关的所有操作都可以成为单元，或者它们全都失败。因此，事务日志代表了SQL Server在正常的日常操作中确保数据一致性和完整性的基本手段之一。

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

事务日志和数据库恢复
正如前面所讨论的，事务日志文件存储一系列日志记录，根据事务何时开始，这些日志记录是连续的，它提供了对该数据库发出的修改和事务的历史记录。每个日志记录都包含有关执行更改的事务标识的详细信息，该事务开始和结束时，哪些页面发生更改，发生了哪些数据更改等等。事务日志文件中的日志记录被组织成多个部分，称为虚拟日志文件（VLF） - 这些在第2层 - 事务日志体系结构中有更详细的介绍。

SQL Server的预写式日志记录机制可确保在修改后的数据本身写入数据文件之前，修改的描述（即日志记录）将写入VLF。因此，日志记录可能包含关闭（已提交）事务或开放（未提交）事务的详细信息，并且在每种情况下，事务修改的数据可能已写入数据文件，也可能未写入数据文件，具体取决于没有发生检查点。

 

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

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

这些不活动的VLF中的日志记录基本上提供了先前完成的数据库事务的“历史记录”，这些不活动的VLF会发生什么情况取决于数据库的恢复模式。我们将在这个阶梯的第3 - 6级中详细讨论这些恢复模型，但关键在于如果您使用FULL（或BULK LOGGED）数据库恢复模型，那么事务日志将保留日志记录在不活动的VLF中，直到进行日志备份（稍后会详细介绍）。

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

当数据库处于SIMPLE恢复模式（更多关于级别3和4）时，保留活动VLF中的日志记录，因为它们可能是回滚操作所需的。但是，当检查点发生时，不活动的VLF将被截断，这意味着这些VLF中的日志记录可以立即被新的日志记录覆盖。这就是为什么操作SIMPLE恢复的数据库被称为处于自动截断模式的原因。在此模式下，日志中不会保留“历史记录”，因此它不能在日志备份中捕获并用作还原过程的一部分。

控制日志文件的大小
希望前面的讨论清楚地表明，对于大多数以FULL恢复模式运行的生产数据库，有必要对事务日志文件进行定期备份，以便将数据库恢复到特定的点时间。

但是，在FULL（或BULK_LOGGED）模式下进行操作并控制日志大小时，还有第二个重要原因需要执行这些日志备份。请记住，日志记录将写入日志文件中，用于修改SQL Server数据库中的数据或对象的每个事务。在繁忙的系统中，有许多并发事务或写入大量数据的事务时，事务日志的大小可能会非常快。

在FULL（或BULK_LOGGED）模式下工作时，在备份文件中捕获不活动VLF中日志记录的副本是唯一可使这些VLF符合截断条件的操作，这意味着日志记录占用的空间可用于重用。

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

因此，在FULL（或BULK_LOGGED）模式下工作时执行常规事务日志备份至关重要的原因之一是控制日志的大小

 

备份事务日志的简要示例
为了简要说明我们在第一级中讨论的一些概念，我们将通过一个非常简单的示例来备份在FULL恢复模式下运行的数据库的事务日志。每个独立进程和命令的细节将在后续级别中进行更详细的介绍。

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

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

正如您所看到的，日志文件大小约为1 MB，约占满30％。

注意：实例上创建的用户数据库的初始大小和增长特征由模型数据库的属性决定，因为每个数据库将使用的默认恢复模型（在本例中为FULL）。我们将在第7级 - 调整和增长交易日志中更详细地讨论这些属性的影响。

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

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

现在让我们为TestDB执行数据文件的备份，如清单1.2所示（您首先需要创建“Backups”目录）。请注意，此备份操作可确保数据库真正在FULL恢复模式下运行;更多关于这个在第3层 - 交易日志，备份和恢复。

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

数据或日志文件的大小（由于此备份操作）或所用日志空间的百分比没有变化，因为目前还没有用户表或数据库中的数据，这可能并不令人惊讶。让我们把它放在正确的位置，然后在这个数据库上创建一个名为LogTest的表，并填充100万行数据，然后重新检查日志文件大小，如清单1.3所示。经常在SQLServerCentral.com论坛上看到的这个脚本的作者是Jeff Moden，并且在他的友好许可下转载。不要担心代码的细节;这里唯一重要的是我们插入了很多行。此代码可能需要几秒钟才能在您的机器上执行，这并不是因为代码效率低下;这些都是幕后工作，写入数据和日志文件。

清单1.3：在TestDB的LogTest表中插入一百万行

请注意，日志文件大小已膨胀到将近110 MB，并且日志文件已满91％（这些数字可能与您的系统稍有不同）。如果我们要插入更多的数据，它将不得不再次增大以容纳更多的日志记录。同样，尺寸的增加可以从物理文件中确认（数据文件已经增长到64 MB）。

我们可以通过重新运行清单1.2来再次备份数据文件，这对日志文件的大小或文件中使用的空间的百分比没有影响。但是，现在让我们备份事务日志文件并重新检查值，如清单1.4所示。
    
 
每日新鲜的文章：

获取SQL Server Central新闻稿并每天获取新的SQL Server文章。获取数据库周刊，以汇总来自网络的所有最大的SQL新闻。注册不用了，谢谢每天通过电子邮件提供更多文章来提高您的SQL Server知识。注册感谢此作者分享：
      给此评分加入讨论添加到公文包
 
SQL Server事务日志管理的阶段，1级：事务日志概述
作者：托尼戴维斯，2013/10/30（第一次公布：2011/06/17）

该系列
本文是SQL Server中“阶层系列：事务日志管理的阶段”的一部分

当事情进展顺利的时候，不需要特别意识到事务日志的作用或工作原理。你只需要确信每个数据库都有正确的备份机制。当事情出错时，对事务日志的理解对于采取纠正措施很重要，尤其是在需要时间点恢复数据库的情况下，迫切需要！托尼戴维斯给出了每个DBA应该知道的正确的细节级别。

 

级别1：事务日志概述
事务日志是一个文件，SQL Server在该文件中存储了与日志文件关联的数据库上执行的所有事务和数据修改的记录。 如果发生导致SQL Server意外关闭的灾难（如实例或硬件故障），则事务日志将用于恢复数据库，并保证数据的完整性。 重新启动时，数据库进入恢复过程，在该过程中读取事务日志以确保将所有有效的已提交数据写入数据文件（前滚），并且撤消（回滚）任何部分未提交的事务。 简而言之，事务日志是SQL Server确保数据库完整性和ACID计划的基本手段