November 12, 2014 at 9:03 am
I got an 823 error. I restored the last full backup to a new DB and am running Checkdb on it now.
This database is 150gb and contains just one table. All the errors are in the same non-clustered index.
So technically, I should be able to just drop the index, correct?
Here's the errors:
DBCC results for 'ABC_Hipaa'.
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 8980, Level 16, State 1, Line 1
Table error: Object ID 485576768, index ID 2, partition ID 72057594038976512, alloc unit ID 72057594047102976 (type In-row data). Index node page (1:2018313), slot 83 refers to child page (1:2360888) and previous child (1:1677418), but they were not encountered.
Msg 8944, Level 16, State 12, Line 1
Table error: Object ID 485576768, index ID 2, partition ID 72057594038976512, alloc unit ID 72057594047102976 (type In-row data), page (1:3690040), row 5. Test (ColumnOffsets <= (nextRec - pRec)) failed. Values are 15 and 3.
Msg 8944, Level 16, State 17, Line 1
Table error: Object ID 485576768, index ID 2, partition ID 72057594038976512, alloc unit ID 72057594047102976 (type In-row data), page (1:3690041), row 4. Test (columnOffsets->offTbl [varColumnNumber] <= (nextRec - pRec)) failed. Values are 49 and 44.
Msg 8944, Level 16, State 14, Line 1
Table error: Object ID 485576768, index ID 2, partition ID 72057594038976512, alloc unit ID 72057594047102976 (type In-row data), page (1:3690042), row 5. Test (ColumnOffsets + (int)sizeof (COLOFF) <= (nextRec - pRec)) failed. Values are 22 and 21.
Msg 8944, Level 16, State 17, Line 1
Table error: Object ID 485576768, index ID 2, partition ID 72057594038976512, alloc unit ID 72057594047102976 (type In-row data), page (1:3690043), row 4. Test (columnOffsets->offTbl [varColumnNumber] <= (nextRec - pRec)) failed. Values are 47 and 44.
Msg 8944, Level 16, State 12, Line 1
Table error: Object ID 485576768, index ID 2, partition ID 72057594038976512, alloc unit ID 72057594047102976 (type In-row data), page (1:3690044), row 5. Test (ColumnOffsets <= (nextRec - pRec)) failed. Values are 15 and 9.
Msg 8944, Level 16, State 17, Line 1
Table error: Object ID 485576768, index ID 2, partition ID 72057594038976512, alloc unit ID 72057594047102976 (type In-row data), page (1:3690045), row 4. Test (columnOffsets->offTbl [varColumnNumber] <= (nextRec - pRec)) failed. Values are 49 and 44.
Msg 8944, Level 16, State 17, Line 1
Table error: Object ID 485576768, index ID 2, partition ID 72057594038976512, alloc unit ID 72057594047102976 (type In-row data), page (1:3690046), row 4. Test (columnOffsets->offTbl [varColumnNumber] <= (nextRec - pRec)) failed. Values are 53 and 50.
Msg 8944, Level 16, State 17, Line 1
Table error: Object ID 485576768, index ID 2, partition ID 72057594038976512, alloc unit ID 72057594047102976 (type In-row data), page (1:3690047), row 4. Test (columnOffsets->offTbl [varColumnNumber] <= (nextRec - pRec)) failed. Values are 47 and 44.
CHECKDB found 0 allocation errors and 9 consistency errors in table 'abc_hipaa' (object ID 485576768).
CHECKDB found 0 allocation errors and 9 consistency errors in database 'ABC_Hipaa'.
November 12, 2014 at 10:11 am
Yes, since the corruption is in an non-clustered index you can either REBUILD the index or explicitly drop and recreate it. You also need to make sure you check your disk subsystem to make sure there aren't issues there that will cause a worse corruption.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
November 12, 2014 at 11:35 am
The problem is definitely the SAN. I had 825 errors from this server last week. The SysAdmins are building me a new VM and I'm going to move this large database off on its own. Same SAN of course, but a different LUN. The server reporting errors is a large shared server with many unrelated databases on it. Most small and not very resource intensive. The one now corrupt is basically a big log file that grows daily that we have to keep for regulatory reasons.
I should have a good backup and that's what I'll restore on the new server. Once I have a clean database elsewhere I'll delete the index on the corrupt database and re-run the checkdb. Just to see it happen. Figured I'd only go that route if my backups were also corrupt.
November 12, 2014 at 12:07 pm
No. Because
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.
Now, if that really was TempDB out of space (easy for you to check), it means there could be other errors that haven't been picked up. If it's a system table error, that's a 'restore from last good backup' condition
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 12, 2014 at 12:54 pm
That's really interesting. There almost 500gb of free disk on the server so I couldn't imaging tempdb running out. But the first thing I did this morning was add a second tempdb file, restored the last full backup as a different database name and ran checkdb on it. It reported MORE errors. It reported 30 consistency errors where the first checkdb (results above) reported 9.
But these are in index ID 1 where the earlier errors were in index id 2.
Thanks for the reply - I didn't put that all together.
DBCC results for 'Hipaa2'.
Service Broker Msg 9675, State 1: Message Types analyzed: 14.
Service Broker Msg 9676, State 1: Service Contracts analyzed: 6.
Service Broker Msg 9667, State 1: Services analyzed: 3.
Service Broker Msg 9668, State 1: Service Queues analyzed: 3.
Service Broker Msg 9669, State 1: Conversation Endpoints analyzed: 0.
Service Broker Msg 9674, State 1: Conversation Groups analyzed: 0.
Service Broker Msg 9670, State 1: Remote Service Bindings analyzed: 0.
Service Broker Msg 9605, State 1: Conversation Priorities analyzed: 0.
DBCC results for 'sys.sysrscols'.
There are 684 rows in 9 pages for object "sys.sysrscols".
DBCC results for 'sys.sysrowsets'.
There are 98 rows in 1 pages for object "sys.sysrowsets".
DBCC results for 'sys.sysallocunits'.
There are 111 rows in 2 pages for object "sys.sysallocunits".
DBCC results for 'sys.sysfiles1'.
There are 2 rows in 1 pages for object "sys.sysfiles1".
DBCC results for 'sys.syspriorities'.
There are 0 rows in 0 pages for object "sys.syspriorities".
DBCC results for 'sys.sysfgfrag'.
There are 2 rows in 1 pages for object "sys.sysfgfrag".
DBCC results for 'sys.sysphfg'.
There are 1 rows in 1 pages for object "sys.sysphfg".
DBCC results for 'sys.sysprufiles'.
There are 2 rows in 1 pages for object "sys.sysprufiles".
DBCC results for 'sys.sysftinds'.
There are 0 rows in 0 pages for object "sys.sysftinds".
DBCC results for 'sys.sysowners'.
There are 16 rows in 1 pages for object "sys.sysowners".
DBCC results for 'sys.sysprivs'.
There are 168 rows in 1 pages for object "sys.sysprivs".
DBCC results for 'sys.sysschobjs'.
There are 96 rows in 2 pages for object "sys.sysschobjs".
DBCC results for 'sys.syscolpars'.
There are 614 rows in 11 pages for object "sys.syscolpars".
DBCC results for 'sys.sysnsobjs'.
There are 1 rows in 1 pages for object "sys.sysnsobjs".
DBCC results for 'sys.syscerts'.
There are 0 rows in 0 pages for object "sys.syscerts".
DBCC results for 'sys.sysxprops'.
There are 0 rows in 0 pages for object "sys.sysxprops".
DBCC results for 'sys.sysscalartypes'.
There are 34 rows in 1 pages for object "sys.sysscalartypes".
DBCC results for 'sys.systypedsubobjs'.
There are 0 rows in 0 pages for object "sys.systypedsubobjs".
DBCC results for 'sys.sysidxstats'.
There are 171 rows in 2 pages for object "sys.sysidxstats".
DBCC results for 'sys.sysiscols'.
There are 337 rows in 2 pages for object "sys.sysiscols".
DBCC results for 'sys.sysbinobjs'.
There are 23 rows in 1 pages for object "sys.sysbinobjs".
DBCC results for 'sys.sysaudacts'.
There are 0 rows in 0 pages for object "sys.sysaudacts".
DBCC results for 'sys.sysobjvalues'.
There are 209 rows in 44 pages for object "sys.sysobjvalues".
DBCC results for 'sys.sysclsobjs'.
There are 18 rows in 1 pages for object "sys.sysclsobjs".
DBCC results for 'sys.sysrowsetrefs'.
There are 0 rows in 0 pages for object "sys.sysrowsetrefs".
DBCC results for 'sys.sysremsvcbinds'.
There are 0 rows in 0 pages for object "sys.sysremsvcbinds".
DBCC results for 'sys.sysxmitqueue'.
There are 0 rows in 0 pages for object "sys.sysxmitqueue".
DBCC results for 'sys.sysrts'.
There are 1 rows in 1 pages for object "sys.sysrts".
DBCC results for 'sys.sysconvgroup'.
There are 0 rows in 0 pages for object "sys.sysconvgroup".
DBCC results for 'sys.sysdesend'.
There are 0 rows in 0 pages for object "sys.sysdesend".
DBCC results for 'sys.sysdercv'.
There are 0 rows in 0 pages for object "sys.sysdercv".
DBCC results for 'sys.syssingleobjrefs'.
There are 148 rows in 1 pages for object "sys.syssingleobjrefs".
DBCC results for 'sys.sysmultiobjrefs'.
There are 207 rows in 1 pages for object "sys.sysmultiobjrefs".
DBCC results for 'sys.sysguidrefs'.
There are 0 rows in 0 pages for object "sys.sysguidrefs".
DBCC results for 'sys.syscompfragments'.
There are 0 rows in 0 pages for object "sys.syscompfragments".
DBCC results for 'sys.sysftstops'.
There are 0 rows in 0 pages for object "sys.sysftstops".
DBCC results for 'sys.sysqnames'.
There are 97 rows in 1 pages for object "sys.sysqnames".
DBCC results for 'sys.sysxmlcomponent'.
There are 99 rows in 1 pages for object "sys.sysxmlcomponent".
DBCC results for 'sys.sysxmlfacet'.
There are 112 rows in 1 pages for object "sys.sysxmlfacet".
DBCC results for 'sys.sysxmlplacement'.
There are 18 rows in 1 pages for object "sys.sysxmlplacement".
DBCC results for 'sys.sysobjkeycrypts'.
There are 0 rows in 0 pages for object "sys.sysobjkeycrypts".
DBCC results for 'sys.sysasymkeys'.
There are 0 rows in 0 pages for object "sys.sysasymkeys".
DBCC results for 'sys.syssqlguides'.
There are 0 rows in 0 pages for object "sys.syssqlguides".
DBCC results for 'sys.sysbinsubobjs'.
There are 3 rows in 1 pages for object "sys.sysbinsubobjs".
DBCC results for 'sys.syssoftobjrefs'.
There are 4 rows in 1 pages for object "sys.syssoftobjrefs".
DBCC results for 'abc_audit'.
There are 9872 rows in 81 pages for object "abc_audit".
DBCC results for 'abc_hipaa_temp'.
There are 32047 rows in 617 pages for object "abc_hipaa_temp".
DBCC results for 'abc_hipaa'.
Msg 8928, Level 16, State 1, Line 1
Object ID 485576768, index ID 1, partition ID 72057594038845440, alloc unit ID 72057594046775296 (type In-row data): Page (1:437376) could not be processed. See other errors for details.
Msg 8976, Level 16, State 1, Line 1
Table error: Object ID 485576768, index ID 1, partition ID 72057594038845440, alloc unit ID 72057594046775296 (type In-row data). Page (1:437376) was not seen in the scan although its parent (1:434569) and previous (1:437375) refer to it. Check any previous errors.
Msg 8944, Level 16, State 18, Line 1
Table error: Object ID 485576768, index ID 1, partition ID 72057594038845440, alloc unit ID 72057594046775296 (type In-row data), page (1:437376), row 3. Test (columnOffsets->offTbl [varColumnNumber] >= priorOffset) failed. Values are 108 and 133.
Msg 8944, Level 16, State 18, Line 1
Table error: Object ID 485576768, index ID 1, partition ID 72057594038845440, alloc unit ID 72057594046775296 (type In-row data), page (1:437376), row 3. Test (columnOffsets->offTbl [varColumnNumber] >= priorOffset) failed. Values are 108 and 133.
Msg 8978, Level 16, State 1, Line 1
Table error: Object ID 485576768, index ID 1, partition ID 72057594038845440, alloc unit ID 72057594046775296 (type In-row data). Page (1:437377) is missing a reference from previous page (1:437376). Possible chain linkage problem.
Msg 8978, Level 16, State 1, Line 1
Table error: Object ID 485576768, index ID 1, partition ID 72057594038845440, alloc unit ID 72057594046775296 (type In-row data). Page (1:11234583) is missing a reference from previous page (1:11234584). Possible chain linkage problem.
Msg 8928, Level 16, State 1, Line 1
Object ID 485576768, index ID 1, partition ID 72057594038845440, alloc unit ID 72057594046775296 (type In-row data): Page (1:11234584) could not be processed. See other errors for details.
Msg 8980, Level 16, State 1, Line 1
Table error: Object ID 485576768, index ID 1, partition ID 72057594038845440, alloc unit ID 72057594046775296 (type In-row data). Index node page (1:11242829), slot 249 refers to child page (1:11234584) and previous child (1:11234585), but they were not encountered.
Msg 8944, Level 16, State 17, Line 1
Table error: Object ID 485576768, index ID 1, partition ID 72057594038845440, alloc unit ID 72057594046775296 (type In-row data), page (1:11234584), row 1. Test (columnOffsets->offTbl [varColumnNumber] <= (nextRec - pRec)) failed. Values are 129 and 111.
Msg 8928, Level 16, State 1, Line 1
Object ID 485576768, index ID 1, partition ID 72057594038845440, alloc unit ID 72057594046775296 (type In-row data): Page (1:11234585) could not be processed. See other errors for details.
Msg 8980, Level 16, State 1, Line 1
Table error: Object ID 485576768, index ID 1, partition ID 72057594038845440, alloc unit ID 72057594046775296 (type In-row data). Index node page (1:11242829), slot 248 refers to child page (1:11234585) and previous child (1:11234586), but they were not encountered.
Msg 8944, Level 16, State 17, Line 1
Table error: Object ID 485576768, index ID 1, partition ID 72057594038845440, alloc unit ID 72057594046775296 (type In-row data), page (1:11234585), row 1. Test (columnOffsets->offTbl [varColumnNumber] <= (nextRec - pRec)) failed. Values are 133 and 115.
Msg 8928, Level 16, State 1, Line 1
Object ID 485576768, index ID 1, partition ID 72057594038845440, alloc unit ID 72057594046775296 (type In-row data): Page (1:11234586) could not be processed. See other errors for details.
Msg 8980, Level 16, State 1, Line 1
Table error: Object ID 485576768, index ID 1, partition ID 72057594038845440, alloc unit ID 72057594046775296 (type In-row data). Index node page (1:11242829), slot 247 refers to child page (1:11234586) and previous child (1:11234587), but they were not encountered.
Msg 8944, Level 16, State 17, Line 1
Table error: Object ID 485576768, index ID 1, partition ID 72057594038845440, alloc unit ID 72057594046775296 (type In-row data), page (1:11234586), row 1. Test (columnOffsets->offTbl [varColumnNumber] <= (nextRec - pRec)) failed. Values are 133 and 115.
Msg 8928, Level 16, State 1, Line 1
Object ID 485576768, index ID 1, partition ID 72057594038845440, alloc unit ID 72057594046775296 (type In-row data): Page (1:11234587) could not be processed. See other errors for details.
Msg 8980, Level 16, State 1, Line 1
Table error: Object ID 485576768, index ID 1, partition ID 72057594038845440, alloc unit ID 72057594046775296 (type In-row data). Index node page (1:11242829), slot 246 refers to child page (1:11234587) and previous child (1:11234588), but they were not encountered.
Msg 8944, Level 16, State 17, Line 1
Table error: Object ID 485576768, index ID 1, partition ID 72057594038845440, alloc unit ID 72057594046775296 (type In-row data), page (1:11234587), row 1. Test (columnOffsets->offTbl [varColumnNumber] <= (nextRec - pRec)) failed. Values are 129 and 115.
Msg 8928, Level 16, State 1, Line 1
Object ID 485576768, index ID 1, partition ID 72057594038845440, alloc unit ID 72057594046775296 (type In-row data): Page (1:11234588) could not be processed. See other errors for details.
Msg 8980, Level 16, State 1, Line 1
Table error: Object ID 485576768, index ID 1, partition ID 72057594038845440, alloc unit ID 72057594046775296 (type In-row data). Index node page (1:11242829), slot 245 refers to child page (1:11234588) and previous child (1:11234589), but they were not encountered.
Msg 8944, Level 16, State 17, Line 1
Table error: Object ID 485576768, index ID 1, partition ID 72057594038845440, alloc unit ID 72057594046775296 (type In-row data), page (1:11234588), row 1. Test (columnOffsets->offTbl [varColumnNumber] <= (nextRec - pRec)) failed. Values are 129 and 115.
Msg 8928, Level 16, State 1, Line 1
Object ID 485576768, index ID 1, partition ID 72057594038845440, alloc unit ID 72057594046775296 (type In-row data): Page (1:11234589) could not be processed. See other errors for details.
Msg 8980, Level 16, State 1, Line 1
Table error: Object ID 485576768, index ID 1, partition ID 72057594038845440, alloc unit ID 72057594046775296 (type In-row data). Index node page (1:11242829), slot 244 refers to child page (1:11234589) and previous child (1:11234590), but they were not encountered.
Msg 8944, Level 16, State 17, Line 1
Table error: Object ID 485576768, index ID 1, partition ID 72057594038845440, alloc unit ID 72057594046775296 (type In-row data), page (1:11234589), row 1. Test (columnOffsets->offTbl [varColumnNumber] <= (nextRec - pRec)) failed. Values are 129 and 111.
Msg 8928, Level 16, State 1, Line 1
Object ID 485576768, index ID 1, partition ID 72057594038845440, alloc unit ID 72057594046775296 (type In-row data): Page (1:11234590) could not be processed. See other errors for details.
Msg 8980, Level 16, State 1, Line 1
Table error: Object ID 485576768, index ID 1, partition ID 72057594038845440, alloc unit ID 72057594046775296 (type In-row data). Index node page (1:11242829), slot 243 refers to child page (1:11234590) and previous child (1:11234591), but they were not encountered.
Msg 8944, Level 16, State 17, Line 1
Table error: Object ID 485576768, index ID 1, partition ID 72057594038845440, alloc unit ID 72057594046775296 (type In-row data), page (1:11234590), row 1. Test (columnOffsets->offTbl [varColumnNumber] <= (nextRec - pRec)) failed. Values are 129 and 115.
Msg 8928, Level 16, State 1, Line 1
Object ID 485576768, index ID 1, partition ID 72057594038845440, alloc unit ID 72057594046775296 (type In-row data): Page (1:11234591) could not be processed. See other errors for details.
Msg 8976, Level 16, State 1, Line 1
Table error: Object ID 485576768, index ID 1, partition ID 72057594038845440, alloc unit ID 72057594046775296 (type In-row data). Page (1:11234591) was not seen in the scan although its parent (1:11242829) and previous (1:11234592) refer to it. Check any previous errors.
Msg 8944, Level 16, State 17, Line 1
Table error: Object ID 485576768, index ID 1, partition ID 72057594038845440, alloc unit ID 72057594046775296 (type In-row data), page (1:11234591), row 1. Test (columnOffsets->offTbl [varColumnNumber] <= (nextRec - pRec)) failed. Values are 133 and 111.
There are 472348275 rows in 8585745 pages for object "khs_hipaa".
CHECKDB found 0 allocation errors and 30 consistency errors in table 'khs_hipaa' (object ID 485576768).
DBCC results for 'sys.queue_messages_1445580188'.
There are 0 rows in 0 pages for object "sys.queue_messages_1445580188".
DBCC results for 'sys.queue_messages_1477580302'.
There are 0 rows in 0 pages for object "sys.queue_messages_1477580302".
DBCC results for 'sys.queue_messages_1509580416'.
There are 0 rows in 0 pages for object "sys.queue_messages_1509580416".
DBCC results for 'sys.filestream_tombstone_1541580530'.
There are 0 rows in 0 pages for object "sys.filestream_tombstone_1541580530".
DBCC results for 'sys.syscommittab'.
There are 0 rows in 0 pages for object "sys.syscommittab".
DBCC results for 'dtproperties'.
There are 0 rows in 0 pages for object "dtproperties".
CHECKDB found 0 allocation errors and 30 consistency errors in database 'Hipaa2'.
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (Hipaa2).
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
November 12, 2014 at 1:00 pm
Index 1 = clustered index = actual data loss. Restore from last good backup + log backups.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 12, 2014 at 1:11 pm
Jack Corbett (11/12/2014)
Yes, since the corruption is in an non-clustered index you can either REBUILD the index or explicitly drop and recreate it.
You can never rebuild to fix corruption in a NC index, because the rebuild process reads the old index. Drop and recreate, yes. Rebuild, no.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply