I woke up this morning to corruption in one of my databases.  I have a job that runs daily DBCC CHECKDB's for all databases on a server.  The output indicated corruption in only ONE table (fortunately).

Msg 8935, Level 16, State 1, Line 1
Table error: Object ID 1946163866, index ID 11, partition ID 72057601280442368, alloc unit ID 72057601393164288 (type In-row data). The previous link (5:216001) on page (5:216002) does not match the previous page (5:2603841) that the parent (5:3902159), slot 23 expects for this page.
Msg 8936, Level 16, State 1, Line 1
Table error: Object ID 1946163866, index ID 11, partition ID 72057601280442368, alloc unit ID 72057601393164288 (type In-row data). B-tree chain linkage mismatch. (5:628894)->next = (5:628895), but (5:628895)->Prev = (5:2602254).
Msg 8935, Level 16, State 1, Line 1
Table error: Object ID 1946163866, index ID 11, partition ID 72057601280442368, alloc unit ID 72057601393164288 (type In-row data). The previous link (5:1165706) on page (5:1165707) does not match the previous page (5:2602253) that the parent (5:1529), slot 33 expects for this page.
Msg 8980, Level 16, State 1, Line 1
Table error: Object ID 1946163866, index ID 11, partition ID 72057601280442368, alloc unit ID 72057601393164288 (type In-row data). Index node page (5:2239068), slot 33 refers to child page (5:2602254) and previous child (5:628894), but they were not encountered.
Msg 8980, Level 16, State 1, Line 1
Table error: Object ID 1946163866, index ID 11, partition ID 72057601280442368, alloc unit ID 72057601393164288 (type In-row data). Index node page (5:3902159), slot 22 refers to child page (5:2603841) and previous child (5:216001), but they were not encountered.
Msg 8981, Level 16, State 1, Line 1
Table error: Object ID 1946163866, index ID 10, partition ID 72057601280507904, alloc unit ID 72057601393229824 (type In-row data). The next pointer of (5:1212172) refers to page (5:3489726). Neither (5:3489726) nor its parent were encountered. Possible bad chain linkage.
Msg 8980, Level 16, State 1, Line 1
Table error: Object ID 1946163866, index ID 11, partition ID 72057601280442368, alloc unit ID 72057601393164288 (type In-row data). Index node page (5:1529), slot 32 refers to child page (5:2602253) and previous child (5:1165706), but they were not encountered.
Msg 8978, Level 16, State 1, Line 1
Table error: Object ID 1946163866, index ID 10, partition ID 72057601280507904, alloc unit ID 72057601393229824 (type In-row data). Page (5:3489727) is missing a reference from previous page (5:1212172). Possible chain linkage problem.
Msg 2533, Level 16, State 1, Line 1
Table error: page (5:2602253) allocated to object ID 1946163866, index ID 11, partition ID 72057601280442368, alloc unit ID 72057601393164288 (type In-row data) was not seen. The page may be invalid or may have an incorrect alloc unit ID in its header.
Msg 2533, Level 16, State 1, Line 1
Table error: page (5:2602254) allocated to object ID 1946163866, index ID 11, partition ID 72057601280442368, alloc unit ID 72057601393164288 (type In-row data) was not seen. The page may be invalid or may have an incorrect alloc unit ID in its header.
Msg 2533, Level 16, State 1, Line 1
Table error: page (5:2602255) allocated to object ID 1946163866, index ID 11, partition ID 72057601280442368, alloc unit ID 72057601393164288 (type In-row data) was not seen. The page may be invalid or may have an incorrect alloc unit ID in its header.
Msg 2533, Level 16, State 1, Line 1
Table error: page (5:2603840) allocated to object ID 1946163866, index ID 11, partition ID 72057601280442368, alloc unit ID 72057601393164288 (type In-row data) was not seen. The page may be invalid or may have an incorrect alloc unit ID in its header.
Msg 2533, Level 16, State 1, Line 1
Table error: page (5:2603841) allocated to object ID 1946163866, index ID 11, partition ID 72057601280442368, alloc unit ID 72057601393164288 (type In-row data) was not seen. The page may be invalid or may have an incorrect alloc unit ID in its header.
Msg 2533, Level 16, State 1, Line 1
Table error: page (5:2603842) allocated to object ID 1946163866, index ID 11, partition ID 72057601280442368, alloc unit ID 72057601393164288 (type In-row data) was not seen. The page may be invalid or may have an incorrect alloc unit ID in its header.
Msg 8935, Level 16, State 1, Line 1
Table error: Object ID 1946163866, index ID 11, partition ID 72057601280442368, alloc unit ID 72057601393164288 (type In-row data). The previous link (5:2602255) on page (5:15918) does not match the previous page (5:15917) that the parent (5:3904979), slot 8 expects for this page.
Msg 8936, Level 16, State 1, Line 1
Table error: Object ID 1946163866, index ID 11, partition ID 72057601280442368, alloc unit ID 72057601393164288 (type In-row data). B-tree chain linkage mismatch. (5:15917)->next = (5:15918), but (5:15918)->Prev = (5:2602255).
CHECKDB found 0 allocation errors and 16 consistency errors in table 'AperioCompare.PolicyAmendmentCompare' (object ID 1946163866).
CHECKDB found 0 allocation errors and 16 consistency errors in database 'Aperio'.
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (Aperio).


