SQL 问题与解答大型事务日志、何时使用修复功能以及更多其他内容
Paul S. Randal
问：我在备份时注意到一些异常现象，希望您能解释一下。我们需要经常备份 62GB 的生产数据库，以刷新应用程序开发人员所使用的数据。我们总是先删除旧副本然后再还原新副本。还原的副本与生产数据库大小相同，而且数据看起来也一样，但是与备份过程相比，还原过程所需的时间要长很多。这是怎么回事？为什么还原比备份需要更长的时间？
答：其实这不属于异常现象。根据您所处的环境，这种现象通常是预料之中的。备份和还原所需的时间之所以不同，是由于每个流程必须执行的步骤不同。
备份数据库包含两个步骤。主要就是对数据库执行 IO 读取操作以及对备份设备执行 IO 写入操作：
备份步骤 1 读取数据文件中所有分配的数据，然后将其写入备份设备。
备份步骤 2 读取某些事务日志，然后将其写入备份设备。
所需的事务日志数量可能会差异很大，但其数量一定能将还原的数据库恢复到相同的时间点。（我曾在一篇博客文章中深入地说明了此问题，其网址为 sqlskills.com/blogs/paul/2008/01/31/MoreOnHowMuchTransactionLogAFullBackupIncludes.aspx。）
而还原数据库最多可能包含四个步骤。涉及的工作要比读写 IO 复杂得多：
还原步骤 1 如果数据库文件不存在，则创建它们。
还原步骤 2 从备份中读取所有数据和事务日志，然后将其写入相关的数据库文件。
还原步骤 3 对事务日志运行恢复过程的“重做”阶段。
还原步骤 4 对事务日志运行恢复过程的“撤消”阶段。
两个备份步骤所需时间与还原步骤 2 所需时间大致相同（假定硬件配置类似并且服务器上没有用户活动）。如果数据文件较大并且需要进行零初始化（这在 SQL Server 2000 中是需要执行的操作，在 SQL Server 2005 中是默认操作），则还原步骤 1 可能需要较长时间。
为避免花费较长时间，请不要在开始还原之前删除现有文件。或者，也可以启用即时初始化，以便快速创建这些文件（有关详细信息，请访问 msdn.microsoft.com/library/ms175935.aspx）。
还原步骤 3 和 4 是对还原的数据库进行恢复，以便确保事务一致性；此流程与崩溃恢复期间对数据库执行的操作流程相同。恢复操作所需时间取决于需要处理的事务日志量。例如，如果在进行备份时恰好有一个长时间运行的事务处于活动状态，则该事务的所有事务日志都会被备份进来，因此届时不得不进行回滚。
问：我打算在日志传送和数据库镜像之间选择一种提供生产数据库冗余副本的方法。我很担心需要在两个服务器之间传送的事务日志数量，尤其是对于我们每晚都需要执行的索引重建操作。听说镜像功能发送的是实际的重建命令而非事务日志，而重建是在镜像中完成的。真的是这样吗？如果是这样，那么对于 BULK_LOGGED 恢复模式，镜像解决方案要优于日志传送，对吗？
答：您听说的观点是错误的。数据库镜像过程是通过将实际的事务日志记录从主体数据库发送到镜像服务器来完成的，这些记录在镜像数据库中将被“重播”。对于镜像的数据库，既不存在任何类型的转换或筛选，也不存在任何类型的 T-SQL 命令拦截。
数据库镜像仅支持 FULL 恢复模式，这意味着始终会完全记录索引重建操作。根据涉及的索引大小的不同，这可能意味着会生成大量事务日志，从而导致主体数据库的日志文件很大，在将日志记录发送到镜像时需要占用大量的网络带宽。
您可以将数据库镜像视为实时日志传送（实际上，这正是早期在 SQL Server 2005 开发期间该功能所使用的名称）。在日志传送过程中，主数据库的事务日志备份会定期传送到辅助服务器上，并在辅助数据库中进行还原。
日志传送功能支持 FULL 和 BULK_LOGGED 恢复模式。对于使用 FULL 恢复模式在日志传送数据库中所执行的索引重建操作，生成的事务日志量将与镜像数据库中生成的数量完全相同。但是，在日志传送数据库方案中，数据是以日志备份（或系列日志备份）而非连续流的形式发送到冗余数据库的。
如果在索引重建完毕后在日志传送数据库中使用 BULK_LOGGED 恢复模式，则只会生成少量的事务日志。但是在下次事务日志备份时，还将会包含被所记录的最低限度索引重建操作改变的全部数据文件范围。这意味着无论是纳入在 BULK_LOGGED 恢复模式下重建的索引的日志备份还是纳入在 FULL 恢复模式下重建的索引的日志备份，其大小都几乎完全相同。
因此，对于镜像数据库与日志传送数据库中的索引重建而言，需要发送到冗余数据库的信息量几乎完全相同。实际的差别仅在于发送信息的方式 — 是连续发送还是成批发送。
在这两种方法之间进行选择时需要考虑许多其他因素（因素太多，仅在一次 SQL 问题解答中无法全部讨论）。您应该先了解所有这些因素与您的需求的关联程度（例如，可接受的数据丢失限制和允许的停机时间），然后再做决定。
问：我正在运行 SQL Server 2005，我发现其中一个数据库的事务日志一直在不断增大。该数据库处于完全恢复模式，我正在进行事务日志备份。我认为这本应该防止事务日志不断增大。这其中究竟发生了什么问题？
答：在完全恢复模式下进行事务日志备份很重要，在这一点上您是对的。但是，还有其他一些因素可导致事务日志增大。这完全取决于究竟是什么在要求事务日志成为被使用的日志（或活动日志）。除了缺乏事务日志备份以外，可能会导致此现象发生的其他常见因素还包括复制、数据库镜像和活动事务等。
复制过程是通过异步读取事务日志记录，然后加载这些事务并将其复制到单独的分布数据库来完成的。尚未被复制日志读取器任务读取的任何事务日志记录都无法被释放。如果您的工作负载生成了大量事务日志记录，而您又为复制日志读取器的运行频率设置了较长的时间间隔，则会累积大量记录，导致事务日志增大。
如果您运行的是异步数据库镜像，则可能会存在尚未从主体数据库发送到镜像服务器的事务日志记录储备（称为数据库镜像 SEND 队列）。这些事务日志记录在成功发出之前无法被释放。如果生成了大量事务日志记录，而网络带宽又受到限制（或出现其他硬件问题），则储备可能会变得很大，导致事务日志不断增大。
最后，如果用户启动了一个显式事务（如使用 BEGIN TRAN 语句），然后进行了某些形式的修改（如 DDL 语句或插入/更新/删除操作），则所生成的事务日志记录在用户提交或回滚该事务前都需要进行保留。这意味着由其他事务生成的任何后继事务日志记录也无法被释放，因为事务日志无法选择性地进行释放。如果假设该用户当天没有结束该事务就下班回家了，则随着越来越多的事务日志记录被不断生成而又无法释放，事务日志就会越来越大。
要了解事务日志无法释放的原因，可以查询 sys.databases 系统目录视图并查看 log_reuse_wait_desc 列，类似于下面所示：
SELECT name AS [Database],
log_reuse_wait_desc AS [Reason]
FROM master.sys.databases;
如果证明是由于某个活动事务所导致的，可使用 DBCC OPENTRAN 语句获取有关该事务的更多信息：
DBCC OPENTRAN ('dbname')
问：我听说在从损坏进行恢复时，不到万不得已不要使用 REPAIR_ALLOW_DATA_LOSS；而应先使用备份进行还原。您是否能解释一下为什么不应使用 SQL Server 2005 的修复功能，以及为什么在使用时要考虑“危险”程度？
答：首先，我确实编写过有关 SQL Server 2005 修复的文章。REPAIR_ALLOW_DATA_LOSS（以下简称为“修复”）的问题在于其工作方式不是很清晰。之所以这样命名此“修复”正是为了说明运行它可能会导致数据库中的数据丢失。此功能修复损坏的数据库结构的方式通常是先删除损坏的结构，然后修复数据库中的其他所有内容（引用已删除结构的或被已删除结构引用的）。在保持数据库结构一致性方面，“修复”的确是不到万不得已不推荐使用的方法，因为它的着眼点不是挽救用户数据。“修复”不会故意去删除用户数据，但它也不会主动去挽救用户数据。
这样一来您可能会认为“修复”是一种不可靠的方式，但是当不得不进行修复时，它可以提供一种最快捷最可靠的方法来修复损坏数据。在进行灾难恢复时，速度极为重要，并且要求绝对准确。设计出经过验证能在各种情况下迅速准确地完成修复操作的复杂修复算法几乎是不可能的。例如，在修复代码中有一些复杂算法可解决为两个索引分配同一页面或范围的问题，但通常此算法都是采用此“修复”功能再加上一些修补。
此外，还有其他一些需要了解的有关“修复”的问题：
- 在删除损坏的结构时，“修复”不会考虑外键约束，因此，可能会删除与其他表格有外键关系的表格中的记录。如果在运行“修复”后不运行 DBCC CHECKCONSTRAINTS，则无法确定是否发生了这种情况。
- “修复”不会（也无法）考虑在应用程序级定义的、可能会被要删除的某些数据破坏的任何内在业务逻辑或数据关系。同样，如果不运行应用程序中构建的任何一种自定义的一致性检查，则无法确定是否有关系遭到破坏。
- 某些修复操作无法被复制。在对等拓扑中对发布服务器或节点运行“修复”可能会在拓扑中引入不一致问题，必须手动进行纠正。
鉴于以上原因，通过采用备份而非运行“修复”来从损坏中进行恢复始终是个不错的办法。但是产品中也提供了“修复”，因为一旦出现数据库受损而又没有备份的情况，最起码要有一种方法能使数据库迅速恢复联机状态。
问：我刚以一名 DBA 的身份加入一家新公司，现在需要负责管理多种应用程序及其后端数据库。其中一种应用程序的更新性能非常差。在经过调查后，我发现该应用程序使用的每个表都包含大量索引。经过多方询问后，才知道似乎是以前的 DBA 喜欢对各个表列及某些组合添加索引。我认为并非所有索引都是必要的，但我该如何找出可以安全删除的索引呢？我们运行的是 SQL Server 2005。
答：正如您所猜测的那样，大量索引极有可能是造成性能不佳的主要因素。每次在表中插入、更新或删除行时，都需要在每个非群集索引中执行相应的操作。这将在 I/O、CPU 利用率和事务日志生成等方面增加大量的管理开销。
在 SQL Server 2000 中，判断正在使用哪些索引的唯一途径是使用配置文件和检查查询计划。在 SQL Server 2005 中，则可使用新的动态管理视图 (DMV) -sys.dm_db_index_usage_stats，它可以跟踪索引使用情况。
此 DMV 会跟踪数据库启动以来的每一次索引使用及使用方式。SQL Server 关闭后所有数据库的统计信息均会丢失，某个数据库关闭或拆分后，该数据库的统计信息会丢失。其想法是如果某个索引未出现在输出中，则它肯定在数据库启动后就未被使用过。
随着时间的推移来跟踪索引使用情况的简单方法是定期拍摄 DMV 输出的快照，然后对这些快照加以比较。许多人都忽略的一点是必须跟踪索引在整个业务周期内的使用情况。如果您只是拍摄一天的快照，则可能会发现多个未使用过的索引。但是，如果这些索引是具有其他用途，比如用于帮助月底报表更快速地运行，则可能不应该删除这些索引。如果某索引确实在整个业务周期内都未被使用过，则很可能能够将其删除并回收空间以提高性能。
有关用于定期拍摄 DMV 快照的一些简单代码，请参见我的博客文章，网址为 sqlskills.com/blogs/paul/2007/10/05/IndexesFromEveryAngleHowCanYouTellIfAnIndexIsBeingUsed.aspx。
Paul S. Randal 是 SQLskills.com 的总裁，也是 SQL Server 的 MVP。Paul 曾编写过 DBCC CHECKDB/repair for SQL Server 2005，并在 SQL Server 2008 的开发过程中负责核心存储引擎部分的工作。Paul 是灾难恢复、高可用性和数据库维护方面的专家，经常出席一些会议。他的博客地址是 SQLskills.com/blogs/paul。