I'm not sure how well I can explain this problem, and also please note that I'm rather inexperienced with SQL 2005 administration. Please ask for any additional information if it should be helpful. **I also am aware that restoring from backup's will be recommended as the easiest fix, but I would like to see if the DB can be fixed without restoring. Data has been added since the last successful pre-error backup. The end-users also are not noticing any corruption.
- DBCC CHECKDB fails on a small number of user databases in SQL. *Manually running DBCC CHECKDB can sometimes pass without error.
- My trouble is there are such a variety of errors, and sometimes no errors at all (ex. passes the check w/o error). Once I think I get a hold of something to fix 1 error, I find more errors elsewhere.
Troubleshooting and Additional Info:
- Running DBCC CHECKDB typically brings the TEMPDB error (error 1 below). However, I found that running the option WITH TABLOCK handles the TEMPDB error. But even with the WITH TABLOCK option, I cant seem to run REPAIR_ALLOW_DATA_LOSS as more errors occur (error 3 below).
- Stranger yet, I tried to COPY a database with the corruption, to make a test DB that I could hammer on. The online COPY failed, but afterwards, the DBCC CHECKDB returned 0 errors! But 10 minutes later, there were errors found again...
Again, I'm not a pro at SQL. If possible, please give me straight-forward steps to try and resolve this problem. Last note: after spending a few hours today, now all databases are passing the DHECKDB... But my hopes arent high. :-) Any thoughts?
- Error 1 from DBCC informational messages:
Msg 8967, Level 16, State 216, Line 1 An internal error occurred in DBCC that prevented further processing. Contact Customer Support Services. DBCC results for 'DB1'. 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 8944, Level 16, State 20, Line 1 Table error: Object ID 85575343, index ID 1, partition ID 287083242389504, alloc unit ID 287083242389504 (type In-row data), page (1:33397), row 6. Test (columnOffsets->IsComplex (varColumnNumber) && (ColumnId == COLID_HYDRA_TEXTPTR || ColumnId == COLID_LOB_AU_INROW_ROOT || ColumnId == COLID_LOB_AU_INROW_ROOT_YUKON || ColumnId == COLID_SLOB_AU_INROW_ROOT || ColumnId == COLID_FILESTREAM || ColumnId == COLID_BACKPTR)) failed. Values are 5 and 99.
CHECKDB found 0 allocation errors and 1 consistency errors in table 'EventLog' (object ID 85575343). CHECKDB found 0 allocation errors and 1 consistency errors in database 'DB1'.
- Error 2 from SQL Server Log:
Unknown,DBCC encountered a page with an LSN greater than the current end of log LSN (217050:0:1) for its internal database snapshot. Could not read page (104:7602208)<c/> database 'DB1' (database ID 43)<c/> LSN = (7274528:6619248:114)<c/> type = 0<c/> isInSparseFile = 1. Please re-run this DBCC command.
- Error 3 from DBCC using "repair_allow_data_loss and TABLOCK" information messages:
Msg 8909, Level 16, State 1, Line 1 Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID -8854472584222146560 (type Unknown), page ID (1:11217) contains an incorrect page ID in its page header. The PageId in the page header = (16452:1717986918). Repairing this error requires other errors to be corrected first.
Msg 8939, Level 16, State 98, Line 1 Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID -7378710555617329152 (type Unknown), page (16460:-858993460). Test (IS_OFF (BUF_IOERR, pBUF->bstat)) failed. Values are 12584969 and -1. Repairing this error requires other errors to be corrected first.