locked
database corrupt RRS feed

  • Question

  • Hi all,

    what the reason database is goes in suspect mode..?

    what the reason database is corrupted?

    how can i resolve this issues?

    Saturday, June 29, 2013 7:01 AM

All replies

  • Database going in suspect mode possible causes

    1.Rogue data coming from application

    2. Faulty motherboard

    3. Faulty RAM

    4. OS corruption

    5. SAN corruption

    Best method to overcome is restore from previous Valid backup in this case possibility of data loss minimum

    Run DBCC CHECKDB to find out inconsistency and allocation error ...if coruption is showing for

    Index id =1 means clustered index corrupted data loss will be there even if REAPIR done

    Index id=0 Heap corrupted..

    Index id=2 Non clustered index corrupted u can run REPAIR_REBUILD might be that after rebuild no data loss will occur

    Story is different for System databases...repair is not possible for system databases u can rebuild it /restore it from prev backup

    http://msdn.microsoft.com/en-us/library/dd207003.aspx

    Hope this helps...more help can be taken from google..from reading articles related to corruption


    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers



    Saturday, June 29, 2013 7:21 AM
  • "1.Rouge data coming from application"

    Above should not cause a database go corrupt or suspect. Do you have any such examples? Curious...

    "Index id=2 Non clustered index corrupted u can run REPAIR_REBUILD might be that after rebuild no data loss will occur"

    In my experience, corrupt indexes still requires REPAIR_ALLOW_DATALOSS. At least the cases I had faulty NC indexes, REPAIR_REBUILD wasn't enough. Just as an FYI...


    Tibor Karaszi, SQL Server MVP | web | blog

    Saturday, June 29, 2013 9:54 AM
  • "1.Rouge data coming from application"

    Above should not cause a database go corrupt or suspect. Do you have any such examples? Curious...

    "Index id=2 Non clustered index corrupted u can run REPAIR_REBUILD might be that after rebuild no data loss will occur"

    In my experience, corrupt indexes still requires REPAIR_ALLOW_DATALOSS. At least the cases I had faulty NC indexes, REPAIR_REBUILD wasn't enough. Just as an FYI...


    Tibor Karaszi, SQL Server MVP | web | blog

    Hi Tibor,

    I cannot actually reproduce a scenario or give ex ...but for sql 2000 i face issue long before  where corrupt data came for app, as system cataloge update was allowed it made some changes and data was corrupt...

    For NC i think before using REPAIR_ALLOW_DATA_LOSS we can also consider dropping and rebuilding it...its possble to remove corruption.(ofcorse if we are able to)


    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers

    Saturday, June 29, 2013 10:56 AM
  • "1.Rouge data coming from application"

    Above should not cause a database go corrupt or suspect. Do you have any such examples? Curious...

    Bad data passed from application code should not cause a database to become suspect but it could cause column-level data corruption before SQL Server 2005.  Before SQL 2005, it was possible for a client application to pass invalid data as rpc parameters or via bcp that subsequently got stored.  Since the raw invalid binary value was physically stored, an invalid data or out-of-range error occurred when data are subsequently selected.  This is described in more detail in a kb article: http://support.microsoft.com/kb/923247.  I remember reading an kb article that had a repro from Query Analyzer (or maybe it was SSMS), but I couldn't find it.

    SQL 2005 introduced strict TDS protocol checks so that invalid data passed via RPC parameters was detected and rejected.  Also SQL 2005 introduced the DATA_PURITY option to detect this invalid data during DBCC.  I haven't personally run into this problem since SQL 2005 but have on SQL 2000 and earlier.

    This was particularly a problem for datetime, float and real types where not all 4 or 8 byte binary values are valid. 


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    Saturday, June 29, 2013 1:58 PM
  • Shanky,

    I cannot actually reproduce a scenario or give ex ...but for sql 2000 i face issue long before where corrupt data came for app, as system cataloge update was allowed it made some changes and data was corrupt...

    If the application account had the rights to change system catalogs then it was extremely over provisioned with privileges. That would be a huge security concern. To add to this, with SQL Server 2005 it is no longer possible to use the sp_configure option to allow system catalog updates, while the option is still there and can be turned on - it results in a NOOP.

    Edit:

    I also wouldn't choose to use DBCC CHECKDB on a bad non-clustered index. To run repair on a database requires it to be in single_user mode which would mean you'd have to kick out all other users - meaning the application users, again though with caveats. I would first (for a smaller index) drop it and re-create it assuming it wasn't enforcing a uniqueness constraint, FK, etc, which would require locking but should be much less impactful to the users... on the other hand if the NCI is GB in size and it'd take a while to build a new one, some level of downtime would probably be needed.

    Also, don't forget for clustered indexes, heaps, etc, repairing and allowing data loss could result in just that - data loss. Repair doesn't take into account foreign keys, constraints, etc, so repairing your database could still leave it in a bad position. I would first investigate what the corruption is and if it's addressable by any non-intrusive means, develop a plan of attack and test fixing it before doing it in production. Once the data is lost... it's lost. This is why I can't stress the importance of backups enough.

    -Sean


    Sean Gallardy | Blog | Twitter


    Sunday, June 30, 2013 3:28 AM
    Answerer
  • This is the best anyone can give you

    http://www.sqlskills.com/blogs/paul/category/corruption/


    Thanks, Praneeth "Please Mark this as "Answered" if you find it helpful." http://www.praneeth-way2sqldba.blogspot.com/

    Wednesday, July 3, 2013 5:44 PM
  • Here are possible reasons that mark database in suspect mode:

    • Improper shutdown of the database server
    • Corruption of the database files
    • Unavailable device files
    • Unavailable database files
    • Database resource used by operating system
    • SQL Server incorrectly asserts free data page space when a row is inserted 

    Read the complete story in following article:

    Repair & Restore SQL Server Database from Suspect Mode

     
    Monday, September 2, 2013 4:59 AM