Search

Monday, February 28, 2011

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 inCTableMetadata::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 <= colInfo.CbMaxLen () && pCol != NULL) failed. Values are 1120 and 1088.

  • Server: Msg 2537, Level 16, State 1, Line 1 Table error: Object ID 2, index ID 0, page (1:3282), row 6. Record check (length <= colInfo.CbMaxLen () && pCol != NULL) failed. Values are 1120 and 1088.

Resolution

In order to sort out this behavior, you need to restore affected SQL Server database from the most recent backup. A valid and updated backup is the most effective way to perform SQL Server recovery.

In case the backup is not available or updated, you are required to opt for MS SQL recovery software to ensure absolute and safe recovery.

MDF recovery applications use highly-advanced scanning methods to successfully recover corrupt SQL Server database in all corruption scenarios. They have simple graphical user interface and read-only conduct to offer easy and safe recovery.

1 comment:

  1. Use SQL recovery software which is an advanced tool to quickly fix SQL error 2537 & repair corrupted database of SQL Server MDF & NDF files. Read more: http://www.sqlrecoverysoftware.net/blog/sql-error-2537.html

    ReplyDelete