通过

  • 项目

SQL Server

了解日志记录和 SQL Server 中恢复

Paul S. Randal

 

概览：

  • 日志记录和恢复 SQL Server 中如何工作
  • 事务日志的工作原理，并且需要了解管理它
  • 恢复模型和日志记录其效果

内容

什么记录？
什么是恢复？
事务日志
恢复模型
向上覆盖

一些最 misunderstood 部分 SQL Server 包括其日志记录和恢复机制。事实上事务日志存在可能导致的问题如果不，正确管理似乎 confound 许多"先天 DBA"。 原因是事务日志增长未绑定的？为什么会有时需要很长系统崩溃后联机数据库？为什么不能记录被关闭完全？为什么无法我恢复我的数据库正确？只是什么是事务日志和为什么是否存在？

这些是我重复在看 SQL Server 论坛和新闻组，因此我想在本文中提供的日志记录和恢复系统概述并解释为什么它是类组成部分 SQL Server 存储引擎的所有问题。我将说明事务日志以及如何数据库的三个的恢复模式可以更改事务日志和日志记录过程的行为的体系结构。以及该的方式我将还提供一些链接到覆盖事务日志的资源管理最佳操作。

什么记录？

记录和恢复不是 SQL Server 特有的概念，所有商业关系数据库管理系统 (RDBMS) 必须将其以支持各种 ACID 属性的交易记录。ACID 代表原子性、 一致性、 隔离和持久性的事务处理系统 （如一个 RDBMS) 的基本属性。您可以阅读更多有关在此，MSDN Library 的 ACID Properties 内容.

视频

Paul Randal 演示管理在交易记录登录正确在完整恢复模式的重要性并他显示 SQL Server 执行此操作的方法。

一个 RDBMS 中的操作是记录 （或记录） 物理和逻辑级别方面的生数据库的存储结构中。每次更改存储结构都有其自身日志为记录描述结构进行更改和更改已。这是方式可以重放或撤消，更改必要。日志记录存储在名为事务日志的特殊文件中，我将描述此详细以后，但现在可以将其视为顺序访问的文件。

一个或多个类更改一组可以 （和事实上，始终是) 在事务中组合在一起，它提供对一个数据库中为玩作为应用程序开发的用户进行更改 （原子性） 的基本单元，并 DBA 都涉及。事务成功 （提交） 或失败 / 被取消 (回滚)。在第一种情况下保证窗体交易记录的操作会反映在数据库。在第二种情况下操作保证不会反映在数据库。

SQL Server 中的交易记录是显式或隐式。显式的事务是在用户或应用程序发出一个 BEGIN TRANSACTION T-SQL 语句信号由该会话的一组相关更改的开始。从显式事务成功发出 COMMIT TRANSACTION 语句是，信号成功完成指定的组的更改。如果 ROLLBACK TRANSACTION 语句而发出，进行该会话，因为发出 BEGIN TRANSACTION 语句的所有更改都还原 （回滚） 和中止该事务。事务回滚可能也被强制由外部事件，如数据库耗尽磁盘空间或服务器出现故障如稍后我将介绍。

隐式事务是其中用户或应用程序不显式发出发出 T-SQL 语句之前在 BEGIN TRANSACTION 语句。但是的到数据库的所有更改必须都将事务，存储引擎将自动启动在底层事务。T-SQL 语句完成时, 存储引擎将自动提交它启动地环绕该用户的语句的事务。

您可能认为这是不必要因为单个的 T-SQL 语句不会生成大量的更改在的数据库的存储结构，但考虑类似的 ALTER INDEX 重建语句。尽管此语句不能包含显式事务中，它未能生成数据库更改的相当大数。因此必须有一些机制来确保如果内容不会进入错误 （该语句已取消，实例） 的所有更改都正确撤消。

作为示例，请考虑在一个隐式事务中更新单个表格行时发生。假设的整列 c 1 和 char 列 c 2 一个简单的堆表。表具有 10,000 的行，而且用户提交更新查询，如下所示：

