首要原因 1/2：不了解恢复模型

Question 1

简短答案

你可能正在运行一个长时间运行的事务（索引维护？大批量删除或更新？）或者你处于“默认”（下面将详细介绍默认的含义）恢复模型中满的并且没有采取日志备份（或者服用频率不够）。

如果是恢复模型问题，简单的答案可能是切换到简单的如果您不需要时间点恢复和定期日志备份，则可以使用恢复模型。但是，许多人在不了解恢复模型的情况下就将其作为答案。请继续阅读以了解它的重要性，然后决定您要做什么。您也可以开始进行日志备份并继续使用满的恢復模式。

可能还有其他原因，但这些是最常见的。本回答开始深入探讨最常见的两个原因，并为您提供一些关于原因背后的原因和方式的背景信息，并探讨一些其他原因。

更长的答案

哪些情况会导致日志不断增长？原因有很多，但通常是以下两种模式之一：对恢复模型存在误解或存在长时间运行的事务。请继续阅读以了解详细信息。

首要原因 1/2：不了解恢复模型

（在里面完全恢复模型并且不服用日志备份- 这是最常见的原因）

虽然这个答案并没有深入探讨 SQL Server 恢复模型，但恢复模型的主题对于这个问题至关重要。

在 SQL Server 中，有三个恢复模式：

满的
批量记录
简单的

我们将忽略批量记录目前来说。我们可以说这是一种混合模型，大多数人采用这种模型都是有原因的，并且了解恢复模型。

我们关心的这两个问题，以及它们带来的困惑，是导致大多数人遇到这个问题的原因是简单的和满的。

中场休息：总体恢复情况

在讨论恢复模型之前，我们先来谈谈恢复的一般情况。如果你想更深入地了解这个话题，请阅读Paul Randal 的博客以及你想发多少帖子都可以。不过对于这个问题：

崩溃/重启恢复

事务日志文件的一个目的是崩溃/重启恢复. 用于对崩溃或重启前已完成的工作进行前滚和回滚（前滚/重做），以及对崩溃或重启后已开始但尚未完成的工作进行前滚和回滚（回滚/撤消）。

事务日志的工作是查看事务是否已启动但未完成（事务提交前发生回滚或崩溃/重启）。在这种情况下，日志的工作是说“嘿...这件事还没真正结束，让我们回顾一下”在恢复期间。日志的工作还包括查看您是否完成了某件事，并且您的客户端应用程序被告知它已完成（即使它还没有固化到您的数据文件中），并说“嘿..这真的发生了，让我们继续前进，让它像应用程序认为的那样”重启后。现在还有更多，但这是主要目的。
时间点恢复

事务日志文件的另一个目的是使我们能够恢复到时间点由于数据库中的“oops”，或者在发生涉及数据库的数据和/或日志文件的硬件故障时保证恢复点。

如果此事务日志包含已启动和完成的事务的记录，以便进行恢复，则 SQL Server 可以并且确实会使用此信息将数据库恢复到问题发生之前的状态。但这对我们来说并不总是可行的选择。要做到这一点，我们必须将数据库置于正确的位置恢复模式，我们必须采取日志备份。

恢复模型

进入恢复模型：

简单恢复模型

通过上面的介绍，首先讨论简单恢复模型是最简单的。在此模型中，您要告诉 SQL Server：“我同意你使用事务日志文件进行崩溃和重启恢复……”（你真的没有选择。抬头看看ACID 属性这应该很快就会有意义。“...但是一旦您不再需要它来进行崩溃/重启恢复，请继续重复使用日志文件。”

SQL Server 在简单恢复中监听此请求，并仅保留执行崩溃/重启恢复所需的信息。一旦 SQL Server 确定它可以恢复，因为数据已（或多或少）固化到数据文件，已固化的数据在日志中不再需要，并被标记为截断 - 这意味着它会被重新使用。
完整恢复模型

通过完整恢复，您可以告诉 SQL Server，只要您的日志文件可用，您就可以恢复到特定的时间点，或者恢复到日志备份涵盖的特定时间点。

在这种情况下，当 SQL Server 达到在简单恢复模式下可以安全截断日志文件的程度时，它不会这样做。相反，它让日志文件继续增长并使其继续增长，直到你进行日志备份（或者在正常情况下日志文件驱动器的空间不足）。

从简单到完整的切换有一个陷阱

这里有规则和例外。我们将在下面深入讨论长期运行的事务。

对于完整恢复模式，需要注意的一点是：如果你只是切换到完整恢复，但从未进行初始完整备份，SQL Server 将不是尊重您使用完整恢复模式的请求。您的事务日志将继续像在简单恢复中一样运行，直到您切换到完整恢复并进行第一次完整备份。

没有日志备份的完整恢复模型很糟糕

那么，导致原木生长不受控制的最常见原因是什么？答案：使用完全恢复模型没有任何日志备份。

有时候是这样的全部把时间交给人们。

为什么这是一个如此常见的错误？

为什么总是发生这种情况？因为每个新数据库都是通过查看模型数据库来获取其初始恢复模型设置。

模型的初始恢复模型设置始终是完整的 - 除非有人更改它。因此，您可以说“默认恢复模型”是完整的。许多人没有意识到这一点，他们的数据库在完整恢复下运行，没有日志备份，因此事务日志文件比必要的大得多。这就是为什么当默认值不适合您组织及其需求时更改默认值很重要的原因。

