in trouble -SQL database corruption

Recently we have been unable to do much of anything, as it seems our SQL database has some corruption. They advised us that corruption has been present for over a year, so there are no clean backups to restore from.

They have said they won't touch it since they only restore from good backups.

I have the errors listed below when I run a DB check.

Msg 8944, Level 16, State 26, Line 1

Table error: Object ID 1309576141, index ID 1, partition ID 4372442057594119651328, alloc unit ID 34720157594136100864 (type In-row data), page (1:1516648), row 9. Test (((DataRecHdr*) m_pRec)->r_tagB == 0) failed. Values are 64 and 0.

Msg 8944, Level 16, State 26, Line 1

Table error: Object ID 46456432, index ID 1, partition ID 72057594119651328, alloc unit ID 346346100864 (type In-row data), page (1:1516648), row 9. Test (((DataRecHdr*) m_pRec)->r_tagB == 0) failed. Values are 64 and 0.

Msg 8928, Level 16, State 1, Line 1

Object ID 309576141, index ID 1, partition ID 72057594119651328, alloc unit ID 72057594136100864 (type In-row data): Page (1:1516648) could not be processed. See other errors for details.

Msg 8976, Level 16, State 1, Line 1

Table error: Object ID 309576141, index ID 1, partition ID 72057594119651328, alloc unit ID 72057594136100864 (type In-row data). Page (1:1516648) was not seen in the scan although its parent (1:1526647) and previous (1:1515967) refer to it. Check any previous errors.

Msg 8978, Level 16, State 1, Line 1

Table error: Object ID 309576141, index ID 1, partition ID 72057594119651328, alloc unit ID 72057594136100864 (type In-row data). Page (1:1517757) is missing a reference from previous page (1:1516648). Possible chain linkage problem.

I have no clue what this is, I just know this is the output from me running DBCC CheckDB.

It states the only repair option is "with data loss" I have no idea how much data is in that section, as I am a complete novice at this and I have yet to find any technicians that will touch this.

CHECKDB found 0

You are definitely in tight spot as you have corrupt database, that too corruption is in Index ID 1 which is clustered index. Dropping and recreating the index on table would not help. Like @Jonathan Fite has suggested find out the table from the object ID mentioned and see what that table includes. One probable solution is can suggest is to

-
Script out structure of new database using database import export wizard. If this fails due to corrupted object ignore that table and select rest others.

-
Run the script to create empty database just make sure the data files physical names are different or else it will flash error

-
Now move data from corrupted database to new database using SSDT tool. Do not import the table which is corrupted, this one Object ID 309576141

-
See how much of data you can transfer. The max you can do better it is.

-
Now try moving data from corrupt table, see how far you go.

-
Now you have new DB with almost all fine .data.

-
Run dbcc checkdb on newly created database and this should come clean.

-
Now run DBCC CHECKDB ('db_name', REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGS; . Let the process run and see if this fixes your corruption.

-
You now at least have a database with valid data. This process will save maximum data as much as possible.

-
Most important find out root cause of corruption. Probably your storage is corrupt, the disk attached to the PC. Make sure you get that fixed.

EDIT:

Contacted OP via Skype and below is what I did

• The DB was accessible and corruption existed in one table.However the application model was throwing error so app was almost down.

• The OP has backups but no idea actually when corruption kicked in hence did restore verifyonly on lot of backups.

• Found out backup which was fine and restored and ran checkdb again to my horror the corruption was still there.

• This is quite possible as verifyonly checks are limited.

• Identified the table which has corruption.

6.Ran checkdb with repair_allow_data_loss and it worked.The checkdb repaired small corruption which was there.

CAUTIONED him about after affects of checkdb with repair_allow_data_loss, he was ok with data loss as the backups were corrupt and he hardly had idea when corruption kicked in. Also highlighted the importance of running checkdb once a week atleast.

It turned out checkdb repair was almost with no data loss, OTOH restoring from old 1 month backup could have lead to more data loss.

StackExchange Database Administrators Q#319739, answer score: 5