为什么事务日志持续增长或空间不足？

#1 楼

简短的答案：

您可能正在运行长时间运行的事务（索引维护？是大批删除还是更新？），或者您处于“默认”状态（有关默认含义，请参见下文） Full的恢复模式，并且没有进行日志备份（或者没有足够频繁地进行备份）。

如果是恢复模型问题，简单的答案可能是：如果您不需要时间点恢复和常规日志备份，则切换到Simple恢复模式。但是，许多人在回答问题时却不了解恢复模型。继续阅读以了解其重要性，然后决定要做什么。您也可以开始进行日志备份并保持恢复状态。

还有其他原因，但这是最常见的。该答案将开始探讨最常见的两个原因，并为您提供原因以及背后原因的背景信息，并探讨其他一些原因。

更长的答案：
什么情况会导致日志持续增长？原因有很多，但是通常这些原因有以下两种模式：对恢复模型有误解，或者有长期运行的事务。继续阅读以获取详细信息。

最主要的原因1/2：不了解恢复模型

（处于完全恢复模式并且不进行日志备份-这是最常见的原因-

虽然此答案不是对SQL Server恢复模型的深入探讨，但是恢复模型的主题对于此问题至关重要。

在SQL Server中，有三种恢复模型：

现在我们将忽略Full，我们会说它是一个混合模型，并且该模型中的大多数人都在这里出于某种原因并了解恢复模型。

我们关心的两个问题以及它们造成的混乱是造成Full和Bulk-Logged问题的大多数原因。
间歇性：总体恢复情况
谈论恢复模型：让我们总体来讲谈恢复。如果您想进一步探讨该主题，请阅读Paul Randal的博客以及您想要的更多帖子。但是，对于这个问题，


崩溃/重新启动恢复
事务日志文件的一个目的是崩溃/重新启动恢复。对于在崩溃或重新启动之前完成的工作（向前滚动/重做）的前滚和后滚，以及在崩溃或重新启动之后已完成但尚未完成的工作（回滚/撤消）。事务日志的工作是查看事务已开始但从未完成（在提交事务之前发生回滚或崩溃/重新启动）。在这种情况下，日志的工作是在恢复过程中说“嘿。这还没有真正完成，让我们回滚”。这也是日志的工作，以确保您确实完成了某些工作，并告知客户端应用程序已完成（即使尚未将其硬化到数据文件中），并说：“嘿。这确实发生了，让我们滚动一下向前，让我们将其重新启动后就像应用程序认为的那样。现在还有更多，但这是主要目的。
时间恢复中的要点
事务日志文件的另一个目的是使我们能够恢复到由于数据库中的“ oops”而导致的时间点，或者能够在涉及数据和数据的硬件故障的情况下保证恢复点。 /或数据库的日志文件。如果此事务日志包含已开始和完成以进行恢复的事务记录，则SQL Server可以并且确实会使用此信息将数据库恢复到发生问题之前的位置。但这并不总是我们可用的选项。为此，我们必须使数据库具有正确的恢复模型，并且必须进行日志备份。

恢复模型

恢复模型：



简单恢复模型
因此，通过上面的介绍，最先讨论Simple模型是最容易的。在此模型中，您告诉SQL Server：“我很好，可以使用事务日志文件进行崩溃并重新启动恢复...”（您确实没有其他选择。查找ACID属性，应该很快就可以理解。） “ ...但是一旦出于崩溃/重新启动恢复的目的不再需要它，请继续并重用日志文件。”

SQL Server在简单恢复中侦听此请求，并且仅保留进行崩溃/重新启动恢复所需的信息。一旦SQL Server确定可以恢复，因为数据已经被硬化到数据文件（或多或少），则已硬化的数据将不再在日志中被使用，并被标记为截断-这意味着它可以重新使用。

完整恢复模型
使用Bulk-Logged，您告诉SQL Server您希望能够恢复到特定的时间点，只要您的日志文件可用或恢复到日志备份所涵盖的特定的时间点即可。在这种情况下，当SQL Server到达可以安全地截断Simple Recovery Model中的日志文件的位置时，它将不会这样做。相反，它可以使日志文件继续增长并允许其继续增长，直到在正常情况下进行日志备份（或日志文件驱动器上的空间不足）为止。

从简单切换为to Full有一个陷阱。

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

但是，对于完全恢复模式要记住的一个注意事项是：如果仅切换到Simple模式，但是从不进行初始完全备份，则SQL Server将不会接受您要求使用Full模型的请求。您的事务日志将继续按Simple Recovery的方式运行，直到您切换到完全恢复模型并采用第一个Full Recovery为止。那么，不受控制的日志增长的最常见原因是什么？
答案：处于完全恢复模式，没有任何日志备份。

这种情况一直在发生。

为什么这样的常见错误？

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

模型的初始恢复模型设置始终为Full Recovery-除非且除非有人进行更改。因此，您可以说“默认恢复模型”是Full Recovery。许多人没有意识到这一点，他们的数据库在Simple中运行，没有日志备份，因此事务日志文件比所需的大得多。这就是为什么在默认设置不适用于您的组织及其需求时更改默认设置很重要的原因。
日志备份太少的完全恢复模型是不好的。

