{"id":142460,"date":"2023-07-17T11:52:39","date_gmt":"2023-07-17T11:52:39","guid":{"rendered":"https:\/\/www.stellarinfo.com\/blog\/?p=142460"},"modified":"2026-02-24T11:39:45","modified_gmt":"2026-02-24T11:39:45","slug":"repair-index-corruption-sql-server-databases","status":"publish","type":"post","link":"https:\/\/www.stellarinfo.com\/blog\/repair-index-corruption-sql-server-databases\/","title":{"rendered":"How to Repair Index Corruption in SQL Server Databases?"},"content":{"rendered":"

The indexes in the SQL Server database may get corrupted due to different reasons. When the indexes are corrupt, you may see any of the following error messages:<\/p>

Error: 823, Severity: 24, State: 2<\/a>.<\/strong><\/p>

The operating system returned error 38 to SQL Server during a read at offset 0x016I64x in file ‘c:\\data\\stellar.mdf’. Additional messages in the SQL Server error log and system event log may provide more detail. This is a severe system-level error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.<\/strong><\/p>

Error 824, Severity: 24, State: 2<\/a>.<\/strong><\/p>

SQL Server detected a logical consistency-based I\/O error: incorrect pageid (expected 12345; actual 67890) in database ‘stellardb’, file ‘YourFileName.mdf’, page 1234. It occurred during a read of page 1234 in database ID 5 at offset 67890 in file ‘stellardb.mdf’. Additional messages in the SQL Server error log or system event log may provide more detail.<\/strong><\/p>

Error 832:<\/strong><\/p>

“A page that should have been constant has changed (expected checksum: 0xABCDEF, actual checksum: 0x123456) in database ‘stellardb’, file ‘stellardb.mdf’, page 5678. This usually indicates a memory or disk problem. Please contact technical support.”<\/strong><\/p>

Below, we will see how to detect index corruption in SQL Server database and how to fix the index corruption.<\/p>

Detect Index Corruption using SQL Server Logs<\/a><\/h2>

You can see the above error messages in the SQL Server Log. In SQL Server Management Studio (SSMS), go to the Object Explorer <\/strong>and then go to Management > SQL Server Logs. <\/strong>Open the logs and you will see the error messages, including the 823, 824, and 832.<\/p>

\"Saving<\/figure>

Alternatively, you can use the xp_readerrorlog command in T-SQL. This system stored procedure reads the SQL Server error log.<\/p>

The following example illustrates how to use this command:<\/p>

EXEC xp_readerrorlog 0, 1, N’disk problem’<\/p>

The command will look for disk error messages in the error log. In SQL Server, there are several error logs. The ‘0’ indicates the current one and others from 1 to 6 by default.<\/p>

\"Reading<\/figure>

The number 1 means that you want to read the SQL Server log file.<\/p>

Detect Index Corruption using Event Viewer<\/a><\/h2>

In Windows, the Event Viewer is used to check errors in the Windows operating system, the application, and security events. SQL Server is not the exception.<\/p>

You can open the Event Viewer in Windows, write the word event, <\/strong>and click on the Event Viewer.<\/p>

\"Event<\/figure>

Then, go to Windows Logs > Applications.<\/strong><\/p>

\"Checking<\/figure>

Here, you can look for the error messages.<\/p>

\"Error<\/figure>

Detect Index Corruption using the DBCC CHECKDB Command<\/a><\/h2>

The DBCC CHECKDB command can detect not only general error messages in the database but also errors in the indexes.<\/p>

The following DBCC CHECKDB command<\/a> will detect errors in the database including the index errors.<\/p>

DBCC CHECKDB(‘stellardb’)<\/p>

If you want to exclude informational messages, you can use the following command.<\/p>

DBCC CHECKDB(‘stellardb’) WITH NO_INFOMSGS;<\/p>

There is another option to see the errors in table format. The following example shows how to do it:<\/p>

DBCC CHECKDB(‘stellardb’) with TABLERESULTS;<\/p>

Methods to Repair Index Corruption in SQL Server Database<\/h2>

Here are some methods you can follow to fix index corruption in SQL database.<\/p>

Repair Index Corruption using the DBCC CHECKTABLE<\/a><\/h2>

The DBCC CHECKTABLE is useful to repair index corruption if just a single index is damaged and you do not want to repair the entire database. The following command will check for errors in a table, named Person.PersonPhone:<\/p>

DBCC CHECKTABLE(‘[Person].[PersonPhone]’);<\/p>

To repair the table, you need to set it in single-user mode first. The following code shows how to do it:<\/p>

ALTER DATABASE [AdventureWorks2019]<\/p>

SET SINGLE_USER WITH ROLLBACK IMMEDIATE;<\/p>

Once it is in single-user mode, run this code to repair the table and the index:<\/p>

DBCC CHECKTABLE(‘[Person].[PersonPhone]’, REPAIR_REBUILD);<\/p>

If you want to repair the table fast, you can use the following command:<\/p>

DBCC CHECKTABLE(‘[Person].[PersonPhone]’, REPAIR_FAST);<\/p>

If the above commands fail, you can use the following command:<\/p>

DBCC CHECKTABLE(‘[Person].[PersonPhone]’, REPAIR_ALLOW_DATA_LOSS);<\/p>

Use an MS SQL Repair Tool<\/a><\/h2>

You can also use a third-party SQL database repair tool, such as Stellar Repair for MS SQL<\/a> to repair the corrupt SQL Server database indexes. To repair the database using the software, follow these steps:<\/p>