日志备份太少的完整恢复模型是不好的

如果不经常进行日志备份，您也可能会遇到麻烦。每天进行一次日志备份听起来不错，因为这样可以减少恢复所需的恢复命令，但请记住上面的讨论，除非您进行日志备份，否则日志文件会不断增长。

我如何知道我需要的日志备份频率？

在考虑日志备份频率时，您需要牢记两点：

恢复需求

希望这是第一个。如果存储事务日志的驱动器发生故障，或者出现影响日志备份的严重损坏，会丢失多少数据？如果该数字不超过 10-15 分钟，那么您需要每 10-15 分钟进行一次日志备份，讨论结束。
对数增长

如果您的组织因为能够轻松重新创建那一天而愿意丢失更多数据，那么您可能可以接受日志备份频率远低于 15 分钟的情况。

也许您的组织每 4 小时备份一次就够了。但您必须查看 4 小时内生成了多少事务。允许日志在这 4 小时内不断增长是否会使日志文件过大？这是否意味着您的日志备份时间过长？

首要原因 2/2：长时间运行的事务

（“我的恢复模型很好！日志仍在增长！”）

这也可能是日志增长不受控制和限制的原因。无论恢复模型如何，但它经常出现“但是我正在简单恢复中 - 为什么我的日志仍在增长？！”

这里的原因很简单：如果 SQL Server 使用事务日志进行恢复目的（如我上面所述），那么它必须回顾事务的开始。

如果您有一个需要很长时间或进行大量更改的事务，则日志无法在检查点截断仍在打开的事务中或自该事务启动以来已开始的任何更改。

这意味着，一次大规模删除（在一条删除语句中删除数百万行）是一个事务，并且日志在整个删除完成之前无法进行任何截断。在完全恢复中，这可能是大量的日志记录。维护窗口期间的索引优化工作也是如此。这也意味着糟糕的事务管理以及不监视和关闭打开的事务可能会真正损害您和您的日志文件。

对于这些长期运行的交易我能做些什么呢？

您可以通过以下方式拯救自己：

适当调整日志文件的大小，以应对最坏的情况 - 例如维护或已知的大型操作。当日志文件增大时，您应该注意这一点指导（以及她发给你的两个链接）作者：Kimberly Tripp。在这里，合适的尺寸至关重要。
注意事务的使用情况。不要在应用程序服务器中启动事务并开始与 SQL Server 进行长时间对话，否则可能会冒着将事务保持打开状态太久的风险。
观看自动提交事务在您的 DML 语句中。

例如：UPDATE TableName Set Col1 = 'New Value'是一个事务。我没有在BEGIN TRAN那里放置，也不必这样做，它仍然是一个事务，完成后会自动提交。在对大量行执行操作时，请考虑将这些操作分批处理为更易于管理的块，并给日志时间进行恢复。或者考虑处理该问题的正确大小。或者也许考虑在批量加载窗口期间更改恢复模型。

这两个原因是否也适用于日志传送？

简短回答：是的。详细答案如下。

问题：“我正在使用日志传送，因此我的日志备份是自动的...为什么我仍然看到事务日志增长？”

答案：继续阅读。

什么是日志传送？

日志传送就是字面意思 - 您将事务日志备份传送到另一台服务器以进行 DR。有一些初始化，但之后的过程相当简单：

在一台服务器上备份日志的作业，
复制该日志备份的作业
无需在目标服务器上进行恢复（NORECOVERY或）即可进行还原的作业。STANDBY

还有一些工作需要监控，如果事情没有按照计划进行，则会发出警报。

在某些情况下，您可能只想每天、每三天或每周进行一次日志传送还原。这很好。但是，如果您对所有作业（包括日志备份和复制作业）进行此更改，则意味着您要等待所有时间才能进行日志备份。这意味着您将有大量日志增长——因为您在没有日志备份的完整恢复模型中—— 这可能还意味着要复制一个很大的日志文件。您应该只修改恢复作业的计划，并让日志备份和复制更频繁地发生，否则您将遭受此答案中描述的第一个问题。

通过状态代码进行常规故障排除

除了这两个原因之外，还有其他原因，但这两个是最常见的。无论原因是什么：您都可以分析这种无法解释的日志增长/缺乏截断的原因，并查看它们是什么。

通过查询sys.databases目录视图中，您可以看到描述日志文件可能正在等待截断/重用的原因的信息。

有一列称为log_reuse_wait原因代码的查找 ID，还有一log_reuse_wait_desc列是等待原因的描述。参考的在线书籍文章中包含了大部分原因（您可能看到的原因和我们可以解释原因的原因。缺失的原因要么是不再使用，要么是内部使用），以及一些关于等待的注释斜体：

0 = 无

听起来..不应该等待。
1 = 检查点

等待检查点发生。这应该会发生，您应该没问题 - 但有些情况需要在此处查找，以便稍后找到答案或进行编辑。
2 = 日志备份

您正在等待日志备份。要么您已安排好备份时间，备份很快就会发生，要么您遇到了此处描述的第一个问题，现在您知道如何修复它。
3 = 主动备份或恢复

数据库上正在运行备份或恢复操作。
4 = 活动事务

有一个活动事务需要完成（无论哪种方式 -ROLLBACK或COMMIT）才能备份日志。这是此答案中描述的第二个原因。
5 = 数据库镜像

