RECOVER DATABASE WITH CORRUPT LOG FILE

Discussion in 'microsoft.public.sqlserver.server' started by FH, Sep 14, 2005.

  1. FH

    FH Guest

    My database was marked 'SUSPECT' as a result of harddisk error and I don't
    have any backup since my backup harddisk was broken


    I tried using
    Sp_resetstatus mydata
    DBCC CHECKDB ('mydata', NO INDEX)
    GO

    It returned:
    Prior to updating sysdatabases entry for Database 'mydata', mode=0 and
    staturs = 1077936397
    (status suspect bit = 256)
    For row in sysdatabase for datase ' mydata', the status bit 256 was forced
    off and mode was force to 0
    warning : you must recover this database prior to access
    Server: Msg 926, Level 14, State 1, Line -1074284091
    Database 'mydata' cannot be opened. It has been marked SUSPECT by recovery.
    See the SQL Server Error log for more information.



    When I checked the error log, part of the log was:

    Opening file C:\mssql7\data\mydata_Data.MDF
    Opening file C:\mssql7\data\mydata_Log.LDF
    The log for database 'mydata' is corrupt
    Error 9004, severity 23, state:2
    Database 'mydata' (database ID 11) could not recover. Contact Technical
    Support
    Error 3414, Severity 21 state 1



    I tried using the following command
    ALTER DATABASE mydata
    ADD LOG FILE
    (NAME = test1log2,
    FILENAME= 'C:\mssql7\data\test2log.ldf',
    SIZE = 5MB,
    MASXIZE = 100MB,
    FILEFROWTH = 5MB)
    GO

    It returned
    Database 'mydata' cannot be opened


    When I tried detach/attach_single_file database, it returned:


    EXEC sp_detach_db @dbname = 'mydata'

    EXEC sp_attach_single_file_db @dbname = 'mydata',
    @physname = 'c:\mssql7\data\mydata_data.mdf'

    Successfully detached database 'mydata'.
    DBCC execution completed. If DBCC printed error messages, contact your
    system administrator.
    server: Msg 9004, Level 23, State 2, Line 1
    The log for database 'mydata' is corrupt.

    Connection Brokens

    Please anyone could help me recover my database

    Regards
    FH
     
    FH, Sep 14, 2005
    #1
    1. Advertisements

  2. FH

    Jens Guest

    Jens, Sep 14, 2005
    #2
    1. Advertisements

  3. Hi

    Try with sp_add_log_file_recover_suspect_db.

    Else you're with problems if don't have recently backup.

    Good luck.

    FJCG
     
    Francisco Corrales, Sep 14, 2005
    #3
  4. Not sure if you fixed this yet but I just had a db marked to this statu
    and it simply means that the Log file is knackered. Unfortunately
    without backups you will lose some uncommitted data, but at least th
    written data will be about. Do the following:

    1) Stop the SQL server
    2) Copy the database.MDF file to anywhere (just a safety check)
    3) Run SP_DETACH_DB 'DBNAME', 'TRUE'
    4) Now Use explorer and manually delete the LDF file (LOG)
    5) Now run sp_attach_single_file_db @dbname = 'dbname',
    @physname = 'fullpath\dbname.MDF'

    It will create a brand new LDF file in the Same folder as the MDF
    this can then be moved to your folder structure using SP_RENAMEDB.

    Word of advice though is to run a maintenance plan with a backup daily
    I guess the problem with the log file is that you never clear it dow
    as a result of a backup. It makes me sad to see you treating a databa
    in that way! :)

    Good luck matey.

    Mik


    -
    mikeroberts2
     
    mikeroberts23, Sep 26, 2005
    #4
    1. Advertisements

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.