UPDATE SimpleTable SET c1 = 10 WHERE c2 LIKE '%Paul%';

发生以下操作：

  • 数据页从 SimpleTable 从磁盘读取到内存 （缓冲池） 以便它们可搜索的匹配行。 事实证明三个数据页保存符合 WHERE 子句谓词的五个行。
  • 存储引擎会自动启动一个隐式的事务。
  • 三个数据页和五个数据行锁定为允许进行更新。
  • 在更改五个数据记录在内存中的三个数据页上。
  • 所做的更改也记录在磁盘上的事务日志中的日志记录。
  • 存储引擎自动提交隐式事务。

请注意，我没有列出的三个更新的数据页被回写入一个步骤出到磁盘。 这是因为他们还不需要为，； 只要日志记录描述所做的更改位于在交易记录日志中的磁盘，然后更改受保护。 如果页需要随后读取或再次更改，然后页的最新的副本是已磁盘 （但只是不的上的内存中。 数据页将被写入出到磁盘下一个检查点操作时，或者如果他们在缓冲池中使用的内存需要另一个页面图像。

检查点有两个原因，为批处理设置写入 I / O 以提高性能并减少时间所需的故障恢复。 方面性能，如果数据页被强制出到磁盘已更新每次写入 I / O 忙系统上发生可以轻松地数淹没 I / O 子系统。 最好定期将写出脏页 （从磁盘读取后已更改的页） 比为写出页立即它们被更改。 我将稍后讨论检查点恢复的方面。

有关检查点的一个常见误解是它们只编写出页面更改从提交的事务。 不正确，检查点始终写出所有脏页，而不考虑的更改页面的事务已提交或不。

预写式日志记录是其中描述更改日志记录会写入磁盘之前，这些更改本身将被写入的机制。 它提供了 ACID 属性的持久性部分。 只要描述更改日志记录是发生了崩溃的事件中的磁盘上日志记录 （因此自己更改） 可以将其恢复，并且交易记录的效果不会丢失。

什么是恢复？

查找 SQL Server 提示？

有关 SQL Server 的提示，请访问， TechNet 杂志 》 SQL Server 提示 页。

有关其他产品的更多提示，请访问， TechNet 杂志 》 提示索引.

日志记录存在 SQL Server 支持各种操作。 它确保了如果发生了崩溃，提交的事务将正确反映在数据库崩溃之后。 它确保一个未提交的事务将被正确回滚并不反映数据库中出现故障后。 它确保可以取消的未决事务并具有所有操作都回滚。 它允许事务日志执行，以便可以还原数据库和重播使数据库到特定点时事务一致性，事务日志备份的备份副本。 并支持依赖于读取事务日志的如复制、 数据库镜像，和更改数据捕获的功能。

这些用途的日志记录的大多数都涉及一种称为恢复的机制。 恢复是更改日志记录重放或还原数据库中所述的过程。 重播的日志记录称为恢复 （或前滚) 恢复的阶段。 恢复的日志记录称为撤消 （或回滚） 的恢复阶段。 换句话说，恢复将确保交易记录和所有构成的日志记录恢复或撤消。

简单的窗体的恢复才在单个事务将取消，在这种情况下是撤消，并且没有该数据库没有网络影响。 最复杂的表单是故障恢复当 SQL Server 崩溃 （对于任何原因） 并必须在到事务性一致点使数据库恢复事务日志。 这意味着在崩溃时已提交的所有事务必须都滚到确保它们的影响会保留在数据库中。 和具有不提交在崩溃时所有正在进行事务必须回滚以确保它们的影响保存在数据库中。

这是因为不没有 SQL Server 中交易记录的任何功能，来出现故障后继续。 因此，如果部分完成事务的影响不回滚，数据库将会处于不一致状态 （可能甚至结构上损坏，根据交易记录的操作的过程中）。

