How to Fix 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:

年代QL 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:

RESTORE DATABASE stellardb
FROM DISK = 'e:\backups\stellardb.bak' WITH REPLACE;

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 theObject Explorer,right-click on databases and select the optionRestore Database.

年代elect theDeviceoption, press theBrowsebutton, and select the backup file to restore the database.

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

Overwriting the existing database from restore database

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:

年代et 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 commandwith 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

如果它是一个单页,损坏,你可以重新store 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 theObject Explorer,right-click the database and selectRestore > Page.

Press theAddbutton and write the File ID that contains the error and the Page ID with the problem. Once selected, pressOK.

Pressing Add button on writing file id with the error in Page ID

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:

年代ELECT 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年代QL 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 theFindbutton 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.

数据库的脱机和复制它。

In Stellar Repair for MS SQL, press theBrowsebutton, 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.