How to Fix SQL Database Logical Consistency Based I/O Error

There can be several reasons that cause Database Logical Consistency based I/O error:

Unexpected system shutdown/crash or forced shutdown
SQL administrator tries to query or modify SQL data

In both the circumstances, the Host instances on SQL server restart repeatedly or terminate the connection between SQL application and the database. The following error message is displayed in SQL Server error log:

SQL Server detected a logical consistency-based I/O error: incorrect pageid (expected 1:1848; actual 0:0). It occurred during a read of page (1:1848) in database ID 10 at offset 0x00000000e70000 in file ‘C:Program FilesMicrosoft SQL ServerMSSQL10.MSSQLSERVERMSSQLDATAABCDb.mdf’. Additional messages in the SQL Server error log or system event log may provide more detail. This is a severe 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.

When a SQL Server database experiences a logical consistency based error, the first step is to diagnose the cause of error. The following methods can help in detecting the error:

Use Best Practice Analyzer (BPA) tool – BPA tool helps in detecting critical consistency error. This tool is specific to the SQL-version, meaning BPA 2008 is available with SQL 2008 and so on.
Check Windows System at Event Log system level, driver or disk related errors
Check file system integrity by running chkdsk command
Run the diagnostics recommended by hardware manufacturers for the system or disk
For SQL server 2008, run the utility SQLIOSim on the drive of the database that has reported I/O error.
Contact hardware vendor or device manufacturer to verify that hardware requirements confirm with I/O requirements of SQL server.
Check and run DBCC CHECKDB in SQL server database. DBCC CHECKDB command helps to check logical and physical consistency of SQL database pages, rows, system table referential integrity, index relationships and other structural checks. In case the check fails, error is reported as a part of CHECKDB command. The following DBCC CHECKDB command can be used:

DBCC CHECKDB (ABCD) WITH NO_INFOMSGS, ALL_ERRORMSGS

Executing above command highlights the problem in detail, and SQL database may display the I/O error message as follows:

Msg 8906, Level 16, State 1, Line 1

Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID 0 (type Unknown), page ID (1:1846) contains an incorrect page ID in its page header. The PageId in the page header = (0:0).

Such error messages help us reach a conclusion that SQL Database logical consistency based I/O error is caused either due to hardware issues or due to corruption in SQL database.

The following methods can be used to resolve logical consistency based IO error:

Method 1 – Check hardware and application connectivity

Database inconsistency error can be resolved by establishing proper connectivity between the database and SQL application.

Method 2 - Restore from SQL backup

The most feasible solution is to use backup for SQL database restoration. Before restoring from backup, check that:

You have a recent full backup
Backup is updated, just prior to corruption, and not long ago so as to avoid critical data loss
Corruption is at page level, as page level corruption issue can be resolved with the help of page-level restoration.

Note: Page-level restoration is valid when you are dealing with large database and corruption is in only in one-page of SQL database.

Step 1: Use the following command for restoring SQL database from full backup

Backup the transaction log
BACKUP LOG PageLevelRestores TO
DISK = 'g:PageLevelRestores_LOG1.bak'
WITH INIT
GO

Step 2: Perform the restoration change to reflect the changes online.

Backup the tail of the log...
BACKUP LOG PageLevelRestores TO
DISK = 'g:PageLevelRestores_LOG_TAIL.bak'
WITH INIT
GO

Note: There is no need to restore full SQL server database if corruption is confined to a single page. You can restore database from the available backup for that page which is corrupted. Performing the following commands will help in restoring backup for a single page:

Restore all available log backups in the correct order
RESTORE LOG PageLevelRestores FROM
DISK = 'g:PageLevelRestores_LOG1.bak'
WITH NORECOVERY
GO

-- Finally restore the tail log backup
RESTORE LOG PageLevelRestores FROM
DISK = 'g:PageLevelRestores_LOG_TAIL.bak'
WITH NORECOVERY
GO

