Post reply

Backup detected log corruption in database

  • July 18, 2017 at 4:41 am

    #322651

    Hi.
    Database FULL backup failed through management plan as well as manual backup.
    Error
    Backup detected log corruption in database JMDBIOMET. Context is FirstSector. LogFile: 2 'E:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\JMDBIOMET_log.ldf' VLF SeqNo: x27baed VLFBase: x10cc30000 LogBlockOffset: x10ccd0000 SectorStatus: 2 LogBlock.StartLsn.SeqNo: x1ce42706 LogBlock.StartLsn.Blk: x1d55966a Size: xeca3 PrevSize: x37b3

    I tried the following steps.
    1. Database switch to SIMPLE recovery for braking LSN
    2. To change FULL recovery
    3. Start full backup but there is no hope.
    Please suggest.
    Thanks

  • July 18, 2017 at 4:51 am

    #1951335

    SQL Galaxy - Tuesday, July 18, 2017 4:41 AM

    3. Start full backup but there is no hope.

    What do you mean?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • July 18, 2017 at 4:59 am

    #1951336

    3. Database to changed FULL recovery mode from SIMPLE

    then try to take FULL backup again. but this full backup not successes again same error

  • July 18, 2017 at 5:47 am

    #1951343

    This is not enough information for a thorough advice but one idea would be to:
    1. Stop the SQL Server Service
    2. Copy the data file, either different name or different location
    3. Start the SQL Server Service
    4. Attache the data file using ATTACH_REBUILD_LOG 
    This way, you will at least have a working copy of the database.
    😎

    Do you do regular integrity checks?
    Is restoring from the last backup an option?
    Any errors in the logs?

  • July 18, 2017 at 6:03 am

    #1951351

    During database backup Error message record in error log file.

    Message
    Error: 3041, Severity: 16, State: 1.
    Backup detected log corruption in database JMDBIOMET. Context is FirstSector. LogFile: 2 'E:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\JMDBIOMET_log.ldf' VLF SeqNo: x27baed VLFBase: x10cc30000 LogBlockOffset: x10ccd0000 SectorStatus: 2 LogBlock.StartLsn.SeqNo: x1ce42706 LogBlock.StartLsn.Blk: x1d55966a Size: xeca3 PrevSize: x37b3

    I have FULL backup available at 16th July 2017 and TLOG backup file available till date at 2017_07_18_140002_2519508.trn
    Application side not finding any downtime also database up and online.

    DBCC CHECKDB command executing. I will post once it completed.
    Shall I execute “REPAIR_ALLOW_DATA_LOSS” command for resolving this error? It may some data loss and I will push data again which are deleted rows.
    Thanks

  • July 18, 2017 at 6:13 am

    #1951357

    CHECKDB command output no errors.

    CHECKDB found 0 allocation errors and 0 consistency errors in database 'JMDBIOMET'.
    DBCC execution completed. If DBCC printed error messages, contact your system administrator.

  • July 18, 2017 at 6:31 am

    #1951368

    SQL Galaxy - Tuesday, July 18, 2017 6:03 AM

    Shall I execute “REPAIR_ALLOW_DATA_LOSS†command for resolving this error? It may some data loss and I will push data again which are deleted rows.

    No, because CheckDB doesn't check or repair the log file, so running it in this case is a waste of time.
    See Eirikur's suggestion.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • July 18, 2017 at 6:45 am

    #1951377

    Ok..I will follow Mr. Eirikur's suggestions.
    I have doubt.. How TLOG backup successed till time if LDF file got corrupted. I think some of the log block was issues.  
    Thanks

  • July 18, 2017 at 9:45 am

    #1951440

    Thank you Mr. Eirikur's and Gail.. I resolved LDF file corruption problem and database working as expected. I followed Mr. Eirikur's steps and slightly changed..
    1. Stop SQL services
    2. Copy the MDF datafile another folder
    3. start SQL services
    4. Drop the database due to i need create same DB name for exists application connection and interface requirement 
    5. Attached same DB name as below script and LDF file created automatically without mention REBUILD options 
    USE [master]
     GO
    EXEC sp_attach_single_file_db @dbname='JMDBIOMET',
    @physname=N'E:\Biometric_Data\JMDBIOMET.mdf'
    GO

    output
    File activation failure. The physical file name "E:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\JMDBIOMET_log.ldf" may be incorrect.
    New log file 'E:\Biometric_Data\JMDBIOMET_log.LDF' was created

    Thanks

  • July 18, 2017 at 10:33 am

    #1951451

    Good.
    Now size the log file properly, set the recovery to full and take a full backup to start the log chain, then you should be back to normal.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • July 18, 2017 at 11:03 am

    #1951460

    The easy fix is to shrink the TLog to 1 MB then re-grow the TLog to the size that it was. This can be done with the DB live thus no downtime or outage is required. 

    You may need to backup the TLog then shrink several times to actually get it down to 1 MB.

Viewing 11 posts - 1 through 10 (of 10 total)

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