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.
-
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
- Is it still possible to produce the backup from the original database ?
-
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.
-
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
- 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 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply