解决简单恢复模式下产生的日志增长

　　最近测试服务器进行数据归档，其间程序员发现一个问题，空间不足，我查看原因发现日志文件暴涨。然后将数据库改为简单恢复模式，但是依然存在这个问题。经过查询资料发现了日志文件在简单模式下依然增加的原因。

　　Simple恢复模式也叫做”Checkpoint with truncate log“，其实这个名字更形象，在Simple模式下，SQL Server会在每次checkpoint或backup之后自动截断log，也就是丢弃所有的闲置日志记录，仅保留用于实例启动时自动发生的instance recovery所需的少量log，这样做的好处是log文件非常小，不需要DBA去维护、备份log，但坏处也是显而易见的，就是一旦数据库出现异常，需要恢复时，最多只能恢复到上一次的备份，无法恢复到最近可用状态，因为log丢失了。

1. 一些Internal CheckPoint时，比如说关闭数据库实例等。
2. 数据库完整备份或差异备份(日志备份不会触发checkpoint)。
5. 手动执行CheckPoint。

　　Simple模式主要用于非critical的业务，比如开发库和测试库，那么这次由于测试环境的磁盘紧张我们也都采用了简单模式。但是数据归档发生时依然产生了大量的日志，并且增加了磁盘占用，这又是什么原因那？因为我们在归档处理中使用了大量的insert和delete以及update操作，这样话，短时间内产生了大量的日志，这个时候日志迅速增加；又因为在SQL Server中，CheckPoint是一个完整的过程，这个过程的耗时取决于脏数据的大小。一旦在很短时间内，日志的CheckPoint没完成的时候日志增加超过了日志的规定上限。则将产生更多的日志。

　　如上所述，产生这个问题的原因就是：CheckPoint时间间隔阈值被足够多的日志记录超过,触发CheckPoint才写入磁盘。

　　下面这个实例来自于：

?????

?　? 让我们用一个脚本来实际的阐明这种行为。首先在一个测试数据库中运行一下脚本创建一个测试表并填充一些数据。

　　测试数据库设置：

　　1.设置为简单的恢复模式。

　　2.日志的大小为100M。

　　3.日志文件的自动增长被禁用（因为观察日志空间被用完的错误比检查自动增长要容易）。

?　　运行以下脚本，观察资源竞争：

<span style="color: #0000ff;"><span style="color: #0000ff;">set<span style="color: #000000;">?nocount?<span style="color: #0000ff;">on<span style="color: #000000;"> <span style="color: #0000ff;">go<span style="color: #000000;"><span style="color: #0000ff;">declare<span style="color: #000000;">?<span style="color: #008000;">@change_size<span style="color: #000000;">?<span style="color: #0000ff;">int<span style="color: #000000;"> <span style="color: #0000ff;">set<span style="color: #000000;">?<span style="color: #008000;">@change_size<span style="color: #000000;">?<span style="color: #808080;">=<span style="color: #000000;">?<span style="color: #800000; font-weight: bold;">100<span style="color: #000000;">?<span style="color: #008080;">--<span style="color: #008080;">?根据需要来调整这个值<span style="color: #008080;"><span style="color: #0000ff;">declare<span style="color: #000000;">?<span style="color: #008000;">@i<span style="color: #000000;">?<span style="color: #0000ff;">int<span style="color: #000000;"> <span style="color: #0000ff;">set<span style="color: #000000;">?<span style="color: #008000;">@i<span style="color: #000000;">?<span style="color: #808080;">=<span style="color: #000000;">?<span style="color: #800000; font-weight: bold;">1<span style="color: #000000;"> <span style="color: #0000ff;">while<span style="color: #000000;">?<span style="color: #008000;">@i<span style="color: #000000;">?<span style="color: #808080;"><<span style="color: #000000;">?<span style="color: #800000; font-weight: bold;">100<span style="color: #000000;"> ?????<span style="color: #0000ff;">begin<span style="color: #000000;"> ??????????<span style="color: #0000ff;">if<span style="color: #000000;">?<span style="color: #008000;">@i<span style="color: #000000;">?<span style="color: #808080;">%<span style="color: #000000;">?<span style="color: #800000; font-weight: bold;">2<span style="color: #000000;">?<span style="color: #808080;">=<span style="color: #000000;">?<span style="color: #800000; font-weight: bold;">0<span style="color: #000000;"> ?????????????? <span style="color: #0000ff;">update<span style="color: #000000;">?test?<span style="color: #0000ff;">set<span style="color: #000000;">?c?<span style="color: #808080;">=<span style="color: #000000;">?<span style="color: #ff00ff;">replicate<span style="color: #000000;">(<span style="color: #ff0000;">'<span style="color: #ff0000;">a<span style="color: #ff0000;">'<span style="color: #000000;">,?<span style="color: #008000;">@change_size<span style="color: #000000;">)? ??????????<span style="color: #0000ff;">else<span style="color: #000000;">? ?????????????<span style="color: #0000ff;">　　update<span style="color: #000000;">?test?<span style="color: #0000ff;">set<span style="color: #000000;">?c?<span style="color: #808080;">=<span style="color: #000000;">?<span style="color: #ff00ff;">replicate<span style="color: #000000;">(<span style="color: #ff0000;">'<span style="color: #ff0000;">b<span style="color: #ff0000;">'<span style="color: #000000;">,?<span style="color: #008000;">@change_size<span style="color: #000000;">) ?????????? ???????????<span style="color: #0000ff;">select<span style="color: #000000;">?<span style="color: #008000;">@i<span style="color: #000000;">?<span style="color: #808080;">=<span style="color: #000000;">?<span style="color: #008000;">@i<span style="color: #000000;">?<span style="color: #808080;">+<span style="color: #000000;">?<span style="color: #800000; font-weight: bold;">1<span style="color: #000000;"> ??????<span style="color: #0000ff;">end
<div class="cnblogs_code_toolbar"><span class="cnblogs_code_copy"><a title="复制代码" onclick="copyCnblogsCode(this)" href="javascript:void(0);">

<img src="https://www.52php.cn/res/2019/01-31/09/51e409b11aa51c150090697429a953ed.gif" alt="复制代码">

?反复根据修改@change_size来看结果，当我将@change_size改为120甚至更大时，得到了9002的错误信息，非常准确的告诉我数据库的事务日志已满。

通过上面这个引用的例子，很好地再现了问题的产生机制，那么我们怎么处理这个情况那？

?默认值是0，意味着由SQL Server来管理这个回复间隔。 　　

?也可以SQL语句实现这个功能：

　　方案3：

　　增大日志文件大小。

（编辑：李大同）

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