好运常伴身旁 2019-04-25 16:50 采纳率: 33.3%
浏览 666
已采纳

SQLSERVER Alwayson 辅助副本隔几天就报 error 3456 : Could not redo log record

环境:
操作系统:Windows Server 2016
数据库:SQLSERVER 2016 SP2 Enterprise Edition (64-bit),是从 SP1打补丁升上来的

问题描述:隔几天辅助副本就出一次该问题,没有规律没有固定时间,出错时也没有什么计划任务在执行,错误日志在 error3456 之前没有可参考的信息。不知道跟 VLF 文件是否有关系,有部分VLF文件永远处于活动状态。

求大佬指点。非常感谢~

VLF部分文件

错误日志:

  • 2019-04-20 00:00:49.89 spid46s This instance of SQL Server has been using a process ID of 3144 since 04/14/2019 22:23:30 (local) 04/14/2019 14:23:30 (UTC). This is an informational message only; no user action is required.
  • 2019-04-21 00:00:05.35 spid47s This instance of SQL Server has been using a process ID of 3144 since 04/14/2019 22:23:30 (local) 04/14/2019 14:23:30 (UTC). This is an informational message only; no user action is required.
  • 2019-04-22 00:00:20.56 spid72s This instance of SQL Server has been using a process ID of 3144 since 04/14/2019 22:23:30 (local) 04/14/2019 14:23:30 (UTC). This is an informational message only; no user action is required.
  • 2019-04-23 00:00:35.64 spid19s This instance of SQL Server has been using a process ID of 3144 since 04/14/2019 22:23:30 (local) 04/14/2019 14:23:30 (UTC). This is an informational message only; no user action is required.
  • 2019-04-23 17:46:10.99 spid59s Error: 3456, Severity: 21, State: 1.
  • 2019-04-23 17:46:10.99 spid59s Could not redo log record (410417:192254:28), for transaction ID (0:1838907632), on page (1:761914), allocation unit 72057594280869888, database 'DB' (database ID 8). Page: LSN = (410417:191949:3), allocation unit = 72057594280869888, type = 10. Log: OpCode = 7, context 10, PrevPageLSN: (410417:192254:26). Restore from a backup of the database, or repair the database.
  • 2019-04-23 17:46:10.99 spid59s Always On Availability Groups data movement for database 'DB' has been suspended for the following reason: "system" (Source ID 2; Source string: 'SUSPEND_FROM_REDO'). To resume data movement on the database, you will need to resume the database manually. For information about how to resume an availability database, see SQL Server Books Online.
  • 2019-04-23 17:46:10.99 spid59s Error: 3313, Severity: 21, State: 5.
  • 2019-04-23 17:46:10.99 spid59s During redoing of a logged operation in database 'DB', an error occurred at log record ID (410417:192254:28). Typically, the specific failure is previously logged as an error in the Windows Event Log service. Restore the database from a full backup, or repair the database.
  • 2019-04-23 17:46:16.01 spid15s Error: 3313, Severity: 21, State: 2.
  • 2019-04-23 17:46:16.01 spid15s During redoing of a logged operation in database 'DB', an error occurred at log record ID (410417:192287:47). Typically, the specific failure is previously logged as an error in the Windows Event Log service. Restore the database from a full backup, or repair the database.
  • 2019-04-23 18:00:00.38 Logon Error: 18456, Severity: 14, State: 40.
  • 写回答

1条回答 默认 最新

  • 好运常伴身旁 2019-08-28 19:57
    关注

    每次出现 3456 错误后,从日志上去查看对应的事务信息时,每次对应的对象都是两个物理表(Temp_Table1/Temp_Table2:这两个都用于将内存数据更新到DB时用到的中转表,会频繁的 Truncate—> Insert)。对应的操作都是在Truncate 表后的 Insert 操作时的首页分配(Allocate First Page)。
    猜测是频繁 Truncate—> Insert 物理表造成的。将两个中转表改为临时表后问题解决了。

    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论

报告相同问题?

悬赏问题

  • ¥20 sub地址DHCP问题
  • ¥15 delta降尺度计算的一些细节,有偿
  • ¥15 Arduino红外遥控代码有问题
  • ¥15 数值计算离散正交多项式
  • ¥30 数值计算均差系数编程
  • ¥15 redis-full-check比较 两个集群的数据出错
  • ¥15 Matlab编程问题
  • ¥15 训练的多模态特征融合模型准确度很低怎么办
  • ¥15 kylin启动报错log4j类冲突
  • ¥15 超声波模块测距控制点灯,灯的闪烁很不稳定,经过调试发现测的距离偏大