Getting below error

  • An error occurred during recovery, preventing the database 'XXXXX' (28:0) from restarting. Diagnose the recovery errors and fix them, or restore from a known good backup. If errors are not corrected or expected, contact Technical Support. (.Net SqlClient Data Provider) (Microsoft SQL Server, Error: 3414)

    Version : Microsoft SQL Server 2012 (SP4-GDR) (KB4583465) - 11.0.7507.2 (X64)

    • Check the sql server ERRORLOG file
    • enable trace flag 3004 to show the internal backup/restore operation and more.

      This is an undocumented TF, so remove as soon as possible !

      -- Enable Trace Flags
      DBCC TRACEON(3004,3605,-1);
      GO

      -- Perform the restore operation
      RESTORE DATABASE YourDb FROM DISK = 'YourdbBackupFile.bak' ... ;
      GO

      -- Disable Trace Flags
      DBCC TRACEOFF(3004,3605,-1);
      GO

      -- Check the error log for detailed information
      EXEC sp_readerrorlog;
      GO

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • Below is the output I am getting

    2025-01-29 14:16:45.26 spid70 DBCC TRACEON 3004, server process ID (SPID) 70. This is an informational message only; no user action is required.

    2025-01-29 14:16:45.26 spid70 DBCC TRACEON 3605, server process ID (SPID) 70. This is an informational message only; no user action is required.

    2025-01-29 14:16:45.26 spid70 RestoreLog: Database XXXXX

    2025-01-29 14:16:45.27 spid70 X-locking database: XXXXX

    2025-01-29 14:16:45.27 spid70 Opening backup set

    2025-01-29 14:16:45.28 spid70 SetTargetRestoreAge: 0

    2025-01-29 14:16:45.28 spid70 Restore: Configuration section loaded

    2025-01-29 14:16:45.28 spid70 Restore: Backup set is open

    2025-01-29 14:16:45.28 spid70 Restore: Planning begins

    2025-01-29 14:16:45.28 spid70 Dismounting FullText catalogs

    2025-01-29 14:16:45.28 spid70 Restore: Planning complete

    2025-01-29 14:16:45.29 spid70 Restore: BeginRestore (offline) on XXXXX

    2025-01-29 14:16:45.45 spid70 Restore: PreparingContainers

    2025-01-29 14:16:45.45 spid70 Restore: Containers are ready

    2025-01-29 14:16:45.47 spid70 Restore: Restoring backup set

    2025-01-29 14:16:45.47 spid70 Restore: Transferring data to XXXXX

    2025-01-29 14:16:45.47 spid70 Restore: FileHandleCache: Initial CacheSize: 48

    2025-01-29 14:16:45.47 spid70 Restore: Waiting for log zero on XXXXX

    2025-01-29 14:16:45.47 spid70 Restore: LogZero complete

    2025-01-29 14:16:45.48 spid70 SetTargetRestoreAge: 0

    2025-01-29 14:16:45.50 spid70 Restore: FileHandleCache: Statistics - Total requests: 0, Files opened: 0, Initial cache entries: 48, Total cache entries: 48.

    2025-01-29 14:16:45.50 spid70 Restore: FileHandleCache: Statistics - Cache hits: 0 (Hit ratio = -1.#J%), Entry recycles: 0.

    2025-01-29 14:16:45.50 spid70 Restore: Data transfer complete on XXXXX

    2025-01-29 14:16:45.51 spid70 Restore: Backup set restored

    2025-01-29 14:16:45.53 spid70 Restore-Redo begins on database XXXXX

    2025-01-29 14:16:46.30 spid70 RunOfflineRedo logIter.Init(): FirstLsn(PruId: 0): 0x295c:0xb9:0x1

    2025-01-29 14:16:46.30 spid70 RunOfflineRedo logIter.Init(): LastLsn(PruId: 0): 0x295c:0xbc:0x1

    2025-01-29 14:16:46.30 spid70 OfflineRollforward: StopLsn/LastLsn(PruId: 0): 0x295c:0xbc:0x1

    2025-01-29 14:16:46.48 spid70 Rollforward complete on database XXXXX

    2025-01-29 14:16:46.52 spid70 Restore: Done with fixups

    2025-01-29 14:16:46.68 spid70 Transitioning to STANDBY

    2025-01-29 14:16:47.01 spid70 Starting up database 'XXXXX'.

    2025-01-29 14:16:48.94 spid70 Error: 9004, Severity: 16, State: 6.

    2025-01-29 14:16:48.94 spid70 An error occurred while processing the log for database 'XXXXX'. If possible, restore from backup. If a backup is not available, it might be necessary to rebuild the log.

    2025-01-29 14:16:48.94 spid70 Error: 3414, Severity: 21, State: 1.

    2025-01-29 14:16:48.94 spid70 An error occurred during recovery, preventing the database 'XXXXX' (28:0) from restarting. Diagnose the recovery errors and fix them, or restore from a known good backup. If errors are not corrected or expected, contact Technical Support.

  • 2025-01-29 14_38_12-Microsoft Copilot_ Your AI companion — Mozilla Firefox

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • The question is:

    • Is it still possible to produce the backup from the original database ?

      • if yes:  please do and try to restore again ( use the trace flags to avoid having to run it a second time )
      • else: do you have an older full backup and the required log backup files to try an alternate restore path?

    • Ask you win-admin to check the disk for errors ( in the meanwhile you can fetch the new backup file )
    • I would drop the target database before restoring it. Just to be sure it doesn't reuse the actual allocated files.
    • if you need it without having it 100% consistent, ( e.g. on a dev server ) you may even try to detatch the db, remove the log file and attach the mdf/ndf file(s) without log
      USE [master]
      GO
      CREATE DATABASE [RecoveredDB] ON
      ( FILENAME = N'path to data folder\RecoveredDB.mdf' )
      FOR ATTACH
      GO

      it will generate an error stating it could nog fine a valid log file , but also states it has rebuilt the log file.

      Then perform a dbcc checkdb for RevoveredDB

    • ref: https://learn.microsoft.com/en-us/sql/relational-databases/errors-events/mssqlserver-9004-database-engine-error

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • Hi Johan,

    This issue has been resolved by enabling TraceFlag 3057 on secondary logshipping server and reconfiguring the logshipping for each databases,  now I can see all databases are in Standby mode. Thanks for your wonderful suggesions.

     

    • This reply was modified 1 week, 6 days ago by  sangameshms.
  • That is great news.

    Thank you for the feedback.

     

    Would it be possible to post a snippet of the log that hinted you towards TraceFlag 3057 ?

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • 2025-01-29 14:16:46.68 spid70 Transitioning to STANDBY

    Error : 9004

    Referred link

    https://support.microsoft.com/en-us/topic/kb2987585-restore-log-with-standby-mode-on-an-advanced-format-disk-may-cause-a-9004-error-in-sql-server-2008-r2-or-sql-server-2012-36d6b88a-9b42-68bc-30b0-5bcf3b5293fe

    • This reply was modified 1 week, 5 days ago by  sangameshms.
  • sangameshms wrote:

    Hi Johan,

    This issue has been resolved by enabling TraceFlag 3057 on secondary logshipping server and reconfiguring the logshipping for each databases,  now I can see all databases are in Standby mode. Thanks for your wonderful suggesions.

    TraceFlage 2057 apperars to be an undocumented TraceFlag according to the MS documentation.  Do you have a link to its description, etc?

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply