我设置了三个维护计划在 Sql Server 2005 实例上运行：
- 每周进行数据库优化，然后进行完整备份
- 每日差异备份
- 每小时事务日志备份
根据活动级别，每小时日志备份通常在几百 Kb 到 10Mb 之间，到周末每日差异通常会增长到 250Mb 左右，每周备份约为 3.5Gb。
我遇到的问题是，完整备份之前的优化似乎导致下一个事务日志备份增长到完整备份大小的 2 倍以上，在本例中为 8Gb，然后才恢复正常。
除此之外BACKUP LOG <DatabaseName> WITH TRUNCATE_ONLY，还有什么方法可以减少日志备份的大小，或者完全防止优化记录在事务日志中，因为它们肯定会在之前的完整备份中得到说明？
答案1
这里有一些有趣的建议，但似乎都表明了对日志备份工作原理的误解。日志备份包含自上次日志备份以来生成的所有事务日志，无论在此期间进行了什么完整或差异备份。停止日志备份或转为每日完整备份不会影响日志备份大小。一旦日志备份链启动，唯一影响事务日志的就是日志备份。
此规则的唯一例外是如果日志备份链已中断（例如，转到 SIMPLE 恢复模型、从数据库快照恢复、使用 BACKUP LOG WITH NO_LOG/TRUNCATE_ONLY 截断日志），在这种情况下，第一个日志备份将包含自上次完整备份以来的所有事务日志 - 这将重新启动日志备份链；或者，如果日志备份链尚未启动 - 当您第一次切换到 FULL 时，您将在一种伪 SIMPLE 恢复模型下操作，直到进行第一次完整备份。
回答你最初的问题，如果不使用简单恢复模式，你将不得不备份所有事务日志。根据你采取的操作，你可以更频繁地备份日志以减少其大小，或者进行更有针对性的数据库备份。
如果您可以发布一些有关您正在进行的维护操作的信息，我可以帮助您优化它们。您是否会先进行索引重建，然后收缩数据库以回收索引重建所使用的空间？
如果维护期间数据库中没有其他活动，则可以执行以下操作：
- 确保用户活动已停止
- 进行最后的日志备份（这可以让你恢复到维护开始的时间点）
- 切换到简单恢复模式
- 执行维护 - 日志将在每个检查点截断
- 切换到完整恢复模式并进行完整备份
- 继续正常
希望这会有所帮助 - 期待更多信息。
谢谢
[编辑：在讨论了完整备份是否可以改变后续日志备份的大小（不能）之后，我整理了一篇全面的博客文章，其中包含背景材料和证明这一点的脚本。请查看https://www.sqlskills.com/blogs/paul/misconceptions-around-the-log-and-log-backups-how-to-convince-yourself/]
答案2
您可以缩小它们，但它们会再次增长，最终导致磁盘碎片。索引重建和碎片整理会产生非常大的事务日志。如果您不需要时间点可恢复性，您可以更改为简单恢复模式并完全取消事务日志备份。
我猜您正在使用维护计划进行优化，您可以将其更改为使用仅在达到特定碎片级别时才执行索引碎片整理的脚本，这样您不太可能遭受任何性能损失。这将生成小得多的日志。
顺便说一句，我会跳过每日差异备份，而选择每日完整备份。
答案3
你的最后一个问题是：“除了使用 TRUNCATE_ONLY 备份日志之外，还有其他方法可以减少日志备份的大小，或者完全防止优化记录在事务日志中，因为它们肯定会在之前的完整备份中得到说明吗？”
不，但这里有一个解决方法。如果您知道当时该数据库中的唯一活动将是索引维护作业，那么您可以在索引维护开始之前停止事务日志备份。例如，我的一些服务器在周六晚上的作业计划如下：
- 晚上 9:30 – 事务日志备份运行。
- 晚上 9:45 - 最后一次运行事务日志备份。计划于 9:59 停止。
- 晚上 10:00 – 索引维护工作开始，并设有内置停止点，将于 11:30 之前完成。
- 晚上 11:30 – 完整备份工作开始并在 30 分钟内完成。
- 12:00 AM – 事务日志备份每 15 分钟重新开始一次。
这意味着我无法在晚上 9:45 到 11:30 之间进行时间点恢复，但回报是更快的性能。
答案4
您还可以研究第三方工具（Quest 的 Litespeed、Red Gate 的 SQL Backup、Hyperbac）来减少备份和日志的大小。它们可以快速节省磁带成本。