Post reply

Log corruption. Why?

  • November 26, 2014 at 3:21 am

    #292742

    Hi,

    I was sent to a customers site to investigate why our application would throw a database connectivity error. Machine is using SQL 2008 R2 Express as it's a stand alone equipment and there is no access to the DB from the network.

    When i went to management studio the database was marked as SUSPECT and was not accessible. I Switched SQL Server service off made a copy of mdf and ldf to analyze later on, switched the service back on and went back to SSMS to try and fix the issue. The database is running in SIMPLE recovery model

    I ran

    ALTER DATABASE XX SET EMERGENCY

    then

    DBCC CHECKDB(XX) --this did not return any errors

    so i've done this

    ALTER DATABASE XX SET SINGLE_USER WITH ROLLBACK IMMEDIATE

    and this

    DBCC CHECKDB (XX, REPAIR_ALLOW_DATA_LOSS) --this rebuild the log

    and this

    ALTER DATABASE XX SET MULTI_USER

    that fixed the issue and brought the DB back ONLINE.

    I took the copy of mdf and ldf files made at the beginning, reproduce the same issue, done some investigating and both of the below scritps also fix the issue with DB.

    ALTER DATABASE XX REBUILD LOG ON(name=XX_log, FILENAME=[path to ldf])

    CREATE DATABASE XX ON (FILENAME = [path to mdf]) FOR ATTACH_REBUILD_LOG

    The question here is, having the copy of both mdf and ldf can i find out what caused the log to fail?

  • November 26, 2014 at 3:27 am

    #1761025

    The error log is more likely to have useful information than the transaction log.

    Corruption is usually IO subsystem

    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

  • November 26, 2014 at 3:59 am

    #1761034

    Hi Gila,

    Thanks for quick reply... I should have made a copy of the error log, didn't think about that under pressure to fix the machine, now it's too late as i don't have access to it.

    Just for the future, Is there anything specyfic I should look for in the error log next time?

  • November 26, 2014 at 4:48 am

    #1761044

    Yes, the messages saying what's wrong with the file and why SQL is marking the database suspect.

    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