复制加镜像 在线重建索引导致堵塞住复制 的logreader，如果搭建了镜像，那么logreader会读取mirror端的日志而不是primary端的日志
复制加镜像 在线重建索引导致堵塞住复制 的logreader，如果搭建了镜像，那么logreader会读取mirror端的日志而不是primary端的日志
复制加镜像 在线重建索引导致堵塞住复制 的logreader，如果搭建了镜像，那么logreader会读取mirror端的日志而不是primary端的日志
https://social.msdn.microsoft.com/Forums/zh-CN/15944c2b-b8ca-4585-99f6-c4b3c0b8b376/logreader-replicated-transactions-are-waiting-for-next-log-backup-or-for-mirroring-partner?forum=sqlserverzhchs同步延迟，Logreader日志显示： Replicated transactions are waiting for next Log backup or for mirroring partner to catch up
已经确定发布数据库没有打开 sync with backup 选项，而且测试验证过，没有日志备份，日志读取器工作不会受影响
跟踪标记 1448 已经打开，测试验证，手工将镜像 suspend，日志读器工作不受影响
但 LogReader agent 日志仍然会出现 Replicated transactions are waiting for next Log backup or for mirroring partner to catch up. 而且持续时间长，初步确定的现象是：
online rebuild index，这时候会产生大量日志（超过1G），完成之后日志 logreader agent 出现等待，并显示上述信息，在镜像同步完成之前不会消失（镜像是高性能模式 parter safety off),
将镜像暂停 (set partner suspend), 很快 logreader agent 继续工作，再将镜像恢复 (set partner resume), logreader agent 仍然继续工作，不再受镜像同步的影响
不知道大家有没有遇到这种问题，有没有什么彻底的解决方法
诡异的是为什么logreader agent会堵塞，镜像要 suspend 再resume就不再受影响（当然，这个suspend 再resume不是持久的，如果这样设置一次之后就再也没问题，那也算解决问题了）
顺便一提的是联机重建索引的日志开销，有时候一个几非MB的表，重建一个索引的日志开销超过10G，不管是排序放到tempdb，还是取消并行，都不会降低这种日志开销
周健
原因： As per message i checked the transaction log backups were happened or not,
checked it and log backups were happening . Finally one option
is pending to review and it is mirroring. I checked the status of mirroring and
seen principal database went to synchronizing mode.
May be mirroring went in synchronizing mode due to heavy or so many
transactions in the route to apply at mirror database. So replication is
waiting to be synchronized status of principal database. I exactly do not know
why replication went in waiting even transaction log backups were happened. I
checked online solution and received some of the solutions from here and it
suggests following,
解决办法： Enable trace flag 1448
After this setting the Log Reader Agent can continue replicating changes regardless of the mirroring state. Please read more here. I never applied these suggestions. Replication and mirroring both are using
transaction logs and this could be the reason why replication was in high delay status while mirroring was stuck or in process to synchronize mirror database.
So i turned off mirroring and monitored replication status. Finally replication was succeed to remove delay and applied all pending commands. Then i configured mirroring again after done with replication sync.
Conclusion : We have two options either to wait for synchronized status of principal database or turned of mirroring, getting replication synced properly and reconfigure mirroring again depend on priority and importance.It will be better to go for correct solution to avoid such issue
1448 我已经说了，这个是开着的，而且测试验证了是有效的，问题不在这里（麻烦再看一下我的描述）
问题最终没正确的解释
Logreader就卡在 rebuild index 产生大量日志之后，但是镜像重启一下就好了，不重启就一直卡着
最终换镜像服务器了，镜像磁盘IO加强了，镜像的处理速度与日志生成速度基本匹配了，这个问题就没了
如果搭建了镜像， 那么logreader是读取mirror端的日志，而不是primary端的日志。所以如果大量的rebuild index，出现这种情况是正常的。
即使没有镜像，rebuild index也会造成log reader读取缓慢，因为log reader要过滤大量的non-replciate日志
力伟
不知道大家有没有遇到这种问题，有没有什么彻底的解决方法
诡异的是为什么logreader agent会堵塞，镜像要 suspend 再resume就不再受影响（当然，这个suspend 再resume不是持久的，如果这样设置一次之后就再也没问题，那也算解决问题了）
顺便一提的是联机重建索引的日志开销，有时候一个几非MB的表，重建一个索引的日志开销超过10G，不管是排序放到tempdb，还是取消并行，都不会降低这种日志开销
周健
原因： As per message i checked the transaction log backups were happened or not,
checked it and log backups were happening . Finally one option
is pending to review and it is mirroring. I checked the status of mirroring and
seen principal database went to synchronizing mode.
May be mirroring went in synchronizing mode due to heavy or so many
transactions in the route to apply at mirror database. So replication is
waiting to be synchronized status of principal database. I exactly do not know
why replication went in waiting even transaction log backups were happened. I
checked online solution and received some of the solutions from here and it
suggests following,
解决办法： Enable trace flag 1448
After this setting the Log Reader Agent can continue replicating changes regardless of the mirroring state. Please read more here. I never applied these suggestions. Replication and mirroring both are using
transaction logs and this could be the reason why replication was in high delay status while mirroring was stuck or in process to synchronize mirror database.
So i turned off mirroring and monitored replication status. Finally replication was succeed to remove delay and applied all pending commands. Then i configured mirroring again after done with replication sync.
Conclusion : We have two options either to wait for synchronized status of principal database or turned of mirroring, getting replication synced properly and reconfigure mirroring again depend on priority and importance.It will be better to go for correct solution to avoid such issue
1448 我已经说了，这个是开着的，而且测试验证了是有效的，问题不在这里（麻烦再看一下我的描述）
问题最终没正确的解释
Logreader就卡在 rebuild index 产生大量日志之后，但是镜像重启一下就好了，不重启就一直卡着
最终换镜像服务器了，镜像磁盘IO加强了，镜像的处理速度与日志生成速度基本匹配了，这个问题就没了
如果搭建了镜像， 那么logreader是读取mirror端的日志，而不是primary端的日志。所以如果大量的rebuild index，出现这种情况是正常的。
即使没有镜像，rebuild index也会造成log reader读取缓慢，因为log reader要过滤大量的non-replciate日志
力伟
架构图
镜像正常
镜像suspend