在高性能镜像情况下，镜像可能落后或存在延迟，或者由于某种原因镜像可能暂停。
6 = 复制

*复制中可能会出现问题，从而导致这种情况 - 例如日志读取器代理未运行、数据库认为它被标记为复制但实际上不再如此，以及其他各种原因。

您也可以看到这个原因，这是完全正常的，因为您正处于正确的时间，正如事务正在被日志读取器使用一样。*
7 = 数据库快照创建

创建数据库快照时，如果您在创建快照的恰当时刻查看，就会看到这一点。
8 = 日志扫描

我还没有遇到过这种情况一直持续下去的问题。如果你观察的时间足够长、频率足够高，你就会发现这种情况发生了，但据我所知，这不应该是事务日志过度增长的原因。
9 = 可用性组辅助副本正在将此数据库的事务日志记录应用于相应的辅助数据库。 关于迄今为止最清晰的描述。

Answer

简短答案

你可能正在运行一个长时间运行的事务（索引维护？大批量删除或更新？）或者你处于“默认”（下面将详细介绍默认的含义）恢复模型中满的并且没有采取日志备份（或者服用频率不够）。

如果是恢复模型问题，简单的答案可能是切换到简单的如果您不需要时间点恢复和定期日志备份，则可以使用恢复模型。但是，许多人在不了解恢复模型的情况下就将其作为答案。请继续阅读以了解它的重要性，然后决定您要做什么。您也可以开始进行日志备份并继续使用满的恢復模式。

可能还有其他原因，但这些是最常见的。本回答开始深入探讨最常见的两个原因，并为您提供一些关于原因背后的原因和方式的背景信息，并探讨一些其他原因。

更长的答案

哪些情况会导致日志不断增长？原因有很多，但通常是以下两种模式之一：对恢复模型存在误解或存在长时间运行的事务。请继续阅读以了解详细信息。

首要原因 1/2：不了解恢复模型

（在里面完全恢复模型并且不服用日志备份- 这是最常见的原因）

虽然这个答案并没有深入探讨 SQL Server 恢复模型，但恢复模型的主题对于这个问题至关重要。

在 SQL Server 中，有三个恢复模式：

满的
批量记录
简单的

我们将忽略批量记录目前来说。我们可以说这是一种混合模型，大多数人采用这种模型都是有原因的，并且了解恢复模型。

我们关心的这两个问题，以及它们带来的困惑，是导致大多数人遇到这个问题的原因是简单的和满的。

中场休息：总体恢复情况

在讨论恢复模型之前，我们先来谈谈恢复的一般情况。如果你想更深入地了解这个话题，请阅读Paul Randal 的博客以及你想发多少帖子都可以。不过对于这个问题：

崩溃/重启恢复

事务日志文件的一个目的是崩溃/重启恢复. 用于对崩溃或重启前已完成的工作进行前滚和回滚（前滚/重做），以及对崩溃或重启后已开始但尚未完成的工作进行前滚和回滚（回滚/撤消）。

事务日志的工作是查看事务是否已启动但未完成（事务提交前发生回滚或崩溃/重启）。在这种情况下，日志的工作是说“嘿...这件事还没真正结束，让我们回顾一下”在恢复期间。日志的工作还包括查看您是否完成了某件事，并且您的客户端应用程序被告知它已完成（即使它还没有固化到您的数据文件中），并说“嘿..这真的发生了，让我们继续前进，让它像应用程序认为的那样”重启后。现在还有更多，但这是主要目的。
时间点恢复

事务日志文件的另一个目的是使我们能够恢复到时间点由于数据库中的“oops”，或者在发生涉及数据库的数据和/或日志文件的硬件故障时保证恢复点。

如果此事务日志包含已启动和完成的事务的记录，以便进行恢复，则 SQL Server 可以并且确实会使用此信息将数据库恢复到问题发生之前的状态。但这对我们来说并不总是可行的选择。要做到这一点，我们必须将数据库置于正确的位置恢复模式，我们必须采取日志备份。

恢复模型

进入恢复模型：

简单恢复模型

通过上面的介绍，首先讨论简单恢复模型是最简单的。在此模型中，您要告诉 SQL Server：“我同意你使用事务日志文件进行崩溃和重启恢复……”（你真的没有选择。抬头看看ACID 属性这应该很快就会有意义。“...但是一旦您不再需要它来进行崩溃/重启恢复，请继续重复使用日志文件。”

SQL Server 在简单恢复中监听此请求，并仅保留执行崩溃/重启恢复所需的信息。一旦 SQL Server 确定它可以恢复，因为数据已（或多或少）固化到数据文件，已固化的数据在日志中不再需要，并被标记为截断 - 这意味着它会被重新使用。
完整恢复模型

通过完整恢复，您可以告诉 SQL Server，只要您的日志文件可用，您就可以恢复到特定的时间点，或者恢复到日志备份涵盖的特定时间点。

在这种情况下，当 SQL Server 达到在简单恢复模式下可以安全截断日志文件的程度时，它不会这样做。相反，它让日志文件继续增长并使其继续增长，直到你进行日志备份（或者在正常情况下日志文件驱动器的空间不足）。

从简单到完整的切换有一个陷阱

这里有规则和例外。我们将在下面深入讨论长期运行的事务。

