DBCC CHECKDB crashes with servere error

i tried
DBCC CHECKTABLE ("tablename") WITH data_purity; but because it is SQL 2000, that won't work.

I ran just

DBCC CHECKTABLE ("tablename")  and it gives the following:

DBCC results
Msg 2533, Level 16, State 1, Line 2
Table error: Page (1:879) allocated to object ID 290100074, index ID 0 was not seen.  Page may be invalid or have incorrect object ID information in its header.
Msg 2534, Level 16, State 1, Line 2
Table error: Page (1:879) with object ID 290100074, index ID 2 in its header is allocated by another object.
Msg 8979, Level 16, State 1, Line 2
Table error: Object ID 290100074, index ID 2. Page (1:879) is missing references from parent (unknown) and previous (page (1:878)) nodes. Possible bad root entry in sysindexes.
Msg 2537, Level 16, State 1, Line 2
Table error: Object ID 290100074, index ID 2, page (1:879), row 486. Record check ((Record (i) == INDEX_RECORD || (Record (i) == GHOST_INDEX_RECORD && level == 0)) && Page->GetType == INDEX_PAGE) failed. Values are 0 and 6.
Msg 2537, Level 16, State 1, Line 2
Table error: Object ID 290100074, index ID 2, page (1:879), row 487. Record check ((Record (i) == INDEX_RECORD || (Record (i) == GHOST_INDEX_RECORD && level == 0)) && Page->GetType == INDEX_PAGE) failed. Values are 0 and 6.
Msg 2537, Level 16, State 1, Line 2
Table error: Object ID 290100074, index ID 2, page (1:879), row 488. Record check ((Record (i) == INDEX_RECORD || (Record (i) == GHOST_INDEX_RECORD && level == 0)) && Page->GetType == INDEX_PAGE) failed. Values are 0 and 6.
Msg 8981, Level 16, State 1, Line 2
Table error: Object ID 290100074, index ID 2. The next pointer of (1:879) refers to page (1:880). Neither (1:880) nor its parent were encountered. Possible bad chain linkage.
There are 83318 rows in 2466 pages for object .
CHECKTABLE found 0 allocation errors and 7 consistency errors in table  (object ID 290100074).
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKTABLE

for the checkdb, even though it gave severe error.. it still lists 9 errors

Error      Level      State      MessageText      RepairLevel      Status      DbId      Id      IndId      File      Page      Slot      RefFile      RefPage      RefSlot      Allocation
2533      16      1      Table error: Page (1:879) allocated to object ID 290100074, index ID 0 was not seen.  Page may be invalid or have incorrect object ID information in its header.      repair_allow_data_loss      0      9      290100074      0      1      879      0      1      224      0      1
2534      16      1      Table error: Page (1:879) with object ID 290100074, index ID 2 in its header is allocated by another object.      repair_allow_data_loss      0      9      290100074      2      1      879      0      1      107      0      1
8979      16      1      Table error: Object ID 290100074, index ID 2. Page (1:879) is missing references from parent (unknown) and previous (page (1:878)) nodes. Possible bad root entry in sysindexes.      repair_rebuild      0      9      290100074      2      1      879      0      1      878      0      1
2537      16      401      Table error: Object ID 290100074, index ID 2, page (1:879), row 486. Record check ((Record (i) == INDEX_RECORD || (Record (i) == GHOST_INDEX_RECORD && level == 0)) && Page->GetType == INDEX_PAGE) fail      repair_allow_data_loss      0      9      290100074      2      1      879      486      0      0      0      1
2537      16      401      Table error: Object ID 290100074, index ID 2, page (1:879), row 487. Record check ((Record (i) == INDEX_RECORD || (Record (i) == GHOST_INDEX_RECORD && level == 0)) && Page->GetType == INDEX_PAGE) fail      repair_allow_data_loss      0      9      290100074      2      1      879      487      0      0      0      1
2537      16      401      Table error: Object ID 290100074, index ID 2, page (1:879), row 488. Record check ((Record (i) == INDEX_RECORD || (Record (i) == GHOST_INDEX_RECORD && level == 0)) && Page->GetType == INDEX_PAGE) fail      repair_allow_data_loss      0      9      290100074      2      1      879      488      0      0      0      1
8981      16      1      Table error: Object ID 290100074, index ID 2. The next pointer of (1:879) refers to page (1:880). Neither (1:880) nor its parent were encountered. Possible bad chain linkage.      repair_rebuild      0      9      290100074      2      1      880      0      1      879      0      1
8990      10      1      CHECKDB found 0 allocation errors and 7 consistency errors in table  (object ID 290100074).      NULL      0      9      290100074      0      0      0      0      0      0      0      1
8989      10      1      CHECKDB found 0 allocation errors and 7 consistency errors in database.      NULL      0      9      290100074      0      0      0      0      0      0      0      1

