一个简短的答案：

您可能正在运行一个长时间运行的事务（索引维护？大批量删除或更新？）或者您处于“默认”（更多关于默认含义的内容）的恢复模式Full并且没有进行日志备份（或没有足够频繁地服用它们）。

如果是恢复模式问题，Simple如果您不需要时间点恢复和定期日志备份，简单的答案可能是切换到恢复模式。但是，许多人在不了解恢复模型的情况下就给出了答案。继续阅读以了解它为什么重要，然后决定你做什么。您也可以开始进行日志备份并保持Full恢复状态。

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

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

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

（处于完全恢复模式并且不进行日志备份- 这是最常见的原因 - 绝大多数遇到此问题的人。）

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

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

• Full,
• Bulk-Logged和
• Simple.

我们现在将忽略Bulk-Logged我们会说它是一个混合模型，并且大多数处于此模型中的人都是有原因的并且了解恢复模型。

我们关心的两个和他们的困惑是大多数人遇到这个问题的原因是Simple和Full。

中场休息：总体恢复

在我们谈论恢复模型之前：让我们来谈谈一般的恢复。如果您想更深入地了解这个主题，只需阅读Paul Randal 的博客以及您想要的尽可能多的帖子。不过，对于这个问题：

1. 崩溃/重新启动恢复
   事务日志文件的用途之一是用于崩溃/重新启动恢复。对于崩溃或重启前已完成的工作（前滚/重做）的前滚和回滚，以及崩溃或重启后已开始但未完成的工作（回滚/撤消）。事务日志的工作是查看事务开始但从未完成（回滚或崩溃/重新启动发生在事务提交之前）。在那种情况下，日志的工作是在恢复期间说“嘿..这从未真正完成，让我们回滚吧”。日志的工作也是查看您确实完成了某些事情并且您的客户端应用程序被告知它已完成（即使它尚未硬化到您的数据文件）并说“嘿..这真的发生了，让我们继续前进，让它像应用程序认为的那样”重启后。现在还有更多，但这是主要目的。

2. 时间点恢复
   事务日志文件的另一个目的是能够使我们能够恢复到由于数据库中的“oops”而导致的时间点，或者在硬件故障的情况下保证恢复点涉及数据库的数据和/或日志文件。如果此事务日志包含已启动和已完成以进行恢复的事务记录，SQL Server 可以并且确实使用此信息将数据库获取到问题发生之前的位置。但这对我们来说并不总是一个可用的选择。为此，我们必须让我们的数据库处于正确的恢复模式，并且我们必须进行日志备份。

恢复模式

恢复模型：

• 简单恢复模型
  有了上面的介绍，最简单的还是先说Simple Recovery模型。在此模型中，您告诉 SQL Server：“我很乐意使用您的事务日志文件进行崩溃和重新启动恢复……”（您确实别无选择。查找ACID 属性，这应该很快就有意义了。）“......但是一旦您不再需要它来进行崩溃/重启恢复，请继续并重用日志文件。”

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

• 完整恢复模型
  使用Full Recovery，您是在告诉 SQL Server 您希望能够恢复到特定时间点，只要您的日志文件可用或恢复到日志备份涵盖的特定时间点。在这种情况下，当 SQL Server 在简单恢复模型中达到可以安全截断日志文件的程度时，它不会这样做。相反，它允许日志文件继续增长，并允许它继续增长，直到您在正常情况下进行日志备份（或日志文件驱动器上的空间不足）。

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

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

但是对于完全恢复模式要记住的一个警告是：如果您只是切换到Full Recovery模式，但从未进行初始完全备份，SQL Server 将不会满足您的模型请求Full Recovery。您的事务日志将继续按原样运行，Simple直到您切换到完全恢复模式并获得第一个Full Backup.

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

那么，不受控制的日志增长最常见的原因是什么？答：处于完全恢复模式而没有任何日志备份。

这一直发生在人们身上。

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

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

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

日志备份太少的完整恢复模式很糟糕。

如果不经常进行日志备份，您也可能在这里遇到麻烦。
每天进行一次日志备份听起来不错，它使还原需要更少的还原命令，但请记住上面的讨论，该日志文件将继续增长，直到您进行日志备份。

如何找出我需要的日志备份频率？

您需要考虑两件事来考虑您的日志备份频率：

1. 恢复需求- 这应该是第一个。如果存放事务日志的驱动器坏了，或者出现严重损坏影响日志备份的情况，会丢失多少数据？如果该数字不超过 10-15 分钟，那么您需要每 10-15 分钟进行一次日志备份，讨论结束。
2. 日志增长- 如果您的组织可以轻松地重新创建当天的数据而丢失更多数据，那么您的日志备份频率可能会低于 15 分钟。也许您的组织每 4 小时就可以了。但是你必须看看你在 4 小时内产生了多少交易。让日志在这四个小时内保持增长会导致日志文件过大吗？这是否意味着您的日志备份需要太长时间？

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

（“我的恢复模式很好！日志还在增长！）

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

这里的原因很简单：如果 SQL 使用此事务日志进行恢复，如上所述，那么它必须查看事务的开始。

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

