A couple of weeks ago, we had a database that had become corrupted due to a disk failure. After running a DBCC CHECKDB, it appeared that there was one table with a corrupted cluster index. Paul Randal (Twitter/Blog) verified that this was the case and that it was, unfortunately, non-recoverable without data loss. Fortunately, we had backups and this database was a replication subscription, so between the backup and publication, we were able to rebuild the database without losing any data. We then had to rebuild a kind of goofy replication set up, but that’s another story.
As I was doing some validation about the data that was contained in the backups and what needed to be pulled down from the publication, I realized that there was at least one other table that was corrupted. This is where it became confusing. I went back through the output from the CHECKDB and noticed that the table in question was not mentioned. At this point, I went through and matched up all of the tables that were listed in the CHECKDB output against the actual tables in the database and found that there were three tables in the database that were not listed on the DBCC output. I ran DBCC CHECKTABLE against the missing tables and while two of them came back with no error, one was definitely corrupted. The CHECKTABLE command actually “terminated abnormally” on the corrupted table. I went back through the the error logs and dump files that had been generated at that time. Aside from the references to the CHECKTABLE that was run, the only object referenced was the table that had shown up in the CHECKDB output.
As a note, this is a 2008 SP1 database. I know that, when running CHECKDB from SSMS, it will only display the first 1000 errors, but there were only 274 errors mentioned in the output, so I didn’t think that was the issue. I asked Paul Randal (mentioned above) and his thought was that perhaps the metadata had also been corrupted and because of that CHECKDB may not have seen those tables as existing.
We’ve recovered from the incident, but it was a curious experience. Up until now, I’ve gone off of the CHECKDB output, but this gives me reason to think that there might be tables that aren’t included. I am interested to know whether anyone else has ever run into a similar incident. Hopefully it was just an (odd) learning experience.