DBCC CHECKDB with Msg 8921, Msg 824

Question

DBCC CHECKDB with Msg 8921, Msg 824

jaganeee

Old Hand

Points: 373
More actions
October 31, 2017 at 6:40 am

#341003

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

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

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

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

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: 6869 More actions · Answer 2

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: 373 More actions · Answer 3

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: 6869 More actions · Answer 4

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: 373 More actions · Answer 5

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: 29544 More actions · Answer 6

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: 1004504 More actions · Answer 7

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: 373 More actions · Answer 8

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: 29544 More actions · Answer 9

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: 373 More actions · Answer 10

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

jaganeee Old Hand Points: 373 More actions · Answer 11

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: 373 More actions · Answer 12

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

Still in vein.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 13

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: 373 More actions · Answer 14

jaganeee

Old Hand

Points: 373

October 31, 2017 at 10:20 pm

#1966110

Thanks