那么如何恢复知道如何操作？ 所有恢复过程都取决于每个日志记录标记日志序列号 (LSN) 这一事实。 日志序列号是唯一地定义交易记录日志中的日志记录的位置的不断增加，三部分号。 在事务中的每个日志记录存储在事务日志中的顺序，并包含事务 ID 和以前的日志记录，交易记录的 LSN。 换句话说，记录作为交易记录的一部分每个操作都有一个"链接"返回到立即进行前面的操作。

针对单个事务回滚的简单情况，恢复机制可以轻松地快速按照记录的操作的链在最近的操作中返回第一个操作和撤消按相反顺序发生的操作的效果。 受交易记录的数据库页是仍在缓冲池中或在磁盘上。 在任何一的种情况下可用页的图像一定要在交易记录的效果将反映在页上并必须撤消。

故障恢复，过程中该机制是更复杂的。 数据库页不写入磁盘事务提交时这一事实意味着磁盘上的数据库页的一组才精确反映的事务日志中所述的更改集不能保证为提交或未提交事务。 但是，没有一我还没有提到的测验题的最后一次错误，所有的数据库页有其页面标题 （包含有关页面的元数据的 8192 字节页的一个 96 字节部分） 中的一个字段包含影响页的最后一个日志记录的 LSN。 这允许恢复系统决定要执行有关特定日志记录，它必须恢复的操作：

  • 对于从数据库页其中有一个 LSN 等于或大于日志记录的 LSN 提交事务的日志记录，Nothing 需要进行。 在页上磁盘具有已被保留日志记录的效果。
  • 从数据库页其中有一个 LSN 小于非日志记录的 LSN 提交事务日志记录，必须日志记录恢复以确保交易记录影响会保留。
  • 从数据库页其中有一个 LSN 等于或大于日志记录的 LSN 的未提交事务日志记录，必须将日志记录撤消以确保交易记录效果不会保留。
  • 对于从其中数据库页具有的 LSN，小于不是日志记录的 LSN 的未提交事务的日志记录，Nothing 需要进行。 日志记录的效果不保留在页上磁盘，因此不需要要撤消。

故障恢复事务日志中读取，并确保所有效果的所有提交的事务都保留在该的数据库，所有未提交事务的所有效果都都保存在数据库中，分别恢复和撤消阶段。 一旦故障恢复完成，数据库是事务性一致且可用于。

我前面提到在检查点操作的用法之一是为了减少的崩溃恢复需要的时间量。 通过定期从所有脏页刷新到磁盘，被减少的页的已更改由于的提交的事务，但其图像不在磁盘上的数量。 这，将反过来，减少需要恢复恢复故障恢复期间应用的页面数。

事务日志

如果事务日志是不变，故障恢复将是唯一的可能。 实际上，事务日志是数据库的最重要部分） 的唯一数据库的所有更改都保证进行描述的故障的位置。

如果事务日志丢失或损坏故障后，然后故障恢复无法完成，导致可疑数据库。 在这种情况下数据库必须从备份还原或恢复使用如紧急模式下修复之类的不需要选项。 （这些步骤不在本文的讨论范围之内但以后一年中将介绍文章中的深入）。

事务日志是一个特殊的文件，每个数据库必须具有正常工作。 它包含生成从日志记录，用于人再次放映恢复 （或任意其他使用了已经提到的日志记录的） 阅读的日志记录。 以及所占用的日志记录自己在空间，交易记录也将保留任何潜在的日志记录，需要交易记录已被取消，并且需要回滚事务日志中的空间。 行为此帐户您可能会发现位置，说，更新数据库中的数据的 50MB 的事务可能实际上需要的事务日志空间的 100 MB。

创建新数据库时，事务日志为实质上是空。 交易记录发生，日志记录按顺序写入到事务日志这意味着从创建多个事务日志文件没有性能增益一个很常见的误解。 事务日志将依次使用每个日志文件。

并发事务的日志记录可以被 interspersed 交易记录日志中。 请记住在单个事务的日志记录通过链接其 LSNs，因此不需要一起组合在日志中交易记录的所有日志记录。 LSNs 可几乎被视为一个时间戳。

