为什么事务日志会继续增长或耗尽空间?


207

这个似乎是大多数论坛和网络上的一个常见问题,这里以多种格式询问,通常听起来像这样:

在SQL Server中 -

  • 事务日志变得如此之大的原因是什么?
  • 为什么我的日志文件如此之大?
  • 有哪些方法可以防止此问题发生?
  • 当我让自己跟上根本原因并希望将我的事务日志文件设置为健康大小时,我该怎么办?
  0

真正简短的答案是:将数据库置于** Simple **模式(不是** Full **模式)。如果您在一整天的夜间备份之间没有进行多个事务日志备份:您不需要**完全**模式。 07 12月. 162016-12-07 14:48:40

  0

@IanBoyd - 肯定这是最简单的答案。但关键是要达到这意味着什么。我在答案中找到了答案。可悲的是,太多的人要么从不解决这个问题,要么只是简单地理解为什么。我会编辑我的答案以便稍早点击简单模式,但是.. 07 12月. 162016-12-07 14:53:55

270

更短的答案:

您可能要么运行一个长时间运行的事务(索引维护?大批量删除或更新?),或者您处于“默认”状态(更多以下默认值)恢复模式Full并没有采取日志备份(或者不经常服用它们)。

如果是恢复模型问题,简单的答案可能是切换到Simple如果您不需要时间点恢复和常规日志备份,则恢复模式。但是,许多人在不了解恢复模型的情况下做出了答案。继续阅读以了解其重要性,然后决定您的工作。您也可以开始使用日志备份并保持原样Full复苏。

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


更长的答案:什么情景会导致日志继续增长?原因有很多,但通常这些原因有以下两种模式:对恢复模型存在误解或存在长时间运行的事务。继续阅读以了解详情。

主要原因1/2:不了解恢复模型

在...完全恢复模式而不是日志备份- 这是最常见的原因 - 绝大多数遇到此问题的人都是。

虽然这个答案并不是SQL Server恢复模型的深层次,但恢复模型的主题对于这个问题至关重要。

在SQL Server中,有三个recovery models

  • Full
  • Bulk-Logged
  • Simple

我们会忽略Bulk-Logged现在我们可以说它是一个混合模型,大多数参与此模型的人都有理由并了解恢复模型。

我们关心的两个和他们的困惑是导致这个问题的人的大多数情况的原因SimpleFull

中场休息:恢复一般

在我们谈论恢复模型之前:让我们来谈谈恢复。如果您想更深入地了解这个主题,请阅读Paul Randal's blog和你想要的很多帖子一样。但是对于这个问题:

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

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

恢复模型

在恢复模型上:

  • 简单恢复模型
    因此,通过上述介绍,最容易谈论Simple Recovery模型第一。在这个模型中,您告诉SQL Server:“我可以使用您的事务日志文件进行崩溃并重新启动恢复...”(你真的别无选择。抬头看ACID properties这很快就会有意义。)“...但是一旦您不再需要它来实现崩溃/重启恢复目的,请继续并重用日志文件。”

    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,这个删除被记录,可能是很多日志记录。维护窗口期间的索引优化工作也是如此。这也意味着糟糕的事务管理以及不关注和关闭打开的事务可能会真正伤害您和您的日志文件。

对于这些长期运行的交易,我该怎么办?

您可以通过以下方式保存自己:

  • 正确调整日志文件的大小以考虑最坏的情况 - 例如维护或已知的大型操作。当你增长你的日志文件时,你应该看看这个guidance(和她发给你的两个链接)由金伯利特里普。正确的尺寸在这里是非常关键的。
  • 观察您对交易的使用情况。不要在您的应用程序服务器中启动事务并开始与SQL Server进行长时间的对话,并且有可能将一个事务打开太久。
  • 看着隐含交易在您的DML语句中。例如:UPDATE TableName Set Col1 = 'New Value'是一个交易。我没有放一个BEGIN TRAN在那里,我没有必要,它仍然是一个只在完成时自动提交的事务。因此,如果对大量行进行操作,请考虑将这些操作批处理为更易于管理的块,并给出恢复的日志时间。或者考虑适当的尺寸来处理。或者可以考虑在批量加载窗口期间更改恢复模型。

这两个原因也适用于Log Shipping吗?

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

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

答:请继续阅读。

什么是原木运输?

日志传送就是这样 - 您将事务日志备份传送到另一台服务器以用于DR目的。有一些初始化,但之后过程相当简单:

  • 在一台服务器上备份日志的工作,
  • 复制该日志备份的作业
  • 恢复它的工作没有恢复(或者NORECOVERY要么STANDBY)在目标服务器上。

如果事情没有按计划进行,还有一些工作需要监控和警报。

在某些情况下,您可能只想每天或每隔三天或每周一次进行日志传送恢复。那样就好。但是,如果对所有作业(包括日志备份和复制作业)进行此更改,则表示您正在等待所有时间进行日志备份。这意味着你将有很多日志增长 - 因为你是在完全恢复模式下没有日志备份- 它可能还意味着要复制的大型日志文件。您应该只修改还原作业的计划并让日志备份和副本更频繁地发生,否则您将遇到本答案中描述的第一个问题。


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

除了这两个原因之外还有其他原因,但这些是最常见的原因。无论原因如何:有一种方法可以分析这种无法解释的日志增长/缺少截断的原因,看看它们是什么。

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

