第十五周翻译

阅读量：5334 次

发布时间：2019-06-15

本文共 7691 字，大约阅读时间需要 25 分钟。

此原文转载自：

楼梯到事务日志管理SQL Server，1级：事务日志

由，2013 / 10 / 30（首次发表：2011 / 06 / 17）

该系列

这篇文章是楼梯系列的一部分：

当事情进展顺利，没有需要特别在意日志或它是如何工作的交易。你只需要相信，每一个数据库已经有正确的备份制度。当事情出错时，了解事务日志是重要的采取纠正措施，特别是在一个时间点恢复数据库是必需的，迫切的！Tony Davis只给出正确的细节层次，每个DBA都应该知道。

1级：事务日志

一个事务日志文件在SQL Server存储的记录所有交易和修改数据并对数据库的日志文件关联。在发生灾难，SQLServer会意外关闭，如一个实例或硬件故障，事务日志来恢复数据库，数据完整性的机智。重新启动后，数据库进入一个恢复的过程中，事务日志读来确保所有的承诺有效，数据写入数据文件（前滚）和影响的任何部分，未提交的事务进行撤消（回滚）。总之，事务日志，SQL Server数据库的完整性和保证事务的ACID特性的基本手段，特别是耐久性。

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

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

执行事务日志备份–除非工作在简单模式，这是至关重要的，DBA执行定期备份事务日志。一旦被捕获在一个备份文件，日志记录可以随后使用完整数据库备份进行数据库恢复，所以创建数据库作为它的存在之前的时间点，例如在一个失败。

显示器管理日志的增长–在一个繁忙的数据库事务日志可以迅速扩大规模。如果不定期备份，或者不恰当的大小，或指定正确的生长特性，事务日志文件可以填补，导致臭名昭著“9002”（事务日志已满）误差，使SQL Server为“只读”模式（或“资源申请”模式，如果它发生在恢复）。

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

在这楼梯系列，我们会考虑这些核心维护任务了。在这里，在第一个层面上，我们将从SQL Server如何使用事务日志的概述，和两个最重要的方面，它影响了DBA的生活，即数据库还原和恢复、磁盘空间管理。

SQL Server如何使用事务日志

在SQL Server中，事务日志的物理文件，确定常规，虽然没有强制性，由扩展LDF。它是自动创建一个数据库的创建，随着主数据文件，由中密度纤维板延伸的普遍认同，尽管任何扩展可用于存储的数据库对象和数据本身。事务日志，而通常实现为一个单一的物理文件，也可以是一组文件的实现。然而，即使在后者的情况下，它仍然是由SQL服务器视为一个单一的顺序文件，这样，SQL Server不能同时写多个日志文件，所以没有性能上的优势，必须从实现事务日志为多个文件。这是更详细的讨论在7级–上浆和越来越多的事务日志。

每当T-SQL代码更改了数据库对象（DDL），或它所包含的数据不仅是数据或对象更新数据文件，而且细节的变化记录为日志记录在事务日志中。每个日志记录包含有关交易的进行改变身份，当交易开始和结束，哪些页面被改变，数据变化了，等等。

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

当一个数据修改，相关的数据页，希望从数据缓存读取的，还是先从磁盘是否在缓存中没有。Data is modified in the data cache, and the log records to describe the effects of the transaction are created in the log cache.When a transaction is committed, the log records are written to the transaction log, on disk.然而，实际改变的数据不能写入磁盘到稍后的时间，当一个数据库检查点发生。任何缓存中的已修改后，在从磁盘读取缓存数据值不同于磁盘上的一个叫什么脏页。这些脏页可能包含：

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

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

周期性数据库检查点进程扫描数据缓存和刷新所有脏页的磁盘，此时修改反映在物理数据文件和日志文件。这甚至发生在一个交易仍然是开放的；在一个检查站，脏页刷新到磁盘打开的事务有关，与SQL Server确保日志记录有关这些公开的交易被从日志缓存，事务日志文件的脏页刷新到数据文件之前。

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

这里要注意的重要一点是，日志缓冲区的经理总是保证变化的描述（记录）写入事务日志，在磁盘上，数据页之前写入到物理数据文件。这种机制称为提前写日志。它本质上是由SQL Server保证事务的持久性机制（见数据库事务）。

总是改变写入日志文件，SQL Server的一个机制，可以保证所有提交的事务的影响，最终将体现在数据文件的基础上，和任何数据修改磁盘上的源于不完整的交易，即那些既不提交或回滚已发行最终没有体现在数据文件。

如果数据库崩溃，例如，经过一定的处理（T1）承诺但在受影响的数据写入数据文件，然后重启后的数据库恢复过程开始试图调和内容的事务日志文件和数据文件。它会读取事务日志文件，确保所有的交易，包括T1的操作记录在日志文件中，是“滚滚向前”（重做），它们反映在数据文件。