对于完整恢复模式，需要注意的一点是：如果你只是切换到完整恢复，但从未进行初始完整备份，SQL Server 将不是尊重您使用完整恢复模式的请求。您的事务日志将继续像在简单恢复中一样运行，直到您切换到完整恢复并进行第一次完整备份。

没有日志备份的完整恢复模型很糟糕

那么，导致原木生长不受控制的最常见原因是什么？答案：使用完全恢复模型没有任何日志备份。

有时候是这样的全部把时间交给人们。

为什么这是一个如此常见的错误？

为什么总是发生这种情况？因为每个新数据库都是通过查看模型数据库来获取其初始恢复模型设置。

模型的初始恢复模型设置始终是完整的 - 除非有人更改它。因此，您可以说“默认恢复模型”是完整的。许多人没有意识到这一点，他们的数据库在完整恢复下运行，没有日志备份，因此事务日志文件比必要的大得多。这就是为什么当默认值不适合您组织及其需求时更改默认值很重要的原因。

日志备份太少的完整恢复模型是不好的

如果不经常进行日志备份，您也可能会遇到麻烦。每天进行一次日志备份听起来不错，因为这样可以减少恢复所需的恢复命令，但请记住上面的讨论，除非您进行日志备份，否则日志文件会不断增长。

我如何知道我需要的日志备份频率？

在考虑日志备份频率时，您需要牢记两点：

恢复需求

希望这是第一个。如果存储事务日志的驱动器发生故障，或者出现影响日志备份的严重损坏，会丢失多少数据？如果该数字不超过 10-15 分钟，那么您需要每 10-15 分钟进行一次日志备份，讨论结束。
对数增长

如果您的组织因为能够轻松重新创建那一天而愿意丢失更多数据，那么您可能可以接受日志备份频率远低于 15 分钟的情况。

也许您的组织每 4 小时备份一次就够了。但您必须查看 4 小时内生成了多少事务。允许日志在这 4 小时内不断增长是否会使日志文件过大？这是否意味着您的日志备份时间过长？

首要原因 2/2：长时间运行的事务

（“我的恢复模型很好！日志仍在增长！”）

这也可能是日志增长不受控制和限制的原因。无论恢复模型如何，但它经常出现“但是我正在简单恢复中 - 为什么我的日志仍在增长？！”

这里的原因很简单：如果 SQL Server 使用事务日志进行恢复目的（如我上面所述），那么它必须回顾事务的开始。

如果您有一个需要很长时间或进行大量更改的事务，则日志无法在检查点截断仍在打开的事务中或自该事务启动以来已开始的任何更改。

这意味着，一次大规模删除（在一条删除语句中删除数百万行）是一个事务，并且日志在整个删除完成之前无法进行任何截断。在完全恢复中，这可能是大量的日志记录。维护窗口期间的索引优化工作也是如此。这也意味着糟糕的事务管理以及不监视和关闭打开的事务可能会真正损害您和您的日志文件。

对于这些长期运行的交易我能做些什么呢？

您可以通过以下方式拯救自己：

适当调整日志文件的大小，以应对最坏的情况 - 例如维护或已知的大型操作。当日志文件增大时，您应该注意这一点指导（以及她发给你的两个链接）作者：Kimberly Tripp。在这里，合适的尺寸至关重要。
注意事务的使用情况。不要在应用程序服务器中启动事务并开始与 SQL Server 进行长时间对话，否则可能会冒着将事务保持打开状态太久的风险。
观看自动提交事务在您的 DML 语句中。

例如：UPDATE TableName Set Col1 = 'New Value'是一个事务。我没有在BEGIN TRAN那里放置，也不必这样做，它仍然是一个事务，完成后会自动提交。在对大量行执行操作时，请考虑将这些操作分批处理为更易于管理的块，并给日志时间进行恢复。或者考虑处理该问题的正确大小。或者也许考虑在批量加载窗口期间更改恢复模型。

这两个原因是否也适用于日志传送？

简短回答：是的。详细答案如下。

问题：“我正在使用日志传送，因此我的日志备份是自动的...为什么我仍然看到事务日志增长？”

答案：继续阅读。

什么是日志传送？

日志传送就是字面意思 - 您将事务日志备份传送到另一台服务器以进行 DR。有一些初始化，但之后的过程相当简单：

在一台服务器上备份日志的作业，
复制该日志备份的作业
无需在目标服务器上进行恢复（NORECOVERY或）即可进行还原的作业。STANDBY

还有一些工作需要监控，如果事情没有按照计划进行，则会发出警报。

在某些情况下，您可能只想每天、每三天或每周进行一次日志传送还原。这很好。但是，如果您对所有作业（包括日志备份和复制作业）进行此更改，则意味着您要等待所有时间才能进行日志备份。这意味着您将有大量日志增长——因为您在没有日志备份的完整恢复模型中—— 这可能还意味着要复制一个很大的日志文件。您应该只修改恢复作业的计划，并让日志备份和复制更频繁地发生，否则您将遭受此答案中描述的第一个问题。

通过状态代码进行常规故障排除

除了这两个原因之外，还有其他原因，但这两个是最常见的。无论原因是什么：您都可以分析这种无法解释的日志增长/缺乏截断的原因，并查看它们是什么。

通过查询sys.databases目录视图中，您可以看到描述日志文件可能正在等待截断/重用的原因的信息。

