[Fixed]: Logical Consistency-Based IO Error in SQL Server Database

When trying to modify data in the SQL database, you may encounter the logical consistency-based I/O error.

The complete error message may look like this:

SQL Server detected a logical consistency-based I/O error: incorrect pageid (expected 1:1628; actual 0:0). It occurred during a read of page (1:1628) in database ID 10 at offset 0x00000002a70000 in file ‘C:Program FilesMicrosoft SQL ServerMSSQL10.MSSQLSERVERMSSQLDATAstellardb.mdf’.

Why this error occurs?

There are several reasons that can damage the database, resulting in the logical consistency-based I/O error. Some common reasons are:

• Problems in the hardware due to unexpected server restarting
• Hard drive overheating
• Malicious software
• Viruses
• File system inconsistencies

Ways to Fix the Logical Consistency-based I/O Error in SQL Database

You can try the following ways to fix this error and restore the database.

Restore Database from Backup

The easiest way to solve this issue is to restore the database from the most recent backup and replace the corrupt one. You can use the following code in T-SQL to restore the database:

The above code will restore the database, named stellardb, stored at the path e:\backups\stellardb.bak. You can change the database name in the code.

Alternatively, you can restore the database using the user interface in SSMS. Here are the steps:

• In the Object Explorer, right-click on databases and select the option Restore Database.

• Select the Device option, press the Browse button, and select the backup file to restore the database.

• On the Options page, select the Overwrite the existing database (WITH REPLACE). This will replace the SQL Server database with logical consistency-based I/O error with the backup.

Use the DBCC CHECKDB Command

If you do not have a database backup, then you can use the DBCC CHECKDB command. This option can repair the database if it is corrupt. Follow the below instructions:

• Set the database in emergency mode using the below command.

ALTER DATABASE stellar SET EMERGENCY;

• Then, use the below command to set the database in single-user mode.

ALTER DATABASE stellar SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

• Now, repair the database using the DBCC CHECKDB command with the REPAIR_REBUILD option (see the below command). You could also use the REPAIR_FAST option or the REPAIR_ALLOW_DATA_LOSS option.

DBCC CHECKDB (stellardb, REPAIR_REBUILD) WITH NO_INFOMSGS, ALL_ERRORMSGS;

• Finally, set the database to multi-user mode.

ALTER DATABASE stellardb SET MULTI_USER;

Restore the Damaged Page

If it is a single page that is damaged, you can restore that page from the backup. This option is useful if your database is large. For this, open the SSMS and follow the below steps:

• In the Object Explorer, right-click the database and select Restore > Page.

• Press the Add button and write the File ID that contains the error and the Page ID with the problem. Once selected, press OK.

• You can find the Page ID when you run the DBCC CHECKDB command. It will show you the Page ID or Page IDs with errors.
• To get the File ID, use the following query:

SELECT name as FileName, file_id AS FileID FROM sys.database_files;

Use a Third-Party SQL Repair Tool

If the DBCC command does not work and you do not have a backup, then the best option is to use a third-party SQL database repair tool. The software can easily repair MDF and NDF files (SQL Server data files that contain the database’s data). It can restore tables, views, rules, stored procedures, functions triggers, and more. This software is compatible with all the SQL Server editions for Windows, including SQL Server 2022, 2019, 2017, 2016, and earlier versions.

Here’s how to use this software.

• Launch the software and use the Find button to find the database file. The file is usually stored at a location similar to this one by default: C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA.

• Take the database offline and make a copy of it.

• In Stellar Repair for MS SQL, press the Browse button, select the copy of the data file previously created and press the Repair button.

• Finally, you can save the repaired data in a new database, replace the current database, or export it to other formats, like CSV, HTML, and Excel.

Conclusion

It is not uncommon to get the logical consistency-based I/O error in SQL database. Above, we have explained the reasons for the logical consistency-based I/O error and mentioned the solutions to resolve this error. You can restore the database from a backup or repair the database using the DBCC CHECKDB command. If nothing works, you can use the Stellar Repair for MS SQL software that can repair the database and help fix the error.