如果您不经常进行日志备份也可能会惹上麻烦。
每天进行日志备份听起来不错，使得还原只需要较少的还原命令，但是请记住上面的讨论，在您进行日志备份之前，日志文件将继续增长。

如何确定我需要的日志备份频率？

您需要考虑以下两点来考虑日志备份频率：



恢复需求-希望这应该是第一位的。如果存放事务日志的驱动器出现故障或受到严重损坏而影响了日志备份，会丢失多少数据？如果该数字不超过10-15分钟，那么您需要每10-15分钟进行一次日志备份，直到讨论结束。

日志增长-如果您的组织由于能够轻松地重新创建当天而丢失更多数据，则可以以少于15分钟的频率进行日志备份。也许您的组织每4小时就可以了。但是您必须查看4个小时内产生了多少交易。允许日志在这四个小时内持续增长是否会使日志文件太大？



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

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

这也可能是不受控制且不受限制的日志增长的原因。无论恢复模型如何，它通常都会出现为“但是我处于简单恢复模型-为什么我的日志还在增长吗？！“

这里的原因很简单：如果SQL像我上面描述的那样正在使用此事务日志进行恢复，那么它必须回头看事务的开始。 />
如果您的事务需要很长时间或进行了很多更改，那么对于仍在打开的事务中或自该事务启动以来已开始的任何更改，日志无法在检查点上截断。

这意味着大的删除操作，即在一条delete语句中删除数百万行是一项事务，在完成整个删除操作之前，日志无法执行任何截断操作。在Full Backup中，记录了此删除操作，这可能是很多日志记录。在维护时段内，与索引优化工作相同。这也意味着不良的事务管理以及不监视和关闭未完成的事务确实会严重损害您和您的日志文件。

这些长期运行的事务我该怎么办？

您可以通过以下方法保存自己的位置：


正确调整日志文件的大小以解决最坏的情况-例如维护或已知的大型操作。当您增加日志文件时，您应该参考Kimberly Tripp的这份指南（以及她发送给您的两个链接）。正确调整大小在这里至关重要。
观看交易用法。不要在应用程序服务器中启动事务，不要与SQL Server进行长时间的对话，否则可能会导致打开时间太长。
观察DML语句中的隐式事务。例如：Full Recovery Model是交易。我没有在这里放一个Full，也不必这样做，它仍然是一项事务，完成后会自动提交。因此，如果对大量的行进行操作，请考虑将这些操作分批处理为更易于管理的块，并给予日志时间进行恢复。或者考虑合适的尺寸来处理。或者，也许要考虑在批量加载窗口期间更改恢复模型。


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

简短回答：是的。下面的答案更长。

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

答案：请继续阅读。

什么是日志传送？

听起来就像是日志传送-您正在将事务日志备份传送到另一台服务器以进行灾难恢复。进行了一些初始化，但是之后的过程非常简单：
用于在一个服务器上备份日志的作业，
用于复制该日志备份的作业，以及
可以在目标服务器上进行恢复而无需恢复的作业（Full Recovery Model或Full Recovery Model）。 />
在某些情况下，您可能只想每天或每三天或每周一次进行日志传送还原。那样就好。但是，如果您对所有作业（包括日志备份和复制作业）进行此更改，则意味着您正在等待所有时间进行日志备份。这意味着您将有大量的日志增长-因为您处于没有日志备份的完全恢复模式下-可能还意味着要复制整个日志文件。您只应修改还原作业的日程表，并让日志备份和副本更频繁地发生，否则您将遭受此答案中描述的第一个问题。


常规故障排除可通过状态码

除了这两个原因外，还有其他一些原因，但这是最常见的。无论原因如何：都有一种方法可以分析导致这种无法解释的日志增长/缺少截断的原因，并查看其原因。
通过查询UPDATE TableName Set Col1 = 'New Value'目录视图，您可以看到描述原因的信息。您的日志文件可能正在等待截断/重用。

有一个名为BEGIN TRAN的列，其中包含原因码的查找ID，而有一个NORECOVERY列，其中包含等待原因的描述。网上参考书籍中的大部分原因（您可能会看到的原因，以及我们可以解释的原因。缺失的原因是已停用或内部使用），其中有一些有关等待时间的注意事项斜体：