could you give you analysis.. i see lot of repair_allow_data_loss messages..

will it tell which data will be lost before removing them?

other than the command in
http://www.sqlhacks.com/FAQs/DBCC-REPAIR_ALLOW_DATA_LOSS is there any other option for sql 2000?
    ALTER DATABASE sql911
    SET single_user WITH ROLLBACK IMMEDIATE;
    go
    DBCC checkdb ('sql911', repair_allow_data_loss);
    go

Your best bet is to restore from most recent backup.  I believe with the errors you've posted, repair_allow_data_loss is going to remove a lot of data.  

I don't believe you can tell what data is going to be lost.  Technically, it's already lost and SQL Server is just going to remove the pointers to things it can't find.

what could cause this.. ? what precaution can be take to avoid this..
1)
it seems it may be a date field..
when i comment it out in SSMS, select * from tablename works ok.. but when the data field is not commented out, then it errors out..
2)
select * from the whole table gives no error in query analuzer!?

First, I would run:

Select *
Into myNewTableName
From myOldTableName

...in order to preserve what you have.  Then, I would drop the old table, run DBCC CHECKDB again until it runs clean and then rename the new table to the old, original name.

Table corruption can be caused by a lot of things.

Select *
Into myNewTableName
From myOldTableName
fails.. because it is just like select * from myOldTableName, right and then it inserts?

>>...in order to preserve what you have.  Then, I would drop the old table, run DBCC CHECKDB again until it runs clean and then rename the new table to the old, original name.

how would it make a difference running it in a backup table against the original table?

You are correct, that is how SELECT INTO works.  I thought you said Select * From tablename was working.

The idea behind doing this is to avoid as much data loss as possible.  The structure of the old table is corrupt.

Just curious, I mentioned restoring to your last good backup.  Do you not have a recent backup?

>> I thought you said Select * From tablename was working.

yes, only from query analyzer.. not from SSMS..

>>The idea behind doing this is to avoid as much data loss as possible.  The structure of the old table is corrupt.

so when a new table is created, some 'fixing' happens?

>>Just curious, I mentioned restoring to your last good backup.  Do you not have a recent backup?
thanks for the idea.. but this is a transaction table.. so it seems like the corruption has happened after the last good backup we have..

Yes, a new table is created and you get your data out of the old one, with SELECT INTO.

OK.. so other than REPAIR_ALLOW_DATA_LOSS | REPAIR_FAST | REPAIR_REBUILD , i don't have any choice, do i?

if i go for any external tools, could they do any more than what the above native options can do for me in this case?

There are third party tools out there that may be better but I can't recommend any.  Most want to do object level restore, but you would have to have a current backup for them to work.

Personally, I take regular backups around the clock and can do point-in-time restore on all my databases so I've not had to use any tools to recover corruption.

Even if you were considering using a third party tool, I would still use the SELECT INTO query ahead of time.  It won't hurt and could save your data in case the tool you choose causes more data loss.

i see your points.. thanksfor your input on the other tools

with select * into, it won't fix the data., right?

thanks cmangus

Hope it works out for you!