sql-server – 为什么事务日志会继续增长或空间不足？

In SQL Server –

• What are some reasons the transaction log grows so large?
• Why is my log file so big?
• What are some ways to prevent this problem from occurring?
• 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?

解决方法

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

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

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

更长的答案：
哪些方案可以使日志继续增长？原因有很多,但通常这些原因有以下两种模式：对恢复模型存在误解或存在长时间运行的事务.继续阅读以了解详情.

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

(处于完全恢复模式而不进行日志备份 – 这是最常见的原因 – 绝大多数遇到此问题的人都是.)

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

在SQL Server中,有三个recovery models：

>完整,
> Bulk-Logged和
>简单.

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

我们关心的两个和他们的困惑是造成这个问题的人的大多数情况的原因是简单和完整.

中场休息：恢复一般

在我们讨论恢复模型之前：让我们来谈谈恢复.如果您想更深入地了解这个主题,请阅读Paul Randal’s blog以及您想要的任意数量的帖子.但是对于这个问题：

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

恢复模型

在恢复模型上：

>简单恢复模型
因此,通过上述介绍,首先讨论简单恢复模型是最容易的.在这个模型中,您告诉SQL Server：“我很好,您使用事务日志文件进行崩溃并重新启动恢复…”(您真的没有选择.查找ACID properties,这应该很快有意义.)“ …但是一旦你不再需要它来实现崩溃/重启恢复目的,请继续并重用日志文件.“

SQL Server在Simple Recovery中侦听此请求,它仅保留崩溃/重新启动恢复所需的信息.一旦SQL Server确定它可以恢复,因为数据已经硬化到数据文件(或多或少),在日志中不再需要硬化的数据并标记为截断 – 这意味着它会被重用.
>完全恢复模型
使用完全恢复,您告诉SQL Server您希望能够恢复到特定时间点,只要您的日志文件可用或日志备份所涵盖的特定时间点.在这种情况下,当SQL Server达到可以安全地截断简单恢复模型中的日志文件时,它不会这样做.相反,它允许日志文件继续增长并允许它继续增长,直到您在正常情况下进行日志备份(或日志文件驱动器上的空间不足).

从简单切换到完全有一个陷阱.

这里有规则和例外.我们将在下面深入讨论长期交易.

但是对于完全恢复模式需要记住的一个警告是：如果您只是切换到完全恢复模式,但从未进行初始完全备份,则SQL Server将不会遵守您的完全恢复模式请求.您的事务日志将继续在Simpleuntil中运行,您可以切换到完全恢复模型并进行第一次完全备份.

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

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

这种情况一直发生在人们面前.

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

为什么会一直这样？因为每个新数据库都通过查看模型数据库来获取其初始恢复模型设置.

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

日志备份太少的完全恢复模型很糟糕.

您也可以通过不经常进行日志备份来解决自己的问题.
每天进行日志备份可能听起来不错,它使恢复需要的恢复命令更少,但请记住上面的讨论,该日志文件将继续增长并增长,直到您进行日志备份.

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

您需要考虑两个方面考虑日志备份频率：

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

主要原因2/2：长期交易

(“我的恢复模式很好！日志仍在增长！)

这也可能是不受控制和无限制的对数增长的原因.无论恢复模式如何,它通常都会出现“但我在简单恢复模型中 – 为什么我的日志仍在增长？！”

这里的原因很简单：如果SQL正在使用此事务日志进行恢复,如上所述,那么它必须回到事务的开始.

如果您的事务需要很长时间或进行大量更改,则日志不能在检查点上截断仍处于打开事务中的任何更改或自该事务启动以来已启动的任何更改.

这意味着删除一个删除语句中的数百万行的大删除是一个事务,并且在完成整个删除之前日志不能执行任何截断操作.在完全恢复模型中,将记录此删除,这可能是许多日志记录.维护窗口期间的索引优化工作也是如此.这也意味着糟糕的事务管理而不是观察和关闭打开的事务可能真的会伤害你和你的日志文件.

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

你可以在这里保存自己：

>正确调整日志文件的大小以考虑最坏的情况 – 例如维护或已知的大型操作.当你发展你的日志文件时,你应该看看Kimberly Tripp的guidance(以及她发给你的两个链接).正确的尺寸在这里是非常关键的.
>观察您对交易的使用情况.不要在应用程序服务器中启动事务并开始与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可用性组辅助副本将此数据库的事务日志记录应用于相应的辅助数据库.关于最清楚的描述..

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!