0 =没什么。.不应等待
1 = Checkpoint正在等待检查点的出现。这应该发生，并且应该很好-但在某些情况下，可以在此处查找以后的答案或进行编辑。
2 =日志备份您正在等待日志备份的发生。您已经计划好它们，并且很快就会发生，或者您遇到了这里描述的第一个问题，现在您知道如何解决此问题了。
3 =活动备份或还原数据库上正在运行备份或还原操作
4 =活动事务
在可以备份日志之前，有一个活动事务需要完成（无论是-STANDBY还是sys.databases）。这是此答案中描述的第二个原因。
5 =数据库镜像在高性能镜像情况下，镜像会落后或处于一定延迟下，或者由于某种原因镜像被暂停
6 =复制复制可能会导致复制问题，例如日志阅读器代理未运行，数据库认为它已标记为不再存在，并且出于各种其他原因。您还可以看到这个原因，这是完全正常的，因为您正在查看正确的时间，就像日志读取器正在消耗事务一样
7 =创建数据库快照正在创建数据库快照，您将看到如果您正好在创建快照的那一刻看这个的话，
8 =日志扫描我还没有遇到与此相关的问题。如果您足够长且足够频繁地查看，则可以看到发生这种情况，但这不应该是导致事务日志过度增长的原因。 9 = AlwaysOn可用性组的辅助副本正在应用事务日志该数据库的记录到相应的辅助数据库。
关于最清晰的描述。.

评论

---

页面拆分将增加日志记录。根据我的经验，在很多情况下都没有解决的可能需要频繁缩小的大量增长的重要原因（根据我的经验）是使用适当的索引选择，包括适当的FillFactor mgmt。我使用以下设置，仔细观察。 FF设置：（0/100）个具有高读/低写的表，（90）个稍加修改的表，（80）个中读/低中的写，（70）个高写，（60）我很难达到级别或其他错误。然后使用正确的索引管理计划匹配的数据量。

–SnapJag
2015年10月8日19:31

---

@SnapJag应该在docs.microsoft.com/zh-cn/sql/relational-databases/indexes/…处检出“性能注意事项”部分，然后再将索引填充系数从默认值0更改。

–乔治·比尔比利斯（George Birbilis）
5月21日14:19

---

如果客户端不需要隐式事务，那么它如何防止UPDATE语句成为隐式事务？

–venomzx
8月30日12:35

---

#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\应该位于代表不同基础存储设备的另一台计算机上。将这些备份到同一台计算机（或使用同一基础磁盘的另一台计算机，或同一物理主机上的另一台VM）并不能真正帮助您，因为如果计算机崩溃，您将丢失数据库及其备份。根据您的网络基础架构，在本地备份然后将其转移到幕后的其他位置可能更有意义。无论哪种情况，您都希望尽快将它们从主数据库计算机中删除。

现在，一旦运行了常规日志备份，就应该将日志文件压缩到更合理的位置。比现在爆炸的要多。这并不意味着一遍又一遍地运行SHRINKFILE，直到日志文件为1 MB-即使您经常备份日志，它仍然需要容纳可能发生的所有并发事务的总和。日志文件自动增长事件非常昂贵，因为SQL Server必须将文件归零（与启用即时文件初始化时的数据文件不同），并且用户事务必须等待这种情况发生。您希望尽可能少地执行此Growth-shrink-grow-shrink例程，并且您当然不想让用户为此付费。

请注意，您可能需要备份在缩小之前记录两次（感谢Robert）。

因此，您需要为日志文件提供一个实用的大小。在这里，没有人会告诉您那是什么，而不是对您的系统有更多了解，但是如果您经常缩小日志文件并且又在增长，那么一个好的水印可能比最大的水印高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事件将有助于控制日志，并确保它不需要增长，除非您在CHECKPOINT s之间生成大量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上，相信我，您会真正注意到此曲线）。

进一步阅读

请不要在这里停下来；尽管您看到的有关缩小日志文件的许多建议本质上都是不好的，甚至可能造成灾难性的影响，但是有些人更关心数据的完整性而不是释放磁盘空间。


A我在2009年撰写的博客文章中兴起，当时我看到一些“这里是如何缩小日志文件”的文章。
四年前，Brent Ozar写了一篇博客文章，指向多个资源，以响应SQL Server。不应发表的杂志文章。
保罗·兰达尔（Paul Randal）的博客文章，解释了为什么维护t-log很重要以及为什么您也不应缩小数据文件。
迈克·沃尔什（Mike Walsh）有一个很好的答案当然，以上内容也涵盖了其中一些方面，包括为什么您可能无法立即缩小日志文件的原因。

#3 楼

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

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



免责声明：我以ApexSQL的支持工程师身份工作

#4 楼

这是几乎所有日志增长并填满磁盘的DBA面临的最常见问题。

•事务日志增长如此之大的一些原因是什么？


长期有效的事务
高日志记录的事务，例如索引重建，重新组织，大容量插入，删除等。
配置了任何HA（例如复制，镜像），可保留日志并不允许其释放日志。日志空间

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

检查log_reuse_wait_des表中的sys.databases c列以了解什么导致日志被截断：

select name, log_reuse_wait_desc 
from sys.databases

•有什么方法可以防止出现此问题？

日志备份将帮助您控制日志的增长，除非有某些阻碍重用日志的东西。

•当我步入正轨时该怎么办？

如果有根本原因，并希望将我的事务日志文件设置为正常大小？

如果您确定了真正的原因，那么请尝试进行相应修复，如下页所述。
https://www.brentozar.com/archive/2016/03/my-favorite-system-column-log_reuse_wait_desc/

安排适当的日志备份是最好的处理方式除非出现异常情况，否则对数增长。