遇到意外增长事务日志文件大小

Question

我们使用的是SQL Server 2008 R2标准版本，我们创建了维护计划Rebuild Index, Reorganized Index and update Statistic，每天安排在凌晨1点执行此维护计划后，数据库事务日志文件大小异常增大。假设主数据文件为7GB，事务日志文件将增长到55 GB。

如何在不收缩进程的情况下减少事务日志文件的大小(因为它不好--增加碎片--降低性能)

推荐的另一种解决方案是完全备份后的事务日志备份。我们做了相同的工作，并观察到文件大小没有变化，它显示的大小与以前相同，主文件为7 GB，事务日志文件为55 GB。

请建议我减少日志文件大小而不影响性能的最佳方法。

Answer 1

如何在不收缩进程的情况下减少事务日志文件的大小(因为它不好--增加碎片--降低性能)

那不是真的。日志收缩是很好的，您认为是数据收缩。有关为什么增长、如何缩小以及为什么是良性的，请参见如何收缩服务器日志。

我的第一个建议是使用智能索引维护计划。过度活跃的重建是昂贵的，有害的和完全不必要的。许多人以Ola Hallengren的索引维护脚本的名义发誓。

您还必须研究如何利用最小的日志记录。索引维护是进行最少日志记录操作的主要选择，但您必须使用数据库的大容量日志恢复模型来启用它们。请参见可以最少记录的操作：

如果数据库设置为简单或大容量日志恢复模型，则无论该操作是脱机执行还是联机执行，一些索引DDL操作都会被最少地记录下来。

Answer 2

• 1-您可能能够更好地控制增长，方法是更频繁地备份事务日志，并设置事务日志备份，使其每15分钟、24 x 7运行一次，如果活动频繁，甚至更频繁。

• 2-在完全恢复模式下，所有批量操作都完全记录在案。但是，可以通过将数据库临时切换到大容量操作的大容量日志恢复模型来最小化一组大容量操作的日志记录。最小日志记录比全日志记录更有效，并减少了在批量事务期间大规模批量操作填充可用事务日志空间的可能性。但是，如果数据库在最低日志记录生效时损坏或丢失，则无法将数据库恢复到故障点。
• 3-使用像脚本这样的单独的重建过程会得到更好的帮助。这样可以更好地控制您的运行如何重新构建/重新操作。这是一个链接，可以帮助您完成您需要的脚本！！

Answer 3

通过重建/REORGANIZE /Update这样的操作来增加日志大小是正常的，这取决于实际数据大小和它增长了多少。我们可以减少的是不必要的磁盘使用，因为不正确的实施维护计划。

第一，真诚地阅读有关MSDN BOL的信息。

索引操作恢复模型的选择

从表中您会注意到，即使您拥有简单恢复模型中的数据库，重新组织操作也是完全记录的。但重建是最低限度的记录。

索引的重组与重建

从上面的链接中，您会发现重构聚集索引而不禁用NONCLUSTERED索引会对磁盘的使用产生巨大的影响。此外，对于集群索引和非CLUSTERED索引，REBUIDL/REORGANIZE操作的顺序也很重要。所有非CLUSTERED操作都应该后面跟着聚集索引操作。

还请注意信息，它解释了两个阶段的大指数的回流。该操作的第二阶段运行在后台，不影响db性能，但不利的一面是磁盘空间将保留使用，直到该阶段完成。

现在，您可以采取哪些合乎逻辑的步骤？

0)确保数据库处于正确的恢复模型中。如果db有“完全恢复”模式，那么您也必须有正确的横截面日志备份过程。如果没有，则不需要“完全恢复”，或者您应该修改当前实现的日志备份过程。

1)检查维护计划，并确保您正在根据适当的条件进行重建或重组。(例如，碎片化百分比。)在某些情况下，您可以确定您希望为特定表重建索引。

2)检查对非CLUSTERED索引的操作是否为对聚集索引的操作。如果没有相应更新计划。

3)您使用的是标准版，所以所有操作都是脱机进行的。但也要检查SORT_IN_TEMPDB选项是否用于重新构建/重组。

4)如果(3)的回答是否定的："Whihc最有可能是因为您实际的DB日志大小增加了，而您没有提到任何有关tempdb的内容“，那么在查看了我在第(5)点中提到的检查之后，考虑使用该选项。

5)如果(3)的回答是肯定的：“这是不可能的”，但如果是这样的话，这意味着您还需要检查Tempdb配置是否出色。喜欢,

    Does it have more than one LDF and MDF file? if no have atlease 2 for each.

    Are those files are set to increase at right amount instead of default 10mb?       
    if default then start with the 1024MB increament. gradually you should find the 
    sweet spot.

    Are those Files are ristricted to some size? if so check the disk size where the 
    files are and set accordingly.

    Even If your Tempdb is not located on Seperate Disk, from the disk Actual DB is    
    on, making sure above checks is good practice amd sure help you.

6)由于收缩日志操作是不坏的，如果做得适度，同样的方式，重建/重组不是天使，当你做过头了。你应该检查一下在整个DB上是否真的需要重建/重组？可能每周或每3天工作更好。

我很高兴在您尝试以上并获得一些结果之后，再介绍有关统计的下一步/建议。我不会期望上述步骤能很好地解决你的担忧，但是的，这些措施肯定会改善你的处境。