事务日志的物理体系结构如 图 1 所示。 它分为在内部称为虚拟日志文件 （或 VLF) 的较小块。 这些是简单地变得更容易内部管理事务日志的辅助。 当一个 VLF 变满时，自动登录继续使用事务日志中的下一个 VLF。 您可能会认为最终事务日志会耗尽空间，但这在事务日志与数据文件因此不同。

fig01.gif

图 1 记录的交易记录的物理体系结构

事务日志是非常循环文件，只要日志记录在事务日志的开头已被截断 （或清除）。 然后当记录到达事务日志的末尾时, 它再次环绕开始并开始覆盖之前存在的。

那么，如何日志记录获取截断以便重复使用它们所占用的空间？ 以下条件均满足时，日志记录已不再需要事务日志中：

  • 已提交的事务的是一部分。
  • 进行更改的库页已所有写入到磁盘的检查点。
  • 日志记录不需要进行备份 （完整、 差异或日志）。
  • 日志记录不需要读取日志 （如数据库镜像或复制） 的任何功能。

仍需要的日志记录调用活动，并包含至少一个活动的日志记录的一个 VLF 也被称为活动。 个如此频繁事务日志检查以查看或不完整的 VLF 中的所有日志记录都是否活动 ； 如果都它们所有的非活动在 VLF 为标记为截断 （表示事务日志换行后，可以覆盖在 VLF）。 当一个 VLF 被截断时，它不是覆盖或以任何方式清零，则它会只标记为被截断，然后可重复使用。

此过程称为日志截断，不必与实际收缩事务日志的大小混淆。 日志截断操作将永远不会更改事务日志的物理大小仅事务日志的部分处于活动状态或不。 图 2 发生截断之后，从 图 1 显示事务日志。

fig02.gif

图 2 </a0>-事务日志之后日志截断操作

逻辑日志的活动 VLF 生成，包含所有活动日志记录的事务日志的部分。 数据库本身知道在故障恢复应该开始读取日志的活动的部分中的日志记录，在日志，（这存储在数据库启动页） 的 MinLSN 中最旧的活动事务的开始。

故障恢复不知道要停止读取日志记录以便继续直到它到达事务日志的 zeroed 的节 （如果事务日志已不还) 或其奇偶校验位不适合从以前的日志记录顺序的日志记录。

VLF 会截断以及新的处于活动状态，逻辑日志移动实际交易记录日志文件中和最终应该环绕若要在开始再次中, 所示 图 3 .

fig03.gif

图 3 的事务日志的循环特性

检查是否日志截断操作可发生在下列情况之一：

  • 当检查点时发生在 SIMPLE 恢复模型或其他恢复模式中永远不会被认为完整备份。 （这意味着数据库完整数据库备份发生之前切换超出 SIMPLE 后将保留在 pseudo-SIMPLE 恢复模式)。
  • 当日志备份完成。

请记住，日志截断操作不能因许多日志记录必须保持活动状态的原因。 当日志截断不会发生，不能被截断，VLF 且最终事务日志增长 （或添加另一个事务日志文件）。 过多的事务日志增长可能导致通过一种现象，称为 VLF 碎片的性能问题。 删除 VLF 碎片有时会导致性能日志相关的活动的一个显著改进。

对此的详细信息，请参阅 Kimberly Tripp 日志过帐" 最好事务日志吞吐量 8 的步骤." Kimberly 讨论与交易记录日志容量计划、 管理和性能改进有关的最佳做法，非常值得读取 ！

有两个常见的问题可阻止日志截断操作：

  • 长时间运行的活动事务。 整个事务日志因为第一个日志记录从最旧的活动事务可永远不会被截断该事务之前提交，或中止。
  • 切换到完全恢复模型，执行完整的备份，然后永远不会采取任何日志备份。 在整个事务日志将保留当前等待进行备份的日志备份。

