Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have SQL Server 2000 and ever since upgrading from SP2 to SP4 random errors began happening. Mostly overflow errors, sometimes in SqlDateTime and sometimes in data type numeric.

For example, right now I have a column with 1099511628.776 as its value, the column type is decimal(18,3) which can hold up to 10^38, so clearly this is not an overflow, however when I try to update that column and set it to 1 I get the error Arithmetic overflow error converting numeric to data type numeric.

I ran DBCC CHECKDB on the database and I got 6 consistency errors in two tables, for the first table:

Msg 8970, Level 16, State 1, Line 1  
Row error: Object ID 578101100, index ID 0, page ID (1:24157), row ID 61.
Column 'wh_id' was created NOT NULL, but is NULL in the row.

Second table:

Msg 8928, Level 16, State 1, Line 1   
Object ID 933578364, index ID 0: Page (1:7160) could not be processed. 
See other errors for details.

Msg 8939, Level 16, State 12, Line 1  
Table error: Object ID 933578364, index ID 0, page (1:7160). 
Test (*(((int*) &m_reservedB) + i) == 0) failed. Values are 0 and 65536.

Msg 8944, Level 16, State 2, Line 1  
Table error: Object ID 933578364, index ID 0, page (1:7223), row 12. 
Test (!(hdr->r_tagA & (VERSION_MASK | RECTAG_RESV_A | RECTAG_RESV_B))) failed. 
Values are 17 and 193.`

Msg 8928, Level 16, State 1, Line 1  
Object ID 933578364, index ID 0: Page (1:7254) could not be processed. 
See other errors for details.`

Msg 8939, Level 16, State 106, Line 1  
Table error: Object ID 933578364, index ID 0, page (1:7254). 
Test (m_freeCnt == freeCnt) failed. Values are 2442 and 2444.`

Now for the first error, I tried running the query:

SELECT *
FROM table1
WHERE wh_id IS NULL

However, it returns zero rows. For the rest of the errors, I don't know how to query the table to find the the bad row(s).

How can I fix these consistency errors, and are they the reason for the random errors?

share|improve this question
add comment

1 Answer

up vote 1 down vote accepted

Those errors are corrupt pages in the actual table themselves. You will need to restore the table. There's no way to repair the issue without loosing data as the corruption is in the base table.

share|improve this answer
    
How would I partially restore the tables? Also is there anyway to identify the corrupted rows so I can enter them again? –  Ammar Abdulaziz Jan 9 '12 at 16:17
    
Using the native tools you would need to restore the entire database, then simply query the restored table looking for rows which don't exist in the table that was corrupt. –  mrdenny Jan 10 '12 at 9:14
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.