Post reply

Log file is gone

  • January 24, 2010 at 4:38 am

    #386170

    We lost one of the 2 log files in our SQL Server 2005 Enterprise DB SP3.

    The database disappeared from the list in SSMS.

    I went through the process of:

    1. Renaming the good mdb file and creating a new database with the same name.

    2. Powering down SQL Server, swapping the newly created file with the old mdf file

    3. Bringing up SQL Server

    4. Setting it to Emergency mode and putting it in single user mode

    I checked the db status -

    SELECT DATABASEPROPERTYEX ('MYDB', 'STATUS')

    and comes back "EMERGENCY".

    Now I've been trying to run DBCC CHECKDB (MYDB, REPAIR_ALLOW_DATA_LOSS)

    in order to rebuild the log file for the last 2 hours but keep getting:

    Msg 922, Level 14, State 1, Line 2

    Database 'MYDB' is being recovered. Waiting until recovery is finished.

    The mdf file is about 160GB. My question is - should I be more patient and let it recover or is something wrong and I should try this process again.

    Thank you.

  • January 24, 2010 at 7:53 am

    #1107911

    I would recommend just restoring from your backups. While this may complete (or may not) it may cause data loss, transactional inconsistencies, structural inconsistencies or other similar problems.

    Emergency mode repair is a last resort and should not be the solution of choice ever.

    Get out your backups, restore the latest full backup, any differential (if you're using them) then all your log backups. If your backup strategy is properly designed, you should lose little data.

    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

  • January 24, 2010 at 3:17 pm

    #1107960

    Thanks.

    We were able to rebuild the raid and recover the data.

  • January 24, 2010 at 11:23 pm

    #1108010

    Good to hear. Just to be sure that there's no lingering damage, please run this

    DBCC CHECKDB (<Database Name>) WITH NO_INFOMSGS, ALL_ERRORMSGS

    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

Viewing 4 posts - 1 through 3 (of 3 total)

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