有一列称为log_reuse_wait原因代码的查找 ID，还有一log_reuse_wait_desc列是等待原因的描述。参考的在线书籍文章中包含了大部分原因（您可能看到的原因和我们可以解释原因的原因。缺失的原因要么是不再使用，要么是内部使用），以及一些关于等待的注释斜体：

0 = 无

听起来..不应该等待。
1 = 检查点

等待检查点发生。这应该会发生，您应该没问题 - 但有些情况需要在此处查找，以便稍后找到答案或进行编辑。
2 = 日志备份

您正在等待日志备份。要么您已安排好备份时间，备份很快就会发生，要么您遇到了此处描述的第一个问题，现在您知道如何修复它。
3 = 主动备份或恢复

数据库上正在运行备份或恢复操作。
4 = 活动事务

有一个活动事务需要完成（无论哪种方式 -ROLLBACK或COMMIT）才能备份日志。这是此答案中描述的第二个原因。
5 = 数据库镜像

在高性能镜像情况下，镜像可能落后或存在延迟，或者由于某种原因镜像可能暂停。
6 = 复制

*复制中可能会出现问题，从而导致这种情况 - 例如日志读取器代理未运行、数据库认为它被标记为复制但实际上不再如此，以及其他各种原因。

您也可以看到这个原因，这是完全正常的，因为您正处于正确的时间，正如事务正在被日志读取器使用一样。*
7 = 数据库快照创建

创建数据库快照时，如果您在创建快照的恰当时刻查看，就会看到这一点。
8 = 日志扫描

我还没有遇到过这种情况一直持续下去的问题。如果你观察的时间足够长、频率足够高，你就会发现这种情况发生了，但据我所知，这不应该是事务日志过度增长的原因。
9 = 可用性组辅助副本正在将此数据库的事务日志记录应用于相应的辅助数据库。 关于迄今为止最清晰的描述。

Question 2

因为我对任何答案都不满意在 Stack Overflow 上，包括得票最多的建议，而且因为我想解决一些 Mike 的答案没有解决的问题，所以我想在这里也提供我的意见。我也把这个答案的副本放在那里。

缩小日志文件实际上应该只用于遇到意外增长的情况，而您不希望这种情况再次发生。如果日志文件再次增长到相同的大小，那么暂时缩小它不会有什么效果。现在，根据数据库的恢复目标，您应该采取以下措施。

首先，进行完整备份

在没有确保万一出现问题可以恢复的情况下，切勿对数据库进行任何更改。

如果你关心时间点恢复

（通过时间点恢复，我的意思是您关心的是能够恢复到除完整备份或差异备份之外的任何内容。）

假设您的数据库处于FULL恢复模式。如果没有，请确保它处于：

ALTER DATABASE yourdb SET RECOVERY FULL;

即使你定期进行完整备份，日志文件也会不断增长，直到你执行日志备份 - 这是为了保护您，而不是不必要地占用您的磁盘空间。您应该根据您的恢复目标经常执行这些日志备份。例如，如果您有一条业务规则，规定在发生灾难时您可以承受不少于 15 分钟的数据丢失，那么您应该有一项每 15 分钟备份一次日志的工作。这是一个脚本，它将根据当前时间生成带时间戳的文件名（但您也可以使用维护计划等来执行此操作，只是不要选择维护计划中的任何缩减选项，它们很糟糕）。

DECLARE @path NVARCHAR(255) = N'\\backup_share\log\yourdb_' 
  + CONVERT(CHAR(8), GETDATE(), 112) + '_'
  + REPLACE(CONVERT(CHAR(8), GETDATE(), 108),':','')
  + '.trn';

BACKUP LOG foo TO DISK = @path WITH INIT, COMPRESSION;

请注意，\\backup_share\应该在代表不同底层存储设备的另一台机器上。将这些备份到同一台机器（或使用相同底层磁盘的另一台机器，或位于同一物理主机上的不同虚拟机）实际上对您没有帮助，因为如果机器崩溃，您就会丢失数据库和它的备份。根据您的网络基础设施，在本地备份然后将它们传输到后台的其他位置可能更有意义；无论哪种情况，您都希望尽快将它们从主数据库计算机中移除。

现在，一旦您运行了常规日志备份，将日志文件缩小到比现在更合理的大小应该是合理的。这确实不是意味着要SHRINKFILE反复运行，直到日志文件达到 1 MB - 即使您经常备份日志，它仍然需要容纳可能发生的任何并发事务的总和。日志文件自动增长事件的代价很高，因为 SQL Server 必须将文件清零（与启用即时文件初始化时的数据文件不同），并且用户事务必须等待这种情况发生。您希望尽可能少地执行这种增长-收缩-增长-收缩例程，并且您肯定不想让您的用户为此付出代价。

请注意，您可能需要备份日志两次才能实现缩减（感谢罗伯特）。

因此，您需要为日志文件确定一个实际的大小。如果您不了解您的系统，没有人能告诉您这个大小，但如果您经常缩小日志文件，而它又开始增长，那么一个好的水位可能比它的最大水位高 10-50%。假设它达到 200 MB，并且您希望任何后续的自动增长事件为 50 MB，那么您可以这样调整日志文件大小：

USE [master];
GO
ALTER DATABASE Test1 
  MODIFY FILE
  (NAME = yourdb_log, SIZE = 200MB, FILEGROWTH = 50MB);
