Microsoft SQL Server is a relational database which is used by many organizations to store their data and the corruption in the database is not a new thing. Types of corruptions and levels are different. A common error is the clustered index corruption error. When this error occurs in the MS SQL server, the data becomes inaccessible to the user. This can obviously be the worst scenario for any DBA who is working on the database. For any organization using SQL Server, the data is its most integral part and losing it can definitely affect business for a long time if the problem is not taken care of at the soonest.
What is clustered index corruption?
To start with, a clustered index in a database is like a directory for all the tables that are present in the database. This is determines, where each table is kept in the database and what is its physical order.
Each database only has one clustered index, this is because each index determines the physical order of data in a database and there can only be one physical order in the database. That is why it is always recommended to have more non-clustered index in the database just as a backup.
If there is a corruption in the clustered index of the database, then you will get the following error-
SQL Server Error Message – Msg. 1902
Server: Msg. 1902‚Level 16&sbquo State3‚ Line1
Cannot create more than one clustered index on %S_MSG '%.*ls'. Drop the existing clustered index '%.*ls' before creating another.
Because of the corruption, it looks like there are more than one clustered indexes in the database when that is actually not true. The corruption could just be in the pages of the index or all the pages might have gotten corrupted.
How to solve clustered index corruption problem?
There are two ways to solve this problem
If you have a backup, then it can always help in restoring a working copy of the database. This can specially be helpful when the changes in the database have been very less. If the corruption has only spread to a single page of the index then it can easily be restored with the help of page level restoration but if more pages of the clustered database have been corrupted then you would have to do a database level restoration. You can also use DBCC CHECKDB command to check the logical as well as the physical integrity of the database and see where you are actually facing error.
Whenever you take backups you need to ensure that all of the tables and indexes in your database are in a good condition and are not corrupted. There can be times when tables stay in corrupted conditions in the database but since they haven’t been used for months, nobody discovers that they are actually corrupted. But when you finally need to restore data and run the restoration process, that is the moment when you realize a part of data in your database was corrupted all along.
To restore the corrupted pages, log backups can be used. You need to make sure all your log backups are present in the right places. That is why it is important to test backups when they are taken and make sure that they are in a good condition. For actual restoration, you would need tail log backups to bring your page to current.
- Through a third party software
If you don’t have a backup or if the backup you have is corrupted and unusable then the only way to get rid of this error is by using a third party software
. Since your data is very critical and a mistake could actually corrupt your database even more, it is very important to use a reliable recovery software.
That is why we recommend Stellar Phoenix SQL Database Repair. This software is specially created to deal with corruptions in MS SQL Server database and it can repair even the most corrupted MDF and NDF files. With the help of its ‘non-destructive repair algorithm’, it can repair your database files without damaging them even more.
Here are some great features of this software:
- It can recover indexes, triggers, keys defaults, rules and of course, tables
- Recover deleted records as well
- User can search and only recover specific objects of the database
- The recovered files can be saved in different formats including CSV, XLS, HTML and MS SQL
To use the software, just select the database which you want to recover. Once you do, the software will scan the database and display all the recoverable objects in it. If you are looking to recover the entire database then you can select all of them, otherwise you could search for the selected objects you want to recover. The software will recover and save them in the format you want.