IT46637: RESTORING THE SQL SERVER DATABASE FAILS WITH MESSAGE 3456: "COULD NOT REDO LOG RECORD"

APAR status

• Closed as Vendor Solution.

Error description

• When restoring a multi-striped VDI full backup on SQL Server
  2019 or later, it fails with error MSSQLSERVER_3456.
  
  
  Error 3456, "Could not redo log record",  indicates that SQL
  Server is attempting to apply a log record that expects the
  latest version of the data page but instead finds an older
  version.
  
  
  
  
  The Windows Event Viewer Application Log shows:
  
  
  Could not redo log record (nnnnnnn:nnnnnn:n), for transaction
  ID (0:0), on page (n:nnnn), allocation unit xxxxxxx, database
  'DB_name' (database ID  nn). Page: LSN = (nnnnnn:nnnnnn:nn),
  allocation unit = nnn, type = 11. Log: OpCode = 7, context 11,
  PrevPageLSN: (nnnnn:nnnnnn:n). Restore from a backup of the
  database, or repair the database.
  
  
  
  
  The SQL Server error log shows:
  
  
  yyyy-mm-dd hh:mm:ss spid66 Error: 3456, Severity: 16, State: 1.
  
  yyyy-mm-dd hh:mm:ss spid66 Could not redo log record
  (nnnnnnn:nnnnnn:n), for transaction ID (0:0), on page (n:nnnn),
  allocation unit  xxxxxxx, database 'DB_name' (database ID nn).
  Page: LSN = (nnnnnn:nnnnnn:nn), allocation unit = nnn, type =
  11. Log: OpCode = 7, context 11, PrevPageLSN: (nnnnn:nnnnnn:n).
  Restore from a backup of the database, or repair the database.
  
  
  
  
  The Data Protection for Microsoft SQL Server service trace
  shows:
  
  
  mm/dd/yyyy hh:mm:ss [002024] [14212] : sqlapi.cpp (8004):
  SQLAPI-CSqlApi::LogoffServer - Handle Passed In >>>>> enter
  hh:mm:ss
  mm/dd/yyyyhh:mm:ss [002024] [14212] : sqlapi.cpp (8009):
  SQLAPI-CSqlApi::LogoffServer - Handle Passed In Disconnecting
  from SQL Server
  mm/dd/yyyy hh:mm:ss [002024] [14212] : sqlapi.cpp (8018):
  SQLAPI-CSqlApi::LogoffServer - Handle Passed In <<<<< exit
  rc(0):RC_OK hh:mm:ss
  mm/dd/yyyy hh:mm:ss [002024] [14212] : pcm.cpp
  (1457): Enter CPcm::cancel()
  mm/dd/yyyy hh:mm:ss [002024] [14212] : pcm.cpp
  (1473): Exit CPcm::cancel(), rc = 0
  mm/dd/yyyy hh:mm:ss [002024] [14212] : restordb.cpp (3519): Exit
  CRestoreDb::consumer(), rc = 1914
  mm/dd/yyyy hh:mm:ss [002024] [14212] : pcm.cpp
  (1964): Exit runConsumer(), rc = 1914
  Processed 480652 pages for database 'DB_name', file
  'DB_name_log' on file 1. (HRESULT:0x80131501)
  A previous restore operation was interrupted and did not
  complete processing on file 'DB_name'. Either restore the backup
  set that was interrupted or restart the restore sequence.
  Problems were identified while planning for the RESTORE
  statement. Previous messages provide details.
  RESTORE DATABASE is terminating abnormally.
  Changed database context to 'master'. (HRESULT:0x80131501)
  
  
  IBM Storage Protect Versions Affected:
  All supported versions of IBM Storage Protect for Databases:
  Data Protection for Microsoft SQL Server
  

Local fix