GO

请注意，如果日志文件当前大于 200 MB，您可能需要先运行以下命令：

USE yourdb;
GO
DBCC SHRINKFILE(yourdb_log, 200);
GO

如果你不关心时间点恢复

如果这是一个测试数据库，并且您不关心时间点恢复，那么您应该确保您的数据库处于SIMPLE恢复模式。

ALTER DATABASE yourdb SET RECOVERY SIMPLE;

将数据库置于SIMPLE恢复模式将确保 SQL Server 重新使用日志文件的部分内容（本质上是逐步淘汰不活动的事务），而不是不断增大以保留以下记录：全部事务（就像FULL恢复一样，直到您备份日志）。CHECKPOINT事件将有助于控制日志，并确保它不需要增长，除非您在CHECKPOINTs 之间生成大量的 t-log 活动。

接下来，您应该绝对确定日志增长确实是由于异常事件（例如，每年春季大扫除或重建最大的索引）引起的，而不是由于正常的日常使用。如果您将日志文件缩小到非常小的大小，而 SQL Server 只需再次增加它即可适应您的正常活动，那么您获得了什么？您是否能够利用您暂时释放的磁盘空间？如果您需要立即修复，则可以运行以下命令：

USE yourdb;
GO
CHECKPOINT;
GO
CHECKPOINT; -- run twice to ensure file wrap-around
GO
-- 200 MB
DBCC SHRINKFILE(yourdb_log, 200);
GO

否则，设置适当的大小和增长率。按照时间点恢复案例中的示例，您可以使用相同的代码和逻辑来确定哪个文件大小合适，并设置合理的自动增长参数。

有些事你不想做

使用TRUNCATE_ONLY选项备份日志，然后SHRINKFILE。首先，此TRUNCATE_ONLY选项已被弃用，在当前版本的 SQL Server 中不再可用。其次，如果您处于FULL恢复模式，这将破坏您的日志链并需要新的完整备份。
分离数据库，删除日志文件，然后重新附加。我无法强调这有多危险。您的数据库可能无法恢复，可能会出现可疑情况，您可能必须恢复到备份（如果有的话），等等。等等。
使用“收缩数据库”选项.DBCC SHRINKDATABASE并使用维护计划选项执行相同操作都是坏主意，特别是如果您真的只需要解决日志问题。使用DBCC SHRINKFILE或ALTER DATABASE ... MODIFY FILE（上述示例），找到要调整的文件并单独调整它。
将日志文件缩小到 1 MB。这看起来很诱人，因为，嘿，SQL Server 会让我在某些情况下这样做，并查看它释放的所有空间！除非您的数据库是只读的（它是只读的，您应该使用将其标记为只读ALTER DATABASE），否则这绝对只会导致许多不必要的增长事件，因为无论恢复模式如何，日志都必须容纳当前事务。暂时释放该空间有什么意义，只是为了让 SQL Server 缓慢而痛苦地收回它？
创建第二个日志文件。这将暂时缓解已占满磁盘的驱动器的压力，但这就像试图用创可贴修补被刺破的肺一样。您应该直接处理有问题的日志文件，而不是仅仅增加另一个潜在问题。除了将某些事务日志活动重定向到其他驱动器外，第二个日志文件实际上对您没有任何作用（与第二个数据文件不同），因为一次只能使用其中一个文件。Paul Randal 还解释了为什么多个日志文件可能会在日后给你带来麻烦。

主动

不要将日志文件缩小到某个小规模并让其不断以较小的速率自动增长，而是将其设置为某个合理的大规模（可以容纳最大并发事务集总和），并设置合理的自动增长设置作为后备，这样它就不必多次增长来满足单个事务，并且在正常业务运营期间其增长的情况相对较少。

这里最糟糕的设置是 1 MB 的增长或 10% 的增长。有趣的是，这些是 SQL Server 的默认设置（我曾经抱怨过，要求改变但没有结果) - 1 MB 用于数据文件，10% 用于日志文件。前者在当今时代太小了，而后者每次都会导致越来越长的事件（例如，您的日志文件是 500 MB，第一次增长是 50 MB，下一次增长是 55 MB，下一次增长是 60.5 MB，等等 - 在慢速 I/O 上，相信我，您真的会注意到这个曲线）。

进一步阅读

请不要就此止步；虽然您看到的许多有关缩小日志文件的建议本质上都是不好的，甚至可能带来灾难性的后果，但有些人更关心数据完整性而不是释放磁盘空间。

Answer

因为我对任何答案都不满意在 Stack Overflow 上，包括得票最多的建议，而且因为我想解决一些 Mike 的答案没有解决的问题，所以我想在这里也提供我的意见。我也把这个答案的副本放在那里。

缩小日志文件实际上应该只用于遇到意外增长的情况，而您不希望这种情况再次发生。如果日志文件再次增长到相同的大小，那么暂时缩小它不会有什么效果。现在，根据数据库的恢复目标，您应该采取以下措施。

首先，进行完整备份

在没有确保万一出现问题可以恢复的情况下，切勿对数据库进行任何更改。

如果你关心时间点恢复

（通过时间点恢复，我的意思是您关心的是能够恢复到除完整备份或差异备份之外的任何内容。）

假设您的数据库处于FULL恢复模式。如果没有，请确保它处于：

ALTER DATABASE yourdb SET RECOVERY FULL;

