subject: DBCC DBREINDEX Fails to Rebuild Microsoft SQL Server Database Indexes [print this page] DBCC DBREINDEX Fails to Rebuild Microsoft SQL Server Database Indexes
Microsoft SQL Server uses indexes to effectively manage the database and improve its performance. It helps database manager to easily locate the specified table and extract it. However, in some situations the indexes may get damaged due to high fragmentation and database may become inaccessible. To overcome such issues, SQL Server provides you a tool, called DBCC DBREINDEX, to rebuild the database indexes. But, sometimes, this tool fails to work and database remains in unusable state. This behavior leads to critical data loss situations and requires SQL database recovery to be fixed, if there is no updated backup in place.
In a practical situation, access violation may occur in CTableMetadata::LoadIndex function when you run the DBCC DBREINDEX on an SQL Server table that contains hypothetical indexes. Such indexes are created by Index Tuning Wizard during tuning process. They are generally dropped at the end of Index Tuning Wizard procedure. But, sometimes these indexes are not deleted correctly.
Furthermore, you may come across the below error message when you run the DBCC CHECKDB utility on problem database:
"Server: Msg 7995, Level 16, State 3, Line 1
Database 'test' consistency errors in sysobjects, sysindexes, syscolumns, or systypes prevent further CHECK processing."
The above error indicates that sysindexes table is corrupt. If you run the DBCC CHECKTABLE on corrupt sysindexes table, further errors occurs that resembles the followings:
Server: Msg 2537, Level 16, State 40, Line 1
Table error: Object ID 2, index ID 0, page (1:3282), row 5. Record check (length