同样，一个数据库崩溃后的恢复过程将“回滚”（撤消）在与未提交的事务相关的数据库中的任何数据变化，通过从日志文件中读取相关的操作，并对数据进行反向物理操作。

在这种方式中，SQL Server可以数据库恢复到一致的状态，在发生撞车事故。更普遍的是，回滚（撤销）过程中如果发生：

回滚命令是一个显式事务发布

发生了一个错误，xact_abort打开

如果数据库检测到通信已断的数据库和客户之间的交易，教唆。

在这种情况下，日志记录属于一个中断的交易，或一个回滚命令明确发出，读取和更改回滚。在这些方面，SQL服务器确保所有行为与交易相关的成功，作为一个单位，或者说，他们都失败了。因此，事务日志是一个由SQL Server确保日常运行过程中的数据一致性和完整性的基本手段。

然而，事务日志中另一个至关重要的作用，它提供的机制，可以将数据库恢复到以前的时间点，在灾难事件。通过适当的规划和管理，你可以使用这些日志文件的备份来恢复您的所有数据直到它成为损坏或不可用。

事务日志和数据库恢复

如前面所讨论的，一个事务日志文件存储一系列日志记录，顺序根据交易开始时，其提供的修改和交易已向数据库发出的历史记录。每个日志记录包含有关交易的进行改变身份，当交易开始和结束，哪些页面被改变，数据变化了，等等。日志记录在事务日志文件被组织成多个部分，称为虚拟日志文件（VLFS）–这些更加详细的覆盖在水平2–事务日志结构。

SQL Server的预写日志记录机制保证修改的描述（即日志记录）将被写入一个VLF之前修改的数据被写入数据文件。因此，日志记录可能包含的细节是封闭的（承诺）的交易或公开的（未提交的）交易，并在每一种情况下的数据修改的交易可能会或可能不会被写入数据文件，这取决于是否有一个检查点发生。

注：通过定期冲洗脏页从高速缓存到磁盘，数据库检查点进程控制量的工作需要在SQL Server数据库恢复操作。如果SQL Server已经推出了数量庞大的承诺事项的脏页的变化，然后恢复过程将非常漫长。

任何日志记录与打开的事务需要回滚操作，在恢复过程中，并将永远是一个部分被称为一个活跃的VLF，将永远保留在日志文件。日志记录与一个封闭的交易也将成为活跃的VLF，直到到达点没有日志记录在整个VLF是与一个开放的交易，此时VLF变得无效。

在这些活动的超大型浮体结构的日志记录，基本上是提供一个“历史”以前完成的数据库事务，并对这些活动的结构会发生什么取决于数据库的恢复模式。我们将讨论这些恢复模式的详细水平的3 - 6这个楼梯，但关键的一点是，如果您使用的是完整的（或大容量日志）数据库恢复模式，然后事务日志保留日志记录活动体，直到一个日志备份了（这个不久）。

通过备份事务日志，我们可以捕捉到一个备份文件的所有日志记录的日志，包括这些活动的结构。这些日志备份可以用来将数据库还原到之前的某个时间点；希望的时间点非常接近的点“灾难”的发生。在这样的灾难事件，日志备份文件可以应用于恢复复制一个完整的数据库备份文件，和发生的完整备份后将“滚滚向前”的任何交易，数据库恢复时，恢复数据库，将数据恢复到一个特定的时间点，所以减少任何数据丢失。当然，这是假设你不仅把这些日志备份，而且把他们转移到安全地点。如果你的日志备份文件在同一驱动器作为生活日志文件和硬盘坏了，那么你可能会失去你所有的备份。

当一个数据库是简单恢复模式（这个在级别3和4），在主动结构的日志记录被保留，因为他们可以回滚操作要求。然而，非超大型浮体将截断一检查站时发生，这意味着日志记录在这些VLFs可以立即覆盖新的日志记录。这就是为什么一个数据库恢复操作是简单称为自动截断模式。在这种模式下，没有“历史”保持在日志，所以它不能被捕获在一个日志备份作为恢复过程的一部分。

控制日志文件的大小

希望前面的讨论已经表明，大多数生产数据库，在完整恢复模式下运行，就必须采取定期备份事务日志文件，以使数据库恢复到某个时间点。

然而，有一次，把这些日志备份在操作时重要的原因（或bulk_logged）模式是控制日志的大小。记得一个日志记录写入到每一个事务修改数据或对象在一个SQL Server数据库的日志文件。在一个繁忙的系统，有许多并发交易，或是那些写了很多数据，事务日志的大小变的很快。

在充分的工作时（或bulk_logged）模式，在备份捕获文件副本的日志记录活动体，是唯一的行动，将使VLFs获得截断，即通过日志记录占用的空间变为可重用。

