备份后事务日志大幅增长
我是一名程序员，副业是非自愿的 DBA。
我有一个维护计划，每天晚上进行完整备份和“检查数据库完整性”。我每 10 分钟备份一次事务日志。数据库备份后，事务日志备份大小激增 - 呈指数级增长。我曾经每天晚上重建索引和统计数据 - 我认为这就是导致事务日志激增的原因 - 但删除这些步骤并没有改变任何东西。
如果没有如此巨大的峰值，在慢速连接上镜像我们的备份将会有很大帮助 - 所以我希望这是我做错的事情。任何人都可以提出任何建议吗？
I am a programmer, with a side job as an involuntary DBA.
I have a maintenance plan that does a full backup and a 'check database integrity' every night. I backup transaction logs every 10 minutes. The transaction log backup size spikes after the database backup - exponentially bigger. I used to rebuild indexes and statistics every night - I thought that is what was causing the transaction log spike - but removing those steps didn't change anything.
Mirroring our backups on slow connections would be helped considerably if there wasn't this massive spike - so I am hoping it is something I am doing wrong. Can anyone suggest anything?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
如果您仅从早上 6 点到午夜运行日志备份，那么早上 6 点的第一个日志备份将备份自上次日志备份以来 6 小时内发生的所有数据库活动。
这是完全正常的，可能与数据库备份发生在凌晨 4 点这一事实无关。
由于您使用的是 SQL2008，因此我的其他答案中的警告不适用，并且您应该可以正常运行 24 小时日志备份。
If you are only running the log backup from 6am to midnight, then the very first log backup at 6am is backing up all the database activity that has occurred in the 6 hours since the last log backup.
This is entirely normal, and probably has nothing to do with the fact that your database backup takes place at 4am.
Since you are on SQL2008, the warning in my other answer doesn't apply, and you should be fine with running the log backups 24 hours.
这是 SQL 2000 吗？
在 SQL 2000 中，您不应该在执行完整备份时运行日志备份，否则“可能会发生不好的事情”，例如阻塞或日志文件过大。
请参阅此 ServerFault 帖子，了解“The Word” Man”，Paul Randal，曾在微软负责 SQL 引擎。
请参阅这篇后续文章，了解一些跳过的想法执行完整备份时记录备份。
在 SQL 2005 或更高版本中，此限制不再存在，并且您同时运行日志备份和完整备份应该不会遇到问题。
Is this SQL 2000?
In SQL 2000, you're not supposed to run the log backup while the full backup is executing, or "bad things can happen", like blocking, or hugely bloated log files.
See this ServerFault post for "The Word" from "The Man", Paul Randal, who used to be in charge of the SQL engine at Microsoft.
See this follow-up post for some ideas for skipping the log backup while the full backup is executing.
In SQL 2005 or later, this restriction no longer exists, and you shouldn't have trouble running log backups and full backups at the same time.
当完整备份运行时，事务备份将不会运行。那么完整备份需要多长时间？在此期间，事务日志备份不会被截断。
While your full backup is running, transaction backups will not run. So how long does your full backup take? The transaction log will not be truncated during this time by transaction log backups.