In SQL Server, indexes are created automatically by defining the ‘Primary Key’ and ‘Unique Constraints’ on the table columns. When a primary key is configured, the database engine sets a clustered index, and when a table is created with unique constraints, the database engine sets a non-clustered index. Corruption in the clustered or nonclustered index of SQL server may result in incorrect query results.
Indexes or on-disk structures speed-up the process of retrieval of rows from the table or view. This is the reason that the corrupted index may lead to inefficiency and delay in key-value results. This problem must be resolved because if it is not addressed in due time, the entire SQL server gets affected which may result in panic situation for the database users.
Causes of corrupt clustered and non-clustered indexes:
Bugs in SQL server, inefficient server installation, abrupt system shutdown, modifications in SQL account, upgrading SQL version and other related reasons may result in corrupted index of SQL server.
Whatever be the cause, analyze the effects properly to choose the best resolution method.
How to check the corrupted index of SQL Server?
Fetch the SQL server data available in Rows
- Corruption in SQL server index may show deleted or different values. Running a ROWID command will help identify the missing entries.
- The missing entries can also be traced when the Administrator checks the Log Report. Log report indicates that the records were edited more than once and there may be multiple DELETE/UPDATE or other command used by the users.
- Perform logical and physical consistency checks on nonclustered indexes with the help of DBCC CHECKDB command
Examples of error messages caused due to corrupted index of SQL Server:
A corrupt clustered index in SQL server may result in the following error message:
Server: Msg 1902, Level 16, State 3, Line 1
Cannot create more than one clustered index on table ‘Table Name’. Drop the existing clustered index ‘Clustered Index Name’ before creating another.
A corrupt Non clustered index may result in the following error message:
Resolving Clustered and Non-Clustered Corrupt Index in SQL Server
Restoring from backup
Corruption in both Clustered index and nonclustered index can be resolved by using the backup. Most SQL Admins maintain a healthy and updated backup of MS SQL database. This backup can be used for database restoration if the database gets corrupt due to a corrupted index of SQL server or any other reason.
Limitations of restoring SQL database from backup
However, there are instances when the system admin who takes care of database backup may have missed monitoring the backup thus resulting in a non-updated backup. In such a case, database backup may not contain the latest updates. Backup restoration is not an appropriate technique to recover the MS SQL database.
SQL Database Repair Utility
SQL suggested DBCC CHECKDB command checks the physical and logical integrity of the database. It is suggested to run the command and check the Database ID, Index ID and Object ID of the corrupted index of SQL server. Use the following command to identify the corrupt Index ID:
DBCC CHECKDB (CorruptDBName) With No_InfoMsgs, All_ErrorMsgs,
TableResults;
GO
- Rebuild Corrupted Index
Once the corrupted index of SQL server is identified, remedy the index by reorganizing and rebuilding the index. Rebuilding helps in dropping the index and recreating that index. This process helps in removing defragmentation in the database and reclaiming disk space through page compaction. Compacting the page is dependent on the specified or existing fill-factor setting. The process reorders the index rows in connecting pages. If all the indexes are showing corruption, then specify “All” to drop all indexes on the table and rebuild in a single transaction.
- Reorganize Corrupted Index
Resolving the corrupted index issue by reorganizing the index requires minimal system resources. The process involves physical reordering of leaf-level pages so that these match with the logical right order of the leaf node. Reorganizing defragments the leaf level of clustered and nonclustered indexes on Tables and Views.
Reorganizing also helps to compact the index pages, based upon the actual fill factor value.
Limitations and Restrictions of Rebuild and Reorganize process
Creating and rebuilding non-aligned indexes on the table may cause excessive memory consumption and degraded server performance.
It’s not possible to reorganize or rebuilt an index if the filegroup in which it is located is offline or set to read-only.
If you have selected “All” for the indexes and one of the indexes is in the offline or read-only filegroup, the statement fails.
Microsoft suggested utility and restoration method may affect the data integrity and may lead to change in some values.
SQL Database Repair Software
Stellar Repair for MS SQL is a specialized SQL repair software designed to fix the corrupted index of SQL server and restore lost or missing tables, triggers, views, stored procedures, and all other SQL components. You can import the repaired database directly into the current database or create a new database at a specified location. The software supports SQL Server 2019, 2017, 2016, 2014, 2012, 2008 and older versions, so offers a great amount of coverage.
Limitations with the free trial of SQL Database Repair Software
The free trial version of the SQL repair software doesn’t allow saving the repaired database. However, the Scan and Preview features available in the trial version of the software help the users to ascertain the effectiveness of the software and make informed decision before going for activation. The free trial version offers a largely unlocked experience, which is remarkable.
Conclusion
In case SQL server suffers from the corrupted index, the first job of an incident management team is to generate a ticket and prioritize the task. The second step is to resolve the ticket within the prescribed Service Level Agreement or SLA. As fixing the corrupt index of SQL server is a critical issue, it should be escalated to L3 Engineer or dedicated SQL administrator.
SQL admin must refer the documents for Business Continuity-Disaster Recovery Plan (BCP-DR plan) to check if the issue can be resolved by using the methods mentioned in BCP-DR plan like Rebuild SQL server or repair using DBCC CHECK DB commands. Also, check for a reliable SQL repair software to address the issue within the prescribed downtime.
There are many limitations attached with the rebuild and repair process, hence, it is better to resolve the corrupted index of SQL server by using a reliable SQL repair software as the software provides most accurate repair results. The software fixes the corrupted clustered and non-clustered SQL indexes and recovers inaccessible database objects.