即使你定期进行完整备份，日志文件也会不断增长，直到你执行日志备份 - 这是为了保护您，而不是不必要地占用您的磁盘空间。您应该根据您的恢复目标经常执行这些日志备份。例如，如果您有一条业务规则，规定在发生灾难时您可以承受不少于 15 分钟的数据丢失，那么您应该有一项每 15 分钟备份一次日志的工作。这是一个脚本，它将根据当前时间生成带时间戳的文件名（但您也可以使用维护计划等来执行此操作，只是不要选择维护计划中的任何缩减选项，它们很糟糕）。

DECLARE @path NVARCHAR(255) = N'\\backup_share\log\yourdb_' 
  + CONVERT(CHAR(8), GETDATE(), 112) + '_'
  + REPLACE(CONVERT(CHAR(8), GETDATE(), 108),':','')
  + '.trn';

BACKUP LOG foo TO DISK = @path WITH INIT, COMPRESSION;

请注意，\\backup_share\应该在代表不同底层存储设备的另一台机器上。将这些备份到同一台机器（或使用相同底层磁盘的另一台机器，或位于同一物理主机上的不同虚拟机）实际上对您没有帮助，因为如果机器崩溃，您就会丢失数据库和它的备份。根据您的网络基础设施，在本地备份然后将它们传输到后台的其他位置可能更有意义；无论哪种情况，您都希望尽快将它们从主数据库计算机中移除。

现在，一旦您运行了常规日志备份，将日志文件缩小到比现在更合理的大小应该是合理的。这确实不是意味着要SHRINKFILE反复运行，直到日志文件达到 1 MB - 即使您经常备份日志，它仍然需要容纳可能发生的任何并发事务的总和。日志文件自动增长事件的代价很高，因为 SQL Server 必须将文件清零（与启用即时文件初始化时的数据文件不同），并且用户事务必须等待这种情况发生。您希望尽可能少地执行这种增长-收缩-增长-收缩例程，并且您肯定不想让您的用户为此付出代价。

请注意，您可能需要备份日志两次才能实现缩减（感谢罗伯特）。

因此，您需要为日志文件确定一个实际的大小。如果您不了解您的系统，没有人能告诉您这个大小，但如果您经常缩小日志文件，而它又开始增长，那么一个好的水位可能比它的最大水位高 10-50%。假设它达到 200 MB，并且您希望任何后续的自动增长事件为 50 MB，那么您可以这样调整日志文件大小：

USE [master];
GO
ALTER DATABASE Test1 
  MODIFY FILE
  (NAME = yourdb_log, SIZE = 200MB, FILEGROWTH = 50MB);
GO

请注意，如果日志文件当前大于 200 MB，您可能需要先运行以下命令：

USE yourdb;
GO
DBCC SHRINKFILE(yourdb_log, 200);
GO

如果你不关心时间点恢复

如果这是一个测试数据库，并且您不关心时间点恢复，那么您应该确保您的数据库处于SIMPLE恢复模式。

ALTER DATABASE yourdb SET RECOVERY SIMPLE;

将数据库置于SIMPLE恢复模式将确保 SQL Server 重新使用日志文件的部分内容（本质上是逐步淘汰不活动的事务），而不是不断增大以保留以下记录：全部事务（就像FULL恢复一样，直到您备份日志）。CHECKPOINT事件将有助于控制日志，并确保它不需要增长，除非您在CHECKPOINTs 之间生成大量的 t-log 活动。

接下来，您应该绝对确定日志增长确实是由于异常事件（例如，每年春季大扫除或重建最大的索引）引起的，而不是由于正常的日常使用。如果您将日志文件缩小到非常小的大小，而 SQL Server 只需再次增加它即可适应您的正常活动，那么您获得了什么？您是否能够利用您暂时释放的磁盘空间？如果您需要立即修复，则可以运行以下命令：

USE yourdb;
GO
CHECKPOINT;
GO
CHECKPOINT; -- run twice to ensure file wrap-around
GO
-- 200 MB
DBCC SHRINKFILE(yourdb_log, 200);
GO

否则，设置适当的大小和增长率。按照时间点恢复案例中的示例，您可以使用相同的代码和逻辑来确定哪个文件大小合适，并设置合理的自动增长参数。

有些事你不想做

使用TRUNCATE_ONLY选项备份日志，然后SHRINKFILE。首先，此TRUNCATE_ONLY选项已被弃用，在当前版本的 SQL Server 中不再可用。其次，如果您处于FULL恢复模式，这将破坏您的日志链并需要新的完整备份。
分离数据库，删除日志文件，然后重新附加。我无法强调这有多危险。您的数据库可能无法恢复，可能会出现可疑情况，您可能必须恢复到备份（如果有的话），等等。等等。
使用“收缩数据库”选项.DBCC SHRINKDATABASE并使用维护计划选项执行相同操作都是坏主意，特别是如果您真的只需要解决日志问题。使用DBCC SHRINKFILE或ALTER DATABASE ... MODIFY FILE（上述示例），找到要调整的文件并单独调整它。
将日志文件缩小到 1 MB。这看起来很诱人，因为，嘿，SQL Server 会让我在某些情况下这样做，并查看它释放的所有空间！除非您的数据库是只读的（它是只读的，您应该使用将其标记为只读ALTER DATABASE），否则这绝对只会导致许多不必要的增长事件，因为无论恢复模式如何，日志都必须容纳当前事务。暂时释放该空间有什么意义，只是为了让 SQL Server 缓慢而痛苦地收回它？
创建第二个日志文件。这将暂时缓解已占满磁盘的驱动器的压力，但这就像试图用创可贴修补被刺破的肺一样。您应该直接处理有问题的日志文件，而不是仅仅增加另一个潜在问题。除了将某些事务日志活动重定向到其他驱动器外，第二个日志文件实际上对您没有任何作用（与第二个数据文件不同），因为一次只能使用其中一个文件。Paul Randal 还解释了为什么多个日志文件可能会在日后给你带来麻烦。