-- Finally finish with the restore sequence
RESTORE DATABASE PageLevelRestores WITH RECOVERY
GO

Once the database backup has restored SQL database, run the query DBCC CHECKDB again to check that the select statement succeeds without SQL Database logical consistency-based I/O error. This command also checks that there is no data loss in this table.

Limitations of SQL database backup:

It is not possible to restore from SQL database backup when the available backup is obsolete.
If Logical consistency based I/O error is spread across the SQL server database, then this method will not be valid.
For those cases where faulty page exists in a non-clustered index, the SQL database can be fixed by dropping and recreating the index of SQL database.

Method 3: Repair corrupt SQL database with REPAIR_ALLOW_DATA_LOSS

REPAIR_ALLOW_DATA_LOSS is the minimum repair level for the diagnosed errors.

Notes: Before using REPAIR_ALLOW_DATA_LOSS, perform the following:

Take a backup of SQL server database and save it with another name
Set SQL database in Single user mode
Get all Tables record count by using the following commands

DECLARE @T_Name VARCHAR(250)

DECLARE @COUNT INT

DECLARE @SQL VARCHAR(2000)

CREATE TABLE #T_Info(ID INT IDENTITY(1,1),T_Name VARCHAR(200),D_Count INT)

DECLARE TINFO_CUR CURSOR FOR

SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES

WHERE TABLE_TYPE='BASE TABLE'

OPEN TINFO_CUR

FETCH NEXT FROM TINFO_CUR INTO @T_Name

WHILE @@FETCH_STATUS =0

BEGIN

SET @SQL='INSERT INTO #T_Info(T_Name,D_Count) SELECT '''+@T_Name+''',COUNT(*) FROM '+@T_Name+''

EXECUTE (@SQL)

FETCH NEXT FROM TINFO_CUR INTO @T_Name

END

CLOSE TINFO_CUR

DEALLOCATE TINFO_CUR

SELECT * FROM #T_Info ORDER BY T_NAME

The following steps help to fix SQL database and resolve logical consistency based I/O error:

Run the command:

DBCC CHECKDB (DB_NAME, REPAIR_ALLOW_DATA_LOSS)

Once SQL database is repaired, repeat the process of ‘Table Record Count’ and compare with old record count.

There must not be any difference between initial and final Table record count.

Limitations:

REPAIR_ALLOW_DATA_LOSS may fix Database logical consistency-based IO error but there is a major issue of data loss, where an organization may experience loss of critical data.

Alternative Solution

If above methods won't work then, give a try to a SQL Recovery Software. Stellar Repair for MS SQL repairs corrupt MDF and NDF files and restores all the database objects. It supports MS SQL 2019 and all the lower versions.

Conclusion

In this post, we have highlighted the cause of SQL Database logical consistency-based I/O error and the methods to detect this error.

Based upon the error, we have tried to find the correct resolution method. If hardware or system is responsible for the error, it is recommended to resolve the hardware-related issues, and in case DBCC CHECKDB reports consistency error, then try to restore SQL database by using an updated backup.

If the issue is not resolved with hardware correction and backup then try to repair the database with the help of REPAIR_ALLOW_DATA_LOSS. This is the minimum level of repair to resolve all errors from CHECKDB, but this does not mean that it will actually fix the error. Furthermore, it may result in data loss.

SQL repair tool helps in repairing corrupt MS SQL database including corrupt .mdf and .ndf files and recovers all database components – Tables, Triggers, Indexes, Keys, Rules, deleted records and Stored Procedures. It is of great help in times of crisis, as the software provides a definite repair solution and supports all SQL database versions including the recent and older ones.

How to Fix SQL Database Logical Consistency Based I/O Error

DBCC CHECKDB (ABCD) WITH NO_INFOMSGS, ALL_ERRORMSGS

Limitations of SQL database backup:

Rate

Share

Share

Rate