碎片整理对复制来说很困难吗？
有人告诉我，碎片整理会导致日志急剧增长。这是真的吗？如果是这样，有什么比碎片整理更好的方法，不会对日志产生太大影响？我们正在两个站点之间运行 SQL Server 2005 复制。
I've been told that defragging causes the log to grow tremendously. Is this true? If so, is there something better to do than defragging that will not impact the log as much? We are running SQL Server 2005 replicating between 2 sites.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
SQL Server 中没有“碎片整理”。您可能正在谈论索引重组操作或索引重建操作。重组对日志的影响很小，但索引重建会创建与索引大小乘以一个因子一样多的日志。对于大型索引，重建操作可能会导致日志增长。
拥有较大的日志将影响事务日志读取器代理，因为它将在一段时间内有更多的日志记录需要扫描。最终日志读取器代理会赶上。确切的数字（延迟时间、延迟大小等）会因多种因素而有所不同，您最好的选择是尝试和测量。
至于替代方案：
如果答案是“是”、“是”和“是”，并且结论是定期索引重建是不可避免的，那么就没有其他选择，您将不得不硬着头皮在校准硬件要求时考虑此操作。
There is no 'defrag' in SQL Server. You may be talking about an index reorganize operation or an index rebuild operation. Reorganize is light on log, but index rebuild creates as much log as the size of the index multiplied by a factor. For a large index the rebuild operation may result in log growth.
Having a large log will impact the transactional log reader agent simply because it will have more log records to scan through for a period. Eventually the log reader agent will catch up. The exact numbers (duration of latency, latency size etc) will differ based on a number of factors, your best choice is trial and measurement.
As for alternatives:
If the answers are Yes, Yes and Yes and the conclusion is that periodic index rebuild is unavoidable then there is no alternative, you're going to have to bite the bullet and take this operation into account when calibrating the hardware requirements.