Database Inconsistency Error (Related to previous question)

Question

question

Mumin-FSL-BD asked Apr 6, '10

Database Inconsistency Error (Related to previous question)

I run DBCC command. all the objects are OK except one table. error message is below:

DBCC results for 'gl_yearly_rsor'.
Msg 8928, Level 16, State 1, Line 3
Object ID 1760725325, index ID 0, partition ID 72057594039304192, alloc unit ID 72057594043891712 (type In-row data): Page (1:47049) could not be processed.  See other errors for details.

Msg 8944, Level 16, State 16, Line 3
Table error: Object ID 1760725325, index ID 0, partition ID 72057594039304192, alloc unit ID 72057594043891712 (type In-row data), page (1:47049), row 11. Test (VarColOffsets + (int)(sizeof (class VarColOffset) * nVarCols) <= (nextRec - pRec)) failed. Values are 29833 and 212.

Msg 8944, Level 16, State 16, Line 3
Table error: Object ID 1760725325, index ID 0, partition ID 72057594039304192, alloc unit ID 72057594043891712 (type In-row data), page (1:47049), row 11. Test (VarColOffsets + (int)(sizeof (class VarColOffset) * nVarCols) <= (nextRec - pRec)) failed. Values are 29833 and 212.

There are 8418 rows in 238 pages for object "gl_yearly_rsor".
CHECKDB found 0 allocation errors and 3 consistency errors in table 'gl_yearly_rsor' (object ID 1760725325).

my question is, why its happened and how could i retrive my corrupted data?

thanks in advance

sql-server-2005 dbcc

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 1 · 2010-04-06T11:19:31Z

Grant Fritchey answered Apr 6, '10

Before you try running the repari with the allow data loss option, which will consign your data to the ether, there are a few steps you should try first. Again, go and read up some of the details on Paul Randal's blog.

First thing, back up the database and put it aside. You may need it, corruption and all.

Since you don't have a restore to go to, you're going to need to try to run repair. But instead of going straight to dropping the data, try running the DBCC with Repair_Rebuild. I don't think it will help because if I'm reading the DBCC results correctly, you have a heap, a table without a clustered index, that's corrupt. That likely means data loss.

If that doesn't work, then you can try using the repair_allow_data_loss.

The perfect answer to this issue is to go to the backup and get non-corrupted structures and data, but you need to have a good set of full backup and log backups to make that happen and know when the corruption occurred.

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 2 · 2010-04-06T10:45:27Z

sp_lock answered Apr 6, '10

If you have no "good" backup (plus tranaction logs) to go back to then you may wish to consider the "repair_allow_data_loss" option with the CHECKDB command. BEWARE it does what it says on the tin and page(s) that are corrupt will be removed/de-allocated.

If this is possible and it fixes this issue(s), I would then start looking for potential h/w issues. Check the EventLogs and/or SQL Logs to see if any errors are reported previous to the corruption occuring. Most corruption comes from the disks and memory, so if you can run a diag on them (or SAN) it may report error at the IO level.

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 3 · 2010-04-08T08:29:34Z

Mumin-FSL-BD answered Apr 8, '10

for on smooth works, temporarily i solve my problem restoring last worked data and repairing index. it's now working smoothly without hassel at my client end. but i have to find out why this data corruption happed and i can check h/w issue as Lockwood said.

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

question