There have been a couple odd types of corruption that I’ve been seeing on the forums. I want to take the time to go into some detail, because the fix is possibly not obvious. The first one that I want to look at is corruption of a stat blob.
These are specifically SQL 2000 errors, I don’t know what the equivalent errors in SQL 2005 look like, if there are equivalent errors.
The errors that indicate corruption in the stats blob look like this.
Server: Msg 8929, Level 16, State 1, Line 4 Object ID 2: Errors found in text ID 153175064576 owned by data record identified by RID = (1:526:13) id = 238623893 and indid = 1. Server: Msg 8929, Level 16, State 1, Line 4 Object ID 2: Errors found in text ID 153175195648 owned by data record identified by RID = (1:526:15) id = 50815243 and indid = 3.
The things to note are the Object ID, 2 being sysindexes in SQL 2000 and the reference to an indid in the section that identifies a record.
In SQL 2000, the statistics for an index (or indeed for column statistics) was stored in an image column in sysindexes called statblob (ref). Each index (and statistic) has a row in sysindexes and keeps the statisics data in that image column. This means that the statblob is just as susceptible to corruption as any other LOB column. Unfortunately it’s not as easily fixed. In fact, since it’s a corruption in the system tables checkDB will not repair it
All well and good, so how do we fix these?
Well, two ways, one easy that doesn’t always work and one that involves a restore from backup.
The easy way, which does not always work, if to drop the offending index. To do that, first we need to identify the name of the index (or statistic), the table that it’s on and whether it’s an index or a column statistics set. The information needed to get that is the object_id and index_id and is given in the error message.
Server: Msg 8929, Level 16, State 1, Line 4 Object ID 2: Errors found in text ID 153175064576 owned by data record identified by RID = (1:526:13) id = 238623893 and indid = 1
So with that I can query the system tables to see what this is. For the two errorslisted earlier, the query would be:
SELECT object_name(id) as TableName, name as IndexName, IndexProperty(id, name, ‘IsStatistics’) AS IsColumnStatistics
FROM sysindexes
WHERE (id = 238623893 and indid = 1) OR (id = 50815243 and indid = 3)
Let’s say that the results of that query were as follows:
TableName IndexName IsColumnStatistics Person idx_Person_Department 0 Address _WA_Sys_00000006_307610B 1
So one of these is an index and the other is a statistics set. To attempt to get rid of this corruption, I’m going to try to drop both the index and the stats set. This might fail.
DROP INDEX idx_Person_Department ON Person
DROP STATISTICS Address._WA_Sys_00000006_307610B
If these succeed, run a checkDB again to be sure that the corruption has gone.
If either of the statements throws an error, then fixing this error requires that the DB be restored from a clean backup (and everyone has a clean backup, right?)
