Effortlessly Repair Clustered Index In SQL Server


Overview

Most of the users of SQL Server need to deal with several issues while working with the database including corruption related problems. One of the common issue is corruption of clustered index. The data becomes inaccessible to the user when this issue occurs in MS SQL Server. Inaccessibility of the database may be the worst situation for the users working on the database. Data is the integral part of every organization and loss of data may require users to start their work from scratch taking lots of time and effort. The blog will be discussing about the way to repair Clustered Index in SQL Server in order to avoid such mishaps.

SQL Server Clustered Index Corruption Issues

Clustered Index can be defined as the index that reorders the way records in the table are physically stored. A table can have only one clustered index and the leaf nodes of a clustered index contain the data pages. E.g., suppose we have a table named Student having column Roll_No on which clustered index is created, all the rows will be sorted on the disk space according to column Roll_No.

Since there can be only one physical order in the database, it is recommended to have more non-clustered index in the database as a backup. If the clustered index of the database is corrupted, the following error message will be displayed:

Repair Clustered Index

Due to corruption, it seems like there are more than one clustered-indexes, which is not possible. The corruption can occur in two ways either in some pages of the index or all the pages.

Methods to Repair Clustered Index

DBCC CHECKDB command can be used to check logical & physical integrity of database and see which objects are corrupted.

DBCC CHECKDB (CorruptDB) With No_InfoMsgs, All_ErrorMsgs,
TableResults;
GO

This command will return Database ID, Object ID, and Index ID. Index ID mapping contains ID0=Heap, ID1=Clustered and ID2=Non-Clustered.

If the index ID is 0 or 1, we can use the following methods:

  1. Restore using Backup
    Backup will help in restoring a working copy of database. If corruption has been spread only to a single page of the index, it can easily be repaired using page-level restoration. If many pages have been corrupted, then database level restoration is needed.

    • If the data model is full or bulk logged, then user can take tail-log backup (capture log records that have not been backed up).
      RESTORE DATABASE DB_Name FROM DISK=’C: /DB_Name.bak’ WITH NORECOVERY
    • For repairing a specific page, use this command:
      RESTORE DATABASE DB_Name PAGE=’1:85567’ FROM DISK=’C: /DB_Name.bak’ WITH NORECOVERY
      Where ‘1:85567’ means Index ID is 1 and page number is 85567.
  2. With REPAIR_REBUILD
    Following command will be used for the minor corruption on the database to repair the corrupted database and rebuild them.
    DBCC CHECKDB (‘DB_Name’, REPAIR_REBUILD)
  3. With REPAIR_ALLOW_DATA_LOSS
    This option is used when the user allows data loss with the attempt to repair all errors. If the corruption is severe, then it will deallocate the page from memory and modifies the links. The command for this method is:
    DBCC CHECKDB (‘DB_Name’, REPAIR_ALLOW_DATA_LOSS)

Conclusion

The blog has been aimed to help users in resolving one of the common error in SQL server database i.e. when the clustered index is corrupted. The corrupted clustered index needs to be repaired in order to make the database accessible. Some of the possible solutions such as using DBCC Command, Restore using Backup and other options have been explained in the blog that will guide users in repairing the cluster index. In case, if the above workaround is failed to repair clustered indexes or recent backup of current database has not been taken timely, then one can go with SQL MDF Recovery Tool to fix corrupted indexes in SQL Server.

Advertisements
This entry was posted in Cluster issue, Erros, issues. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s