一个简短的便条上截断事务日志的大小：有一个普遍的误解，截断日志文件，日志记录被删除了的文件大小。它不；一个日志文件截断仅仅是标记为可重用的行为空间。截断，在每一个不同的恢复模式下，会在随后的水平进行更详细的讨论。

因此，一个原因是很重要的定期进行事务日志备份的全部工作时（或bulk_logged）模式是控制日志的大小。

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

为了简单地说明一些我们在这一层面上讨论的概念，我们将步行通过一个非常简单的备份一个数据库的完整恢复模式操作的事务日志的例子。每个过程的细节和命令将覆盖更多的细节在随后的水平。

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

USE master ;IF EXISTS ( SELECT name

FROM sys.databases

WHERE name = 'TestDB' )

DROP DATABASE TestDB ;CREATE DATABASE TestDB ON(

NAME = TestDB_dat,

FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Data\TestDB.mdf') LOG ON(

NAME = TestDB_log,

FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Data\TestDB.ldf') ;DBCC SQLPERF(LOGSPACE) ;Database Name Log Size (MB) Log Space Used (%) Status---------------------------------------------------------

master 1.242188 34.27673 0...<snip>...

TestDB 0.9921875 31.74213 0

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

你可以看到，日志文件是目前约1 MB的大小，和大约30%。

注：初始大小和生长特性的用户创建的数据库实例是由属性数据库的模式，是默认的恢复模式，每个数据库将使用（全，在这种情况下）。我们将讨论的影响，这些特性在很多更详细的7级–上浆和越来越多的事务日志。

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

图1.1：数据和日志文件库

现在让我们来执行备份的数据文件库，如清单1.2所示（你需要先创建“备份”目录）。注意，这个备份操作，保证数据库的真正运行在完整恢复模式；更在这水平3–交易，备份和恢复。

-- full backup of the databaseBACKUP DATABASE TestDBTO DISK ='C:\Backups\TestDB.bak'WITH INIT;

清单1.2：其中初始的完整备份

在数据或日志文件的大小没有变化，由于这种备份操作，或使用的日志空间的百分比，这也许是不足为奇的没有用户表或数据库中的数据，还。让我们把它的权利，并创建一个表，称为数据库logtest，填写100万行数据，并重新检查日志文件的大小，如清单1.3所示。这个脚本的作者，经常看到在sqlservercentral.com论坛，是Jeff Moden，这是他的一种许可转载。不必担心代码的细节；最重要的是，我们插入多行。这个代码可能需要几秒到你的机器上执行，而不是因为代码是无效的；它的所有工作在幕后，写数据和日志文件。

USE TestDB ;

GOIF OBJECT_ID('dbo.LogTest', 'U') IS NOT NULL

DROP TABLE dbo.LogTest ;--===== AUTHOR: Jeff Moden--===== Create and populate 1,000,000 row test table.-- "SomeID" has range of 1 to 1000000 unique numbers-- "SomeInt" has range of 1 to 50000 non-unique numbers-- "SomeLetters2";"AA"-"ZZ" non-unique 2-char strings-- "SomeMoney"; 0.0000 to 99.9999 non-unique numbers-- "SomeDate" ; >=01/01/2000 and <01/01/2010 non-unique-- "SomeHex12"; 12 random hex characters (ie, 0-9,A-F) SELECT TOP 1000000

SomeID = IDENTITY( INT,1,1 ),

SomeInt = ABS(CHECKSUM(NEWID())) % 50000 + 1 ,

SomeLetters2 = CHAR(ABS(CHECKSUM(NEWID())) % 26 + 65)

+ CHAR(ABS(CHECKSUM(NEWID())) % 26 + 65) ,

SomeMoney = CAST(ABS(CHECKSUM(NEWID())) % 10000 / 100.0 AS MONEY) ,

SomeDate = CAST(RAND(CHECKSUM(NEWID())) * 3653.0 + 36524.0 AS DATETIME) ,

SomeHex12 = RIGHT(NEWID(), 12)INTO dbo.LogTestFROM sys.all_columns ac1

CROSS JOIN sys.all_columns ac2 ;DBCC SQLPERF(LOGSPACE) ;

清单1.3：插入一百万行到logtest表，在其中

请注意，日志文件大小已激增至近110 MB和日志91%（数字可能在你的系统略有不同）。如果我们要插入更多的数据，它会长大，再容纳更多的日志记录。再次，尺寸的增加可以从物理文件的确认（数据文件已增长到64 MB）。

我们可以备份的数据文件在这一点上，重新运行清单1.2，它将日志文件的大小无差异，或部分空间用于文件。现在，然而，让我们备份事务日志文件和检查的值，如清单1.4所示。

-- now backup the transaction logBACKUP Log TestDBTO DISK ='C:\Backups\TestDB_log.bak'WITH INIT;