这意味着一个大删除，在一个删除语句中删除数百万行是一个事务，并且在整个删除完成之前，日志不能进行任何截断。在Full Recovery Model中，此删除被记录，这可能是很多日志记录。维护窗口期间的索引优化工作也是如此。这也意味着糟糕的事务管理以及不注意和关闭打开的事务真的会伤害您和您的日志文件。

我可以对这些长期运行的交易做些什么？

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

• 适当调整日志文件的大小以应对最坏的情况——比如您的维护或已知的大型操作。当您增加日志文件时，您应该查看 Kimberly Tripp 的此指南（以及她发送给您的两个链接）。正确的尺寸在这里非常重要。
• 观察您对交易的使用情况。不要在您的应用程序服务器中启动事务并开始与 SQL Server 进行长时间的对话，否则可能会导致打开时间过长。
• 观察 DML 语句中的隐含事务。例如：UPDATE TableName Set Col1 = 'New Value'是一笔交易。我没有放在BEGIN TRAN那里，我也不必，它仍然是一个在完成后自动提交的事务。因此，如果对大量行进行操作，请考虑将这些操作分批成更易于管理的块并给日志时间来恢复。或者考虑合适的尺寸来解决这个问题。或者也许考虑在批量加载窗口期间更改恢复模型。

这两个原因是否也适用于 Log Shipping？

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

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

答案：继续阅读。

什么是日志传送？

日志传送就是它听起来的样子 - 您将事务日志备份传送到另一台服务器以用于 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 = AlwaysOn 可用性组辅助副本正在将此数据库的事务日志记录应用到相应的辅助数据库。 关于最清晰的描述..

由于我对 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 必须将文件归零（与启用即时文件初始化时的数据文件不同），并且用户事务必须在发生这种情况时等待。您希望尽可能少地执行这种增长-收缩-增长-收缩例程，并且您当然不想让您的用户为此付费。

请注意，您可能需要两次备份日志才能进行缩减（感谢 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在备份日志之前恢复所做的那样）。CHECKPOINTevents 将有助于控制日志并确保它不需要增长，除非您在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 可以缓慢而痛苦地收回它？

• Create a second log file. This will provide temporarily relief for the drive that has filled your disk, but this is like trying to fix a punctured lung with a band-aid. You should deal with the problematic log file directly instead of just adding another potential problem. Other than redirecting some transaction log activity to a different drive, a second log file really does nothing for you (unlike a second data file), since only one of the files can ever be used at a time. Paul Randal also explains why multiple log files can bite you later.

Be proactive

Instead of shrinking your log file to some small amount and letting it constantly autogrow at a small rate on its own, set it to some reasonably large size (one that will accommodate the sum of your largest set of concurrent transactions) and set a reasonable autogrow setting as a fallback, so that it doesn't have to grow multiple times to satisfy single transactions and so that it will be relatively rare for it to ever have to grow during normal business operations.

The worst possible settings here are 1 MB growth or 10% growth. Funny enough, these are the defaults for SQL Server (which I've complained about and asked for changes to no avail) - 1 MB for data files, and 10% for log files. The former is much too small in this day and age, and the latter leads to longer and longer events every time (say, your log file is 500 MB, first growth is 50 MB, next growth is 55 MB, next growth is 60.5 MB, etc. etc. - and on slow I/O, believe me, you will really notice this curve).

Further reading

Please don't stop here; while much of the advice you see out there about shrinking log files is inherently bad and even potentially disastrous, there are some people who care more about data integrity than freeing up disk space.

• A blog post I wrote in 2009, when I saw a few "here's how to shrink the log file" posts spring up.

• A blog post Brent Ozar wrote four years ago, pointing to multiple resources, in response to a SQL Server Magazine article that should not have been published.

• A blog post by Paul Randal explaining why t-log maintenance is important and why you shouldn't shrink your data files, either.

• Mike Walsh has a great answer above, of course, covering some of these aspects too, including reasons why you might not be able to shrink your log file immediately.

You can also see the content of your log file. To do that, you can use the undocumented fn_dblog, or a transaction log reader, such as ApexSQL Log.

It doesn't show index reorganization, but it shows all DML and various DDL events: ALTER, CREATE, DROP, trigger enable/disable, grant/revoke permissions, object rename.

Disclaimer: I work for ApexSQL as a Support Engineer

This is the most frequently faced issue for almost all the DBAs where the logs grows and fills out the disk.

•What are some reasons the transaction log grows so large?

1. Long Active Transaction
2. High logging transactions like Index rebuild, re-organise, Bulk Insert, Deletes etc.
3. Any HA like Replication, Mirroring configured which holds the log and does not allow it release the log space

•Why is my log file so big?

Check for the log_reuse_wait_desc column in sys.databases table to know what is holding the logs from truncating:

select name, log_reuse_wait_desc 
from sys.databases

•What are some ways to prevent this problem from occurring?

Log backups will help you control the log growth unless there is something that is holding up the logs from being reused.

•What do I do when I get myself on track with the underlying cause and want to put my transaction log file to a healthy size?

If you have identified what actually is causing it then try to fix it accordingly as explained in below page.

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

Having proper log backups scheduled is the best way of dealing with log growth unless for an unusual situation.