SQL users are often plagued by various odd cases of database corruption. One of them is corruption of a ‘statblob’. A ‘statblob’ corruption error in SQL Server 2000 would normally look like:
'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.'
'Statblob' refers to an image column that stores statistics for an index. There is a row for each index in the 'sysindexes' table and the image column of this table is used to keep the statistics data for the corresponding index. So, it is very similar to an LOB column that is prone to corruption. As this type of corruption is inside the system table, it cannot be repaired using DBCC CHECKDB.
In order to fix this, you need to drop the offending index. The first step towards this approach is to find out the name of the index (or statistic), the table corresponding to the index, and if it is an index or column statistics set. This information can be obtained by using the ‘object_id’ and ‘index_id’ given in the error message. You can run a query on the system tables as follows:
‘SELECT object_name(id) as TableName, name as IndexName, IndexProperty (id, name, 'IsStatistics') AS IsColumnStatistics
'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.'
'Statblob' refers to an image column that stores statistics for an index. There is a row for each index in the 'sysindexes' table and the image column of this table is used to keep the statistics data for the corresponding index. So, it is very similar to an LOB column that is prone to corruption. As this type of corruption is inside the system table, it cannot be repaired using DBCC CHECKDB.
In order to fix this, you need to drop the offending index. The first step towards this approach is to find out the name of the index (or statistic), the table corresponding to the index, and if it is an index or column statistics set. This information can be obtained by using the ‘object_id’ and ‘index_id’ given in the error message. You can run a query on the system tables as follows:
‘SELECT object_name(id) as TableName, name as IndexName, IndexProperty (id, name, 'IsStatistics') AS IsColumnStatistics
FROM sysindexes
WHERE (id = 238623893 and indid = 1)’
The output will display the table name under ‘TableName’, index name under ‘IndexName’, and ‘0’ or ‘1’ under ‘IsColumnStatistics’. You should drop all the indexes and statistics shown in the results by running these commands:
‘DROP INDEX index_name ON table_name’
The output will display the table name under ‘TableName’, index name under ‘IndexName’, and ‘0’ or ‘1’ under ‘IsColumnStatistics’. You should drop all the indexes and statistics shown in the results by running these commands:
‘DROP INDEX index_name ON table_name’
‘DROP STATISTICS table_name.statistic_name’
After completion of the process, you can try running CHECKDB in order to make sure that the corruption has been repaired. If the above process fails, you can restore the database from a recent clean backup.
If you receive the same error again, you should use commercial third-party SQL database recovery tools. This software easily fix corruption in your SQL databases to retrieve all valuable objects, including tables, queries, views, stored procedures, etc. They recover databases for which DBCC CHECKDB fails to perform a successful repair. With the help of these utilities, you can perform selective recovery of database components and restore them to a new database at any desired location of choice. Further, they are compatible with Windows 7, Server 2008, Vista, Server 2003 and XP.
After completion of the process, you can try running CHECKDB in order to make sure that the corruption has been repaired. If the above process fails, you can restore the database from a recent clean backup.
If you receive the same error again, you should use commercial third-party SQL database recovery tools. This software easily fix corruption in your SQL databases to retrieve all valuable objects, including tables, queries, views, stored procedures, etc. They recover databases for which DBCC CHECKDB fails to perform a successful repair. With the help of these utilities, you can perform selective recovery of database components and restore them to a new database at any desired location of choice. Further, they are compatible with Windows 7, Server 2008, Vista, Server 2003 and XP.
No comments:
Post a Comment