Hi experts - I have a 2015 database that is corrupted.
This is the output from DBCC CHECKDB
Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID 0 (type Unknown), page ID (1:31462320) contains an incorrect page ID in its page header. The PageId in the page header = (0:0).
I ran DBCC CHECKDB(dbName) REPAIR_REBUILD with no luck. REPAIR_FAST also failed.
Do I have a corrupted index and, if that's likely, how can I find the table name and the actual index that needs to be rebuilt?
OR is this a matter of bad storage?
Thanks for any tips.
Did DBCC say what the minimum REPAIR option is? If it is the "Repair With Data Loss" I suggest you restore from Backup (otherwise you may well not be able to deduce 100% what data is lost and you may well have Foreign Key inconsistencies and all sorts of ghastly outcomes if you allow Data Loss).
Suggest you repeat DBCC CHECKDB (after each repair) to see if there is any other Repair Command Request, and then run that and then repeat the DBCC CHECKDB, until you get a "clean" outcome.
There is a [suspect_pages] table in MSDB, if that has any rows in it (normally not) that may suggest failing hardware.
P.S. In addition to "fixing" the database you also need to find, isolate, and fix the cause of the problem - otherwise its just going to happen again. if you don't already do so I suggest changing all the databases to maximum safety setting of PAGE_VERIFY i.e. CHECKSUM (and rebuild every index and table in the database to ensure they are in place). Also turn on WITH CHECKSUM on Backups. Those things will provide you with the earliest alert to Corruption, and you then stand a good change of associating the time of that alert with Event Logs etc. to narrow down the cause - a power cut is one thing, a flaky disc controller is something altogether different
Thanks very much for your suggestions, Kristen.
I looked at the suspect_pages table in msdb. 8 rows (identifying 8 pages) were returned. Per SQL logs, the corruption began yesterday.
And no, the checkdb results did not recommend any type of Repair.
As this is a TEST instance/database, we do not have a backup to revert to. But it may be a hardware/controller issue, correct?
Well if you don't care about the database and can e.g. reconstitute if from somewhere else, and no work is lost if you lose the database, then that's fine of course. Anything else needs a backup.
Our backup routine detects that a new database has been created and adds it to the backup routine (using the most aggressive / data-critical backup plan - we can, subsequently, change that to e.g. "once a day" if that's all it needs). For us, if we deliberately name a database with the prefix "RESTORE_" then that is excluded from the automatic backup database-add, anything else gets included in the backup within 5 minutes of being created ...
... I sure as heck wouldn't want to have to rely on my memory to make sure that it was either backed up, or even added to a backup plan
Minion Backup does much the same (don't know about a default name for "Don't back this up"). Its free.
if you failed to fix this error by running above command also don't have recent backup then you can go with third party SQL Recovery program to repair corrupted index.
Thanks Jason_clark. Because dealing with hardware/storage issues as the cause is painful, I would like to first find and recreate any damaged indexes. How can I identify wich tables have corrupted indexes? Thanks
if you allow Data Loss).