• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 828
  • Last Modified:

DBCC CHECKDB crashes with servere error

when I run
DBCC CHECKDB (dbname) WITH NO_INFOMSGS, ALL_ERRORMSGS , TABLERESULTS

i get
A severe error occurred on the current command.  The results, if any, should be discarded.

is there any other option to try.
0
25112
Asked:
25112
  • 13
  • 7
1 Solution
 
25112Author Commented:

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

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
0
 
25112Author Commented:
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
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
25112Author Commented:
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?
0
 
25112Author Commented:
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

0
 
Chris MangusDatabase AdministratorCommented:
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.
0
 
25112Author Commented:
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!?
0
 
Chris MangusDatabase AdministratorCommented:
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.
0
 
25112Author Commented:

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?
0
 
Chris MangusDatabase AdministratorCommented:
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?
0
 
25112Author Commented:
>> I thought you said Select * From tablename was working.

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

0
 
25112Author Commented:

>>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?
0
 
25112Author Commented:
>>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..
0
 
Chris MangusDatabase AdministratorCommented:
Yes, a new table is created and you get your data out of the old one, with SELECT INTO.
0
 
25112Author Commented:
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?
0
 
Chris MangusDatabase AdministratorCommented:
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.
0
 
25112Author Commented:
i see your points.. thanksfor your input on the other tools

with select * into, it won't fix the data., right?
0
 
Chris MangusDatabase AdministratorCommented:
If the data is coming up when you do Select * then yes, you are going to be able to recover the data into another table.  Basically, whatever you get when you do the Select * is what you are going to get in the new table.
0
 
25112Author Commented:
thanks cmangus
0
 
Chris MangusDatabase AdministratorCommented:
Hope it works out for you!
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

  • 13
  • 7
Tackle projects and never again get stuck behind a technical roadblock.
Join Now