SQL Server detected a logical consistency-based I/O error

  • First, when I run this sql : select * from mydatabase.dbo.mytable, I got this

    Msg 824

    SQL Server detected a logical consistency-based I/O error: incorrect pageid (expected 1:2412; actual 0:0). It occurred during a read of page (1:2412) in database ID 5 at offset 0x000000012d8000 in file 'mydatabase.mdf'. Additional messages in the SQL Server error log or system event log may provide more detail. This is a severe error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.

    Second, when I run DBCC CHECKDB ('mydatabase') WITH NO_INFOMSGS, ALL_ERRORMSGS, I got same message

    Msg 824

    SQL Server detected a logical consistency-based I/O error: incorrect pageid (expected 1:2412; actual 0:0). It occurred during a read of page (1:2412) in database ID 13 at offset 0x000000012d8000 in file 'mydatabase.mdf:MSSQL_DBCC13'. Additional messages in the SQL Server error log or system event log may provide more detail. This is a severe error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.

    Third, I right click on database, Properties, and

    TITLE: Microsoft SQL Server Management Studio

    ------------------------------

    Cannot show requested dialog.

    ------------------------------

    ADDITIONAL INFORMATION:

    Cannot show requested dialog. (SqlMgmt)

    ------------------------------

    Property Size is not available for Database 'mydatabase'. This property may not exist for this object, or may not be retrievable due to insufficient access rights. (Microsoft.SqlServer.Smo)

    For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1600.1+((KJ_RTM).100402-1540+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.PropertyCannotBeRetrievedExceptionText&EvtID=Size&LinkId=20476

    ------------------------------

    BUTTONS:

    OK

    ------------------------------

    Can you suggest me to resolve this please…. I am using sql server 2008 r2

    Can you please let me know if you need any other details….

    Thank you in Advance…..

  • Restore from a clean backup (backup taken before the corruption occurred)

    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
  • GilaMonster (2/26/2014)


    Restore from a clean backup (backup taken before the corruption occurred)

    I don't have clean backup. For some reason, my customer stopped weekly backup and the last backup is several month ago

  • I hope that database isn't very important... It's very likely there will be some data loss here, it's possible that said data loss could be the entire DB.

    Find some downtime, take the database into single user mode (make sure you get the single connection) and run the following (just the following, exactly as it is with no extra options, settings or switches)

    DBCC CheckDB(<Database Name>) WITH NO_INFOMSGS, ALL_ERRORMSGS, TABLOCK

    Let's see if that will give anything useful in terms of the actual errors.

    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
  • VIII (2/26/2014)


    GilaMonster (2/26/2014)


    Restore from a clean backup (backup taken before the corruption occurred)

    I don't have clean backup. For some reason, my customer stopped weekly backup and the last backup is several month ago

    One question: why is your customer taking (or not taking) backups? Isn't that your job as a DBA?

  • I don't have clean backup. For some reason, my customer stopped weekly backup and the last backup is several month ago

    Since you don't have backup , try to repair the database,

    TRY

    ALTER DATABASE corrupted_db SET SINGLE_USER WITH ROLLBACK IMMEDIATE

    DBCC ChECKDB ('corrupted_db', REPAIR_ALLOW_DATA_LOSS)

    ALTER DATABASE corrupted_db SET MULTI_USER

    CAUTION : There is a high chance of data loss

    If you mange to get database online, make sure you implement database backup strategy

    SQL 2000/2005/2008/2012 DBA - MCTS/MCITP

  • kenneth.mofokeng (2/26/2014)


    I don't have clean backup. For some reason, my customer stopped weekly backup and the last backup is several month ago

    Since you don't have backup , try to repair the database,

    TRY

    ALTER DATABASE corrupted_db SET SINGLE_USER WITH ROLLBACK IMMEDIATE

    DBCC ChECKDB ('corrupted_db', REPAIR_ALLOW_DATA_LOSS)

    ALTER DATABASE corrupted_db SET MULTI_USER

    NO! NO! NO!

    Recommending repairing with no idea of what's actually wrong is insane! We don't even know what the errors are, a sensible recommendation cannot be made on no data. When dealing with corrupt databases the extent of the corruption is essential to know before any useful recommendations can be made. To be honest, since CheckDB's throwing errors I suspect repair will not be a option, it'll likely fail as well.

    DO NOT attempt a repair at this point.

    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
  • kenneth.mofokeng (2/26/2014)


    I don't have clean backup. For some reason, my customer stopped weekly backup and the last backup is several month ago

    Since you don't have backup , try to repair the database,

    TRY

    ALTER DATABASE corrupted_db SET SINGLE_USER WITH ROLLBACK IMMEDIATE

    DBCC ChECKDB ('corrupted_db', REPAIR_ALLOW_DATA_LOSS)

    ALTER DATABASE corrupted_db SET MULTI_USER

    CAUTION : There is a high chance of data loss

    If you mange to get database online, make sure you implement database backup strategy

    Odd. I'd always heard that what's important is to have a recovery strategy.


    My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

    My thought question: Have you ever been told that your query runs too fast?

    My advice:
    INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
    The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

    Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
    Since random numbers are too important to be left to chance, let's generate some![/url]
    Learn to understand recursive CTEs by example.[/url]
    [url url=http://www.sqlservercentral.com/articles/St

  • dwain.c (2/26/2014)


    kenneth.mofokeng (2/26/2014)


    I don't have clean backup. For some reason, my customer stopped weekly backup and the last backup is several month ago

    Since you don't have backup , try to repair the database,

    TRY

    ALTER DATABASE corrupted_db SET SINGLE_USER WITH ROLLBACK IMMEDIATE

    DBCC ChECKDB ('corrupted_db', REPAIR_ALLOW_DATA_LOSS)

    ALTER DATABASE corrupted_db SET MULTI_USER

    CAUTION : There is a high chance of data loss

    If you mange to get database online, make sure you implement database backup strategy

    Odd. I'd always heard that what's important is to have a FREQUENTLY TESTED recovery strategy.

    FTFY 😀

    I'm a DBA.
    I'm not paid to solve problems. I'm paid to prevent them.

  • andrew gothard (2/26/2014)


    dwain.c (2/26/2014)


    kenneth.mofokeng (2/26/2014)


    I don't have clean backup. For some reason, my customer stopped weekly backup and the last backup is several month ago

    Since you don't have backup , try to repair the database,

    TRY

    ALTER DATABASE corrupted_db SET SINGLE_USER WITH ROLLBACK IMMEDIATE

    DBCC ChECKDB ('corrupted_db', REPAIR_ALLOW_DATA_LOSS)

    ALTER DATABASE corrupted_db SET MULTI_USER

    CAUTION : There is a high chance of data loss

    If you mange to get database online, make sure you implement database backup strategy

    Odd. I'd always heard that what's important is to have a FREQUENTLY TESTED recovery strategy.

    FTFY 😀

    I stand corrected sir!


    My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

    My thought question: Have you ever been told that your query runs too fast?

    My advice:
    INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
    The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

    Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
    Since random numbers are too important to be left to chance, let's generate some![/url]
    Learn to understand recursive CTEs by example.[/url]
    [url url=http://www.sqlservercentral.com/articles/St

  • GilaMonster (2/26/2014)


    I hope that database isn't very important... It's very likely there will be some data loss here, it's possible that said data loss could be the entire DB.

    Find some downtime, take the database into single user mode (make sure you get the single connection) and run the following (just the following, exactly as it is with no extra options, settings or switches)

    DBCC CheckDB(<Database Name>) WITH NO_INFOMSGS, ALL_ERRORMSGS, TABLOCK

    Let's see if that will give anything useful in terms of the actual errors.

    Nothing useful, same message

    SQL Server detected a logical consistency-based I/O error: incorrect pageid (expected 1:2412; actual 0:0). It occurred during a read of page (1:2412) in database ID 5 at offset 0x000000012d8000 in file 'mydatabase.mdf'. Additional messages in the SQL Server error log or system event log may provide more detail. This is a severe error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.

  • No good news then.

    The DB is damaged in such a way that CheckDB can't run, that makes the corruption irreparable. At this point I'd normally say 'restore from backup', but...

    The DB can't be fixed, you're going to have to recreate it.

    Script all objects from the damaged database. Don't forget permissions, constraints, indexes, etc. Some objects may well fail to script. Get what you can.

    Export all data out. BCP out is probably a good option. Some tables will almost certainly fail. You may have to export tables in chunks, range filters on the clustered index key to avoid reading damaged pages. Get what data you can.

    Assuming you can get anything at all out, use the scripts and exported data to create a new DB. This will not be quick, likely days of work.

    Once you've got the new DB up, get a backup strategy in place and point out to the customer (politely), that all of this work could have been avoided if they'd had backups.

    Additionally... Corruption doesn't just happen for no reason. There's likely something wrong somewhere in the IO subsystem. Do your investigations, check logs, update drivers, update firmware, etc, etc. Otherwise this could all happen again in a few months.

    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
  • GilaMonster (2/27/2014)


    No good news then.

    The DB is damaged in such a way that CheckDB can't run, that makes the corruption irreparable. At this point I'd normally say 'restore from backup', but...

    The DB can't be fixed, you're going to have to recreate it.

    Script all objects from the damaged database. Don't forget permissions, constraints, indexes, etc. Some objects may well fail to script. Get what you can.

    Export all data out. BCP out is probably a good option. Some tables will almost certainly fail. You may have to export tables in chunks, range filters on the clustered index key to avoid reading damaged pages. Get what data you can.

    Assuming you can get anything at all out, use the scripts and exported data to create a new DB. This will not be quick, likely days of work.

    Once you've got the new DB up, get a backup strategy in place and point out to the customer (politely), that all of this work could have been avoided if they'd had backups.

    Additionally... Corruption doesn't just happen for no reason. There's likely something wrong somewhere in the IO subsystem. Do your investigations, check logs, update drivers, update firmware, etc, etc. Otherwise this could all happen again in a few months.

    thanks, but I think the problem is worse than that

    when I try to generate script, nothing to get

    when I try to export data, same message 'SQL Server detected a logical consistency-based I/O error' again

    when I expand folder table and store procedure, all gone

    I must find another way to export data, please let me known if you have any idea

    Is this solution worth to try ?

    kenneth.mofokeng (2/26/2014)


    Since you don't have backup , try to repair the database,

    TRY

    ALTER DATABASE corrupted_db SET SINGLE_USER WITH ROLLBACK IMMEDIATE

    DBCC ChECKDB ('corrupted_db', REPAIR_ALLOW_DATA_LOSS)

    ALTER DATABASE corrupted_db SET MULTI_USER

    CAUTION : There is a high chance of data loss

    If you mange to get database online, make sure you implement database backup strategy

  • VIII (2/27/2014)


    thanks, but I think the problem is worse than that

    when I try to generate script, nothing to get

    when I try to export data, same message 'SQL Server detected a logical consistency-based I/O error' again

    when I expand folder table and store procedure, all gone

    Then the DB is a complete loss. That several month old backup may be your only option. Good luck.

    Is this solution worth to try ?

    kenneth.mofokeng (2/26/2014)


    Since you don't have backup , try to repair the database

    See my reply to kenneth above. Also, as I said to you

    The DB is damaged in such a way that CheckDB can't run, that makes the corruption irreparable.

    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
  • dwain.c (2/26/2014)


    andrew gothard (2/26/2014)


    dwain.c (2/26/2014)


    kenneth.mofokeng (2/26/2014)


    I don't have clean backup. For some reason, my customer stopped weekly backup and the last backup is several month ago

    Since you don't have backup , try to repair the database,

    TRY

    ALTER DATABASE corrupted_db SET SINGLE_USER WITH ROLLBACK IMMEDIATE

    DBCC ChECKDB ('corrupted_db', REPAIR_ALLOW_DATA_LOSS)

    ALTER DATABASE corrupted_db SET MULTI_USER

    CAUTION : There is a high chance of data loss

    If you mange to get database online, make sure you implement database backup strategy

    Odd. I'd always heard that what's important is to have a FREQUENTLY TESTED recovery strategy.

    FTFY 😀

    I stand corrected sir!

    I shall now correct myself and add "Documented". That's a biggie too. The best strategy in the world is no use if no-one knows what it is.

    I'm a DBA.
    I'm not paid to solve problems. I'm paid to prevent them.

Viewing 15 posts - 1 through 15 (of 18 total)

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