I'm always looking for the last line so I can figure out the minimum level of recovery involved.  In this case, it says "repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB".

I looked at the Object ID and verified OBJECT_NAME(1946163866) indeed pointed to my ONE table.  The error report showed problems with index ID's 10 and 11.  I then selected from sys.indexes for object_id=1946163866 and found the index rows for indexes 10 and 11 - both of these indexes are non-clustered.  I dropped and recreated both non-clustered indexes and re-ran my DBCC CHECKDB - no errors were reported.

I'm puzzled by the minimum level of recovery reported by the DBCC CHECKDB as being repair_allow_data_loss.  Shouldn't this be rebuild?

Am I missing something obvious here?

I once had a table and it was shiny and beautiful. It held all the financial transactions for an organization. And then we started loading data into it. 

In the current month, they can state and restate values as often as they want. In the final 10 days of a month, they'd restate numbers -> run ETL processing -> review reports several times a day. Once the month is complete, then the books are sealed and they can't modify values.

It's amazing how much financial data a financial services firm generates... Something we didn't realize with our test data set was the volume of data was going to make their month end procedures untenable. It took an increasingly long time to delete out the "current month's data" prior to replacing it with the new trial run. 

We had to do something to make it faster for processing without breaking the uncatalogued list of "who knows what" that all depends on the MonthlyAllocation table. I decided to play magician and whip the tablecloth out from underneath them. I went old-school and used a Partitioned View. The data already had  an IsComplete flag so I made two tables - each with contrary check constraints: MonthlyAllocationComplete, MonthlyAllocationInComplete

I then created the partitioned view with the same name as the original table: MonthlyAllocation. No process was any the wiser about the physical change we made to the database. No reports broke, none of the analysts with direct access reported any issues with that "table" before or after.

Cool story bro, but where you going?

What if they had a naming convention there, tbl_MonthlyAllocation? Now what? Do we spend lots of man hours going through every ETL, every report, every ad-hoc spreadsheet in the organization and updating them to use vw_MonthlyAllocation? And then of course all those changes go through the Change Board and that's always a quick and painless process. 

You boss might ask: What's the reward to the company for all that work again? 

The other option becomes we leave this view named as tbl_ and not spend all that time testing, updating and deploying code. Which becomes an amusing anecdote you explain to all the new hires, and those with short attention spans, that have to work with the database as to why you are inconsistent with the naming of objects

Or you don't double encode objects with redundant metadata. The database will happily tell you what is a table, what is a view, what is a table valued function, etc. 

Naming conventions are good, just don't paint yourself into a corner with them.
