Post reply

Log file corrrupted

  • August 9, 2011 at 9:46 pm

    #255138

    HI,

    Log file has corrupted in sql server 2005 database?

    I have only mdf file. How to create new log file in sql server 2005 database?

    Please any one help for me.

    Thanks

    Jerry

  • August 10, 2011 at 12:34 am

    #1366396

    You can ATTACH the .mdf file.

    .ldf file will be created automatically.

  • August 10, 2011 at 12:54 am

    #1366400

    If the database was shut down cleanly (probably not) then SQL will recreate the log on startup.

    If not, the database will come up in recovery_pending then you'll have to use DBCC CheckDB with repair_allow_data_loss to recreate the log.

    DO NOT detach that database. If the database was not shut down cleanly before the log damage (unlikely) you won't be able to reattach it.

    http://sqlinthewild.co.za/index.php/2009/06/09/deleting-the-transaction-log/

    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

  • August 10, 2011 at 1:43 am

    #1366411

    GilaMonster (8/10/2011)

    If the database was shut down cleanly (probably not) then SQL will recreate the log on startup.

    If not, the database will come up in recovery_pending then you'll have to use DBCC CheckDB with repair_allow_data_loss to recreate the log.

    DO NOT detach that database. If the database was not shut down cleanly before the log damage (unlikely) you won't be able to reattach it.

    http://sqlinthewild.co.za/index.php/2009/06/09/deleting-the-transaction-log/

    Gail, Generally your first approach is restore from backup and this time I see that you have recommended repair_all_data_loss. I read your link and have tagged it as a favorite. I really like the last part in the link where you hack the DB back online after attach fails.

    M&M

  • August 10, 2011 at 8:46 am

    #1366617

    Because it it's just the log damaged, repair_allow_data_loss will rebuild the log. There won't be any data lost, though there may be some transactional inconsistencies (see my blog post)

    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 5 posts - 1 through 4 (of 4 total)

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