有一个名为的专栏log_reuse_wait使用原因码的查找ID和alog_reuse_wait_desc列与等待原因的描述。从参考书籍在线文章的大多数原因(你可能会看到的和我们可以解释原因的那些。缺失的原因要么是不使用的,要么是内部使用的),并附有关于等待的一些注释。斜体

  • 0 =没什么
    它听起来像......不应该等待

  • 1 =检查点
    等待检查点发生。这应该发生,你应该没问题 - 但是有些情况可以在这里寻找以后的答案或编辑。

  • 2 =日志备份
    您正在等待日志备份发生。要么你安排了它们,它会很快发生,或者你有这里描述的第一个问题,你现在知道如何解决它

  • 3 =主动备份或还原
    数据库上正在运行备份或还原操作

  • 4 =主动交易
    有一个活动的交易需要完成(无论哪种方式 - ROLLBACK要么COMMIT)可以备份日志之前。这是这个答案中描述的第二个原因。

  • 5 =数据库镜像
    镜像在高性能镜像情况下落后或处于某种延迟或由于某种原因暂停镜像

  • 6 =复制
    可能存在导致此问题的复制问题 - 例如日志读取器代理未运行,数据库认为它已标记为不再存在的复制以及各种其他原因。您也可以看到这个原因并且这是完全正常的,因为您正在寻找恰当的时间,就像日志阅读器正在使用事务一样

  • 7 =创建数据库快照
    您正在创建数据库快照,如果您在创建快照时正好查看正确的时刻,您将会看到这一点

  • 8 =日志扫描
    我还没有遇到一个永远存在的问题。如果你看起来足够长,而且频繁,你可以看到这种情况发生,但它不应该是我看到的过多的事务日志增长的原因。

  • 9 = AlwaysOn可用性组辅助副本将此数据库的事务日志记录应用于相应的辅助数据库。关于最清楚的描述..

+1

页面拆分会增加日志记录。一个重要的原因(根据我的经验),可能需要经常收缩的大规模增长没有被提及已经在我的很多案例中得到解决,那就是使用适当的索引选择,包括适当的FillFactor mgmt。我使用以下设置,仔细观察。FF设置:(0/100)表具有高读/低写,(90)表示略微修改,(80)中读/低写写,(70)高写,(60)我几乎达不到这个等级或其他可能是错误的。然后使用正确的索引管理计划匹配数据量。 08 10月. 152015-10-08 19:31:03


98

因为我对任何答案都不满意over on Stack Overflow,包括最重要的投票建议,并且因为有一些我想解决的问题,迈克的答案没有,我想我也会在这里提供我的意见。我也在那里放了一份这个答案的副本。

使日志文件更小应该真正保留用于遇到意外增长的情况,而这种情况不会再发生。如果日志文件将再次增长到相同的大小,那么暂时缩小它就不会实现。现在,根据数据库的恢复目标,这些是您应该采取的操作。

首先,进行完整备份

如果没有确保您可以在出现问题时恢复数据库,切勿对数据库进行任何更改。

如果您关心时间点恢复

(通过即时恢复,我的意思是您关心能够还原到完整或差异备份以外的任何其他内容。)

大概你的数据库在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恢复直到您备份日志)。CHECKPOINT事件将有助于控制日志,并确保它不需要增长,除非您之间生成大量的t-log活动CHECKPOINT秒。

接下来,您应该绝对确保此日志增长真正是由于异常事件(例如,每年春季清洁或重建您的最大指数),而不是由于正常的日常使用。如果您将日志文件缩小到一个非常小的大小,并且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 also explains why multiple log files can bite you later

主动

而不是将您的日志文件缩小到一个小的数量并让它自己以小的速率自动进行自动增长,将其设置为一个相当大的大小(一个可以容纳最大并发事务集合的总和)并设置合理的自动增长设置为后备,因此它不必增长多次以满足单个事务,因此在正常业务操作期间它必须增长相对较少。

这里最糟糕的设置是1 MB增长或10%增长。有趣的是,这些是SQL Server的默认设置(我已经抱怨过了asked for changes to no avail) - 数据文件为1 MB,日志文件为10%。前者在这个时代太小了,后者每次都会导致更长更长的事件(例如,你的日志文件是500 MB,第一个增长是50 MB,下一个增长是55 MB,下一个增长是60.5 MB等等 - 在缓慢的I/O上,相信我,你会真正注意到这条曲线)。

进一步阅读

请不要停在这里;虽然你在那里看到的有关缩小日志文件的大部分建议本来就很糟糕甚至可能是灾难性的,但有些人更关心数据完整性而不是释放磁盘空间。


21

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

它不显示索引重组,但它显示了所有DML和各种DDL事件: ALTERCREATEDROP ,触发启用/禁用,授予/撤销权限,对象重命名。

ApexSQLLogProject.temp - ApexSQL.log

免责声明:我作为支持工程师为ApexSQL工作


1

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

•事务日志变得如此之大的原因是什么?

  1. 长期活跃交易
  2. 高度记录事务,如索引重建,重新组织,批量插入,删除等。
  3. 复制,镜像等任何HA都配置了保存日志并且不允许它释放日志空间

•为什么我的日志文件如此之大?

检查sys.databases表中的log_reuse_wait_des 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/

除非出现异常情况,否则安排正确的日志备份是处理日志增长的最佳方法。