DBCC CHECKDB with Msg 8921, Msg 824

  • jaganeee

    Old Hand

    Points: 313

    my database has 20 tables.
    out of which 8 tables is opening but the remaining 12 shows error
    no backup has been created recently. last full backup on jan 8, 2017.

    i do the 
    DBCC CheckDB  against the corrupted database.

    it shows the following error

    Msg 8921, Level 16, State 1, Line 1
    Check terminated. A failure was detected while collecting facts. Possibly tempdb out of space or a system table is inconsistent. Check previous errors.
    Msg 824, Level 24, State 2, Line 1
    SQL Server detected a logical consistency-based I/O error: torn page (expected signature: 0x0; actual signature: 0x5555300). It occurred during a read of page (1:58) in database ID 10 at offset 0x00000000074000 in file 'D:\App_Data\SJ_Extended.mdf:MSSQL_DBCC10'. 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.

    i also tried

    DBCC CheckDB ('SJ_Extended', REPAIR_ALLOW_DATA_LOSS)

    same error occurs

    guide me to recover the datas

  • John Mitchell-245523

    SSC Guru

    Points: 148258

    Last full backup nine months ago is not a good situation to be in.  It looks as if that may be the best you can do, though.  Restore that - maybe you also have some log backups or differential backups that can bring you slightly closer to today's date.  When you've done that, get your storage administrators to do some diagnostics on your disks, set up a proper database backup schedule and test that you can restore the backups.

    John

  • NorthernSoul

    SSCertifiable

    Points: 6775

    You should only use REPAIR_ALLOW_DATA_LOSS as a last resort.

    Do you mean you haven't taken full backups? Have you taken diff or log backups?

    Thanks

  • jaganeee

    Old Hand

    Points: 313

    i haven't backup any log, differential or full backup recently.
    full backup on Jan 8.
    is there any possibility or steps to get possible data from corrupted database.

  • NorthernSoul

    SSCertifiable

    Points: 6775

    jaganeee - Tuesday, October 31, 2017 7:06 AM

    i haven't backup any log, differential or full backup recently.
    full backup on Jan 8.
    is there any possibility or steps to get possible data from corrupted database.

    It looks like your only option is to script out the database objects and export the non corrupted data but you will lose data. As John said you need to perform regular backups and restores in future. 

    Thanks

  • jaganeee

    Old Hand

    Points: 313

    thanks`

    NorthernSoul - Tuesday, October 31, 2017 7:15 AM

    jaganeee - Tuesday, October 31, 2017 7:06 AM

    i haven't backup any log, differential or full backup recently.
    full backup on Jan 8.
    is there any possibility or steps to get possible data from corrupted database.

    It looks like your only option is to script out the database objects and export the non corrupted data but you will lose data. As John said you need to perform regular backups and restores in future. 

    Thanks

    learned a lot with this issue.
    i understand how important is backup.
    is it possible to share the steps and scripts to get the data from database objects

  • Paul Randal

    One Orange Chip

    Points: 29438

    The 824 is a read failure of a page in a critical system table. That's not repairable and not fixable manually either. You'll need to script out as much as you can, but unfortunately page 1:54 is in sys.syscolpars, which is the equivalent of syscolumns, so you might run into a few tables that you can't access. If you're lucky, and there are clustered indexes on everything, you should be ok as long as there isn't other corruption in tables like sys.sysidxstats.

    My guess is there's likely further corruption and you'll be lucky to escape without significant data loss.

    Paul Randal
    CEO, SQLskills.com: Check out SQLskills online training!
    Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
    SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
    Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005

  • Gail Shaw

    SSC Guru

    Points: 1004424

    jaganeee - Tuesday, October 31, 2017 7:06 AM

    i haven't backup any log, differential or full backup recently.
    full backup on Jan 8.
    is there any possibility or steps to get possible data from corrupted database.

    Not in a DB with those errors.

    Script all objects (via Management Studio is easiest), some will fail. Export the data, some will fail, recreate the DB from what's left, and then put a proper backup strategy in place.

    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
  • jaganeee

    Old Hand

    Points: 313

    Paul Randal - Tuesday, October 31, 2017 9:52 AM

    The 824 is a read failure of a page in a critical system table. That's not repairable and not fixable manually either. You'll need to script out as much as you can, but unfortunately page 1:54 is in sys.syscolpars, which is the equivalent of syscolumns, so you might run into a few tables that you can't access. If you're lucky, and there are clustered indexes on everything, you should be ok as long as there isn't other corruption in tables like sys.sysidxstats.

    My guess is there's likely further corruption and you'll be lucky to escape without significant data loss.

    Thanks for ur reply. 
    using the long ago backup i checked for clustered index. each table has clustered index.
    can u help more to get the possible data. 
    don't know how to to execute to get the result.

  • Paul Randal

    One Orange Chip

    Points: 29438

    https://www.google.com/search?q=script+in+sql+server+with+data

    Paul Randal
    CEO, SQLskills.com: Check out SQLskills online training!
    Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
    SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
    Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005

  • jaganeee

    Old Hand

    Points: 313

    Thanks. Let me try my level best to get the data.

  • jaganeee

    Old Hand

    Points: 313

    I can’t able to get the required table data.

    Is it possible to update the corrupted database page 1:58 from the last backup done on jan.

  • jaganeee

    Old Hand

    Points: 313

    I tried but can’t able to get the required table .

    Still in vein.

  • Gail Shaw

    SSC Guru

    Points: 1004424

    jaganeee - Tuesday, October 31, 2017 8:49 PM

    I can’t able to get the required table data.Is it possible to update the corrupted database page 1:58 from the last backup done on jan.

    No.

    You WILL lose data as a result of this. Extract what you can, what you can't extract is lost

    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
  • jaganeee

    Old Hand

    Points: 313

    Thanks

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

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