有关因素和如何确定阻止日志截断操作的说明的完整列表，请参阅 SQL Server 联机丛书主题" 可以延迟日志截断的因素." 我还创建了显示不受控制的事务日志增长和如何删除 VLF 碎片的效果的视频演示，在查看此视频 screencast （和以前 screencasts SQL 主题） technetmagazine.com/videos.

如果事务日志会增长到产能，并且不能增加任何进一步，然后将报告错误 9002，您将需要采取步骤提供更多的空间如增长，日志文件、 添加另一个日志文件，或删除任何障碍日志被截断。

在任何情况下应您删除事务日志、 尝试重新生成它使用未记录的命令或只是截断使用了 NO _ LOG 或 TRUNCATE _ ONLY 选项的 BACKUP LOG 已删除 SQL Server 2008 中）。 这些选项将导致事务性不一致和多个可能的损坏或删除可能能够正确恢复数据库。

有关如何解决完整的事务日志的更多信息，查看出联机丛书主题" 完整的事务日志 （错误 9002） 的疑难解答."

恢复模型

正如您所看到的事务日志行为部分取决数据库正在使用的恢复模式。 可用，也三种恢复模式，并且它们都使对交易记录日志问题或如何记录操作或两者都产生影响。

在完全恢复模式意味着每个操作的各个部分会记录，这称为完全记录。 一旦完整数据库备份被认为在完全恢复模型，事务日志将不会自动截断之前执行日志备份。 如果您不希望使日志备份的使用和能够在数据库恢复到特定的点时，不会使用完全恢复模式。 但是，如果您希望使用数据库镜像，则有任何的选择为它只支持在完全恢复模式。

BULK_LOGGED 恢复模型具有相同的事务日志截断语义与完全恢复模型但允许某些操作无法部分记录的调用至少记录。 示例包括一个索引重建和某些大容量加载操作，在完全恢复模式记录整个操作。

但 BULK_LOGGED 恢复模型只在分配中的更改将记录，极大地减少生成的日志记录的数量，并依次，减少事务日志增长的可能性。 有关至少记录的操作的详细信息，请参阅联机丛书部分" 可以被至少记录的操作."

最后，SIMPLE 恢复模型实际上具有相同的日志记录行为，以在 BULK_LOGGED 恢复，但事务日志截断语义是完全不同。 日志备份不可能 SIMPLE 恢复模型表示日志可以被截断 （只要没有其他持有活动的日志记录） 中当检查点时。 有优点和缺点的备份是可能 （或需要） 和能够在时间 （我将介绍这另一文章中更高版本本年度) 恢复到各个点这些恢复模式的每个。

向上覆盖

这篇文章实际上已更 Academic 说明如何 SQL Server 工作的关键部分。 我希望我已经设法清除任何可能已经的错误理解。 如果这是您第一个简介日志记录和恢复，以下是我希望您可以从本文的要点：

  • 不要创建多个的日志文件，因为它不会导致性能增益。
  • 注意使用您的数据库恢复模型和它对事务日志的效果，尤其是围绕是否可以自动截断，或不当检查点时。
  • 认识的事务日志增长可能，可能会导致和如何获取它的因素回控制。
  • 知道查找帮助进行故障排除完整的事务日志时的位置。

在我的博客，我拥有事务日志，它影响的因素的更多信息，请参阅我的博客张贴内容类别" 事务日志"有关详细信息。 有关事务日志在各种联机丛书主题也是很好，开头 事务日志管理.

始终，如果有任何反馈或问题，请删除我在一行 Paul@SQLskills.com.

由于为 Kimberly L。 提供此文章进行技术审阅 Tripp。

Randal Paul S。 被管理的 Director SQLskills.com和 SQL Server MVP。 他效 SQL Server 存储引擎团队 Microsoft 从 1999 年可以 2007。 Paul 写 DBCC CHECKDB / 修复 SQL Server 2005 但负责核心存储引擎 SQL Server 2008 开发过程中。 Paul 在灾难恢复、 高可用性和数据库维护的专家且为常规的演示者在世界各地的会议。 在他博客 SQLskills.com/blogs/Paul.