• Disable MSSQL's "Delayed log pin" feature by applying the
  following:
  
  
  1. Enable MSSQL's trace flags 3471 and 3475 as a startup
  parameter.
  
  
  Note: Trace flag 3471 disables the feature delayed log pinning
  for a full backup. Trace flag 3475 disables the feature delayed
  log pinning for a differential backup.
  
  
  
  
  2. Restart SQL Server.
  
  
  
  
  This local fix suggested by Microsoft should allow the
  multi-stripes restores to be successful.
  
  
  
  Note: "Disable log pinning" is a performance improvement
  feature introduced in Microsoft SQL Server. For more details on
  this issue and Microsoft's solution, please refer to: https://l
  earn.microsoft.com/en-us/troubleshoot/sql/database-engine/backu
  p-restore/backup-multistriped-vdi-issue
  

Problem summary

Problem conclusion

Temporary fix

Comments

• Customer is unable to restore database backups which backed up
  using multi-stripes while delayed log pinning was enabled. The
  problem is not known to affect versions of prior to MicroSoft
  SQL Server version 2019.
  
  There's no direct resolution documented in MSDN page for this
  scenario, the only preventive measure is to disable the delayed
  log pinning feature before taking backups using trace flags
  https://learn.microsoft.com/en-us/troubleshoot/sql/database-engi
  ne/backup-restore/backup-multistriped-vdi-issue
  
  
  CAUSE:
  ----------------------------------------------------------------
  -------
  Customer is unable to restore a SQL Server 2019 databases backup
  that was created using multi-backup stripes. The restore
  operation fails with message
  MSSQLServer_3456 or hangs indefinitely.
  
  Problems were identified while planning for the RESTORE
  statement. Previous messages provide details.
  
  RESTORE DATABASE is terminating abnormally.
  
  Error message:
  
  The window event viewer application log will show an Error 3456
  as follows:
  
  Could not redo log record (7507:3040076:3), for transaction ID
  (1:-1436091579), on page (6:9697736), allocation unit
  72057596474818560, database 'my_database' (database ID 5). Page:
  LSN = (7475:35169076:17), allocation unit = 72057596474818560,
  type = 1. Log: OpCode = 2, context 1, PrevPageLSN:
  (7507:3040017:2). Restore from a backup of the database, or
  repair the database.
  
  
  WORKAROUND SUGGESTED BY MICROSOFT:
  ----------------------------------------------------------------
  -------
    Solution1:
    -------------
      - Before taking the backup, enabled the following trace
  flags to temporarily disable delayed log pinning using below
  command.,
      - DBCC TRACEON(3471,3472,3475,-1)
      - This command can be used to mitigate the issue if you
  can't restart your SQL Server instance immediately.
  
  
    Solution 2:
    --------------
      - Depending on your configuration, you need to enable one or
  more trace flags as startup parameters for your SQL Server
  instance:
      - If you're taking full backups when the instance is acting
  as a primary replica or an instance without availability groups,
  enable trace flag 3471 to disable the delayed log pinning
  feature for full backups.
      - If you're taking differential backups when the instance is
  acting as a primary replica or an instance without availability
  groups, enable trace flag 3475 to disable the delayed log
  pinning feature for differential backups.
      - If you're taking full backups with copy only when the
  instance is acting as a secondary replica, enable trace flag
  3472 to disable the delayed log pinning feature for differential
  backups.
      - Restart SQL Server.
      - Take your full or differential backups again.
  
  
  UPDATE FROM MICROSOFT SUPPORT:
  ----------------------------------------------------------------
  -------
  Microsoft SQL Server version 2019 has reached its end of life
  (EOL) as of February 29th, 2025. Therefore, no further fixes
  will be provided for MS SQL Server version 2019. Recommendation
  is to upgrade to SQL Server version 2022, which will receive
  ongoing updates and fixes.
  

APAR Information

• APAR is sysrouted FROM one or more of the following:

• APAR is sysrouted TO one or more of the following:

Fix information

Applicable component levels