nihao_lf
好运常伴身旁
采纳率33.3%
2019-04-25 16:50

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

  • sql
已采纳

环境:
操作系统: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条回答

  • nihao_lf 好运常伴身旁 2年前

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

    点赞 评论 复制链接分享

相关推荐