主动

不要将日志文件缩小到某个小规模并让其不断以较小的速率自动增长，而是将其设置为某个合理的大规模（可以容纳最大并发事务集总和），并设置合理的自动增长设置作为后备，这样它就不必多次增长来满足单个事务，并且在正常业务运营期间其增长的情况相对较少。

这里最糟糕的设置是 1 MB 的增长或 10% 的增长。有趣的是，这些是 SQL Server 的默认设置（我曾经抱怨过，要求改变但没有结果) - 1 MB 用于数据文件，10% 用于日志文件。前者在当今时代太小了，而后者每次都会导致越来越长的事件（例如，您的日志文件是 500 MB，第一次增长是 50 MB，下一次增长是 55 MB，下一次增长是 60.5 MB，等等 - 在慢速 I/O 上，相信我，您真的会注意到这个曲线）。

进一步阅读

请不要就此止步；虽然您看到的许多有关缩小日志文件的建议本质上都是不好的，甚至可能带来灾难性的后果，但有些人更关心数据完整性而不是释放磁盘空间。

Question 3

您还可以查看日志文件的内容。为此，您可以使用未记录的fn_dblog或事务日志读取器，例如ApexSQL 日志。

它不显示索引重组，但它显示所有 DML 和各种 DDL 事件：ALTER、、、CREATE触发器DROP启用/禁用、授予/撤销权限、对象重命名。

免责声明：我是 ApexSQL 的支持工程师

Answer

您还可以查看日志文件的内容。为此，您可以使用未记录的fn_dblog或事务日志读取器，例如ApexSQL 日志。

它不显示索引重组，但它显示所有 DML 和各种 DDL 事件：ALTER、、、CREATE触发器DROP启用/禁用、授予/撤销权限、对象重命名。

免责声明：我是 ApexSQL 的支持工程师

Question 4

对于几乎所有 DBA 来说，这都是最常遇到的问题，即日志不断增长并填满磁盘。

导致事务日志变得如此之大的原因有哪些？

长期活跃交易
高日志事务，如索引重建、重新组织、批量插入、删除等。
任何配置了 HA（如复制、镜像）的 HA 都会保留日志，并且不允许其释放日志空间

为什么我的日志文件这么大？

检查表log_reuse_wait_des中的 c 列sys.databases以了解是什么阻止了日志截断：

select name, log_reuse_wait_desc 
from sys.databases

有哪些方法可以防止此问题的发生？

日志备份将帮助您控制日志增长，除非有什么东西阻碍了日志的重复使用。

当我找到根本原因并希望将我的事务日志文件调整到健康大小时，我该怎么做？

如果您已确定实际原因，请尝试按照以下页面的说明进行修复。

https://www.brentozar.com/archive/2016/03/my-favorite-system-column-log_reuse_wait_desc/

除非出现异常情况，安排适当的日志备份是处理日志增长的最佳方法。

Answer

对于几乎所有 DBA 来说，这都是最常遇到的问题，即日志不断增长并填满磁盘。

导致事务日志变得如此之大的原因有哪些？

长期活跃交易
高日志事务，如索引重建、重新组织、批量插入、删除等。
任何配置了 HA（如复制、镜像）的 HA 都会保留日志，并且不允许其释放日志空间

为什么我的日志文件这么大？

检查表log_reuse_wait_des中的 c 列sys.databases以了解是什么阻止了日志截断：

select name, log_reuse_wait_desc 
from sys.databases

有哪些方法可以防止此问题的发生？

日志备份将帮助您控制日志增长，除非有什么东西阻碍了日志的重复使用。

当我找到根本原因并希望将我的事务日志文件调整到健康大小时，我该怎么做？

如果您已确定实际原因，请尝试按照以下页面的说明进行修复。

https://www.brentozar.com/archive/2016/03/my-favorite-system-column-log_reuse_wait_desc/

除非出现异常情况，安排适当的日志备份是处理日志增长的最佳方法。

首要原因 1/2：不了解恢复模型

答案1

首要原因 1/2：不了解恢复模型

中场休息：总体恢复情况

恢复模型

从简单到完整的切换有一个陷阱

没有日志备份的完整恢复模型很糟糕

为什么这是一个如此常见的错误？

日志备份太少的完整恢复模型是不好的

我如何知道我需要的日志备份频率？

首要原因 2/2：长时间运行的事务

对于这些长期运行的交易我能做些什么呢？

这两个原因是否也适用于日志传送？

什么是日志传送？

通过状态代码进行常规故障排除

答案2

首先，进行完整备份

如果你关心时间点恢复

如果你不关心时间点恢复

有些事你不想做

主动

进一步阅读

答案3

答案4

相关内容