创建AlwaysOn后只读副本无法得到查询结果？

Question

只读辅助副本（异步）执行查询语句无法呈现数据且一直在执行中状态，无其他异常。如何解决？

 

注:

  1.创建可用性组仪表盘显示同步正常

  2.主数据库读写正常

  3.主数据库创建新表同步正常

  4.辅助日志大小一致（140GB）

  5.只读辅助副本尝试插入数据正常提示“只读”

Answer 1

日志大小一致（140GB），楼主做一下日志备份吧，收缩一下事务日志，事务日志太大，会造成同步慢

5.只读辅助副本尝试插入数据正常提示“只读”
只读副本是只读的，不能写

---

Love SQL

Answer 2

感谢回答。

日志目前并未影响同步

5是测试只读状态正常。

Answer 3

Did you see blocking on read only replica?

Answer 4

没有堵塞。

Answer 5

Did you see blocking on read only replica?

没有堵塞。

SELECT TOP 0 * FROM TABLE1; 语句执行正常

SELECT TOP 1* FROM TABLE1; 长时间等待且不会出现超时现象。

Answer 6

Your sql version and sp level? I'll test it on my server.

Answer 7

By the way, did you connect to listener or to server name?

Answer 8

Thank you very much for answering

Not listening server

SQL SERVER 2012 

windows server 2008 Enterprise    SP1

Answer 9

select top 1 长时间，这个时候你看下 sysprocesses 中的 status 是什么状态

Answer 10

select top 1 长时间，这个时候你看下 sysprocesses 中的 status 是什么状态

   suspended

Answer 11

What's wait type of it?

Answer 12

What's wait type of it?

lastwaittype = HADR_DATABASE_WAIT_FOR_TRANSITIO

waittype = 0x0351

Answer 13

Per books online:

A query on object(s) in a readable secondary database of an AlwaysOn availability group is blocked on row versioning while waiting for commit or rollback of all transactions that were in-flight when the secondary replica was enabled for read workloads. This wait type guarantees that row versions are available before execution of a query under snapshot isolation. 

Answer 14

Active Secondaries: Readable Secondary Replicas (AlwaysOn Availability Groups)

As soon as a readable secondary replica joins the availability group, the secondary replica can start accepting connections to its secondary databases. However, if any active transactions exist on a primary database, row versions will not be fully available immediately on the corresponding secondary database. Any active transactions that existed on the primary replica when the secondary replica was configured must be committed or rolled back. Until this process completes, the transaction isolation level mapping on the secondary database is incomplete and queries are temporarily blocked.

Answer 15

Thank you very much, I also found。

Answer 16

这个状态不是正常的。这个操作耗时很短。除非你的主数据库那边一直锁着。

---

想不想时已是想，不如不想都不想。

Answer 17

这个状态不是正常的。这个操作耗时很短。除非你的主数据库那边一直锁着。

---

想不想时已是想，不如不想都不想。

• 由于读操作会映射到快照隔离事务级别，因此，一个或多个辅助副本上的事务会阻止在主副本上清除虚影记录。 当辅助副本不再需要虚影记录时，虚影记录清除任务将自动清除主副本上的虚影记录。 这类似于您在主副本上运行事务时执行的操作。 在辅助数据库上的极端情况下，需要终止正在阻塞虚影清除的长时间运行的读查询。 请注意，如果辅助副本断开连接或数据移动在辅助数据库上挂起，可能阻塞虚影清除。 此状态还会阻止日志截断，因此，如果此状态持续，则我们建议您从可用性组中删除此辅助数据库。

谢谢。

准备删除重做，看是否任然存在该情况。

Answer 18

Redo still exists.

Answer 19

Which sync mode do you use?

Answer 20

Just tested on my sql2014 alwayson servers, unable to reproduce this issue on read only replicas whether in sync or async mode.

Answer 21

Index induced fault

Answer 22

What does that mean?

Answer 23

参考：https://blog.csdn.net/zzzz214646812/article/details/79931063

主节点上进程异常导致。