Ways to Fix SQL Server Detected A Logical Consistency-Based I/O Error

Summary: SQL Database inaccessibility issue is the common problem faced by the database users. So this problem tackling blog will discuss SQL Server Detected A Logical Consistency-Based I/O Error. We are going to discuss the reason behind this error and the the best possible ways to know how to tackle this issue. To access and recover the inaccessible SQL database then the user can take the help of Recover SQL Server Database Tool.

 

Reasons Behind the Database Logical Consistency based I/O Error:

  • Sudden system shutdown/crash or constrained shutdown
  • SQL administrator attempts to query or change SQL data

At the point when a SQL Server database encounters a logical consistency-based error, the initial step is to detect the error.

The accompanying strategies can help in detecting the error:

  • Use Best Practice Analyzer (BPA) tool – BPA tool helps in identifying basic consistency errors. This tool is explicit to the SQL-variant, which means BPA 2008 is accessible with SQL 2008, etc.
  • Check Windows System at Event Log framework level, driver or disk-related errors
  • Check file framework respectability by running chkdsk order
  • Run the diagnostics suggested by hardware producers for the framework or disk
  • For SQL Server 2008, run the utility SQLIOSim on the drive of the database that has announced I/O error.
  • Contact hardware seller or device manufacturer to check that hardware prerequisites affirm with I/O necessities of the SQL server.

Manual Ways to Tackle SQL Server Detected A Logical Consistency-Based I/O Error

Technique 1 – Check hardware and application network

Database irregularity error can be fixed by building up an appropriate network between the database and the SQL application.

Technique 2 – Restore from SQL backup

The most attainable solution is to utilize backup for SQL database reclamation. Before restoring from backup, ensure that:

  • You have an ongoing full backup
  • Backup is updated, only before corruption, and not very old in order to maintain a strategic distance from basic data loss.
  • Corruption is at the page level, as page-level corruption issues can be settled with the assistance of page-level restoration.

Step 1: Use the accompanying order for restore SQL database from a full backup

 

Backup the transaction log

Backup LOG PageLevelRestores TO

Plate = ‘g:PageLevelRestores_LOG1.bak’

WITH INIT

GO

Step 2: Perform the restoration change to mirror the progressions online.

Backup the tail of the log…

Backup LOG PageLevelRestores TO

Circle = ‘g:PageLevelRestores_LOG_TAIL.bak’

WITH INIT

GO

Note: There is no compelling reason to restore the full SQL server database if corruption is bound to a single page. You can restore the database from the accessible backup for that page which is undermined. Performing accompanying orders will help in restoring the backup for a solitary page:

Restore all accessible log backups in the right request

Restore LOG PageLevelRestores FROM

Circle = ‘g:PageLevelRestores_LOG1.bak’

WITH NORECOVERY

GO

Finally, Restore the tail log backup

 

Restore LOG PageLevelRestores FROM

Circle = ‘g:PageLevelRestores_LOG_TAIL.bak’

WITH NORECOVERY

GO

Finally, get done with the Restore sequence

Restore DATABASE PageLevelRestores WITH RECOVERY

GO

When the database backup has restored the SQL database, run the query DBCC CHECKDB again to watch that the select proclamation prevails without SQL Database logical consistency-based I/O error. This order additionally watches that there is no data loss in this table.

Limitations of SQL database backup:

 

  1. It is preposterous to expect to Restore from SQL database backup when the accessible backup is out of date.
  2. On the off chance that Logical consistency-based I/O error is spread over the SQL server database, at that point this strategy won’t be legitimate.
  3. For those situations where the defective page exists in a non-clustered index, the SQL database can be fixed by dropping and recreating the index of the SQL database.

Technique 3: Repair corrupt SQL database with REPAIR_ALLOW_DATA_LOSS

REPAIR_ALLOW_DATA_LOSS is the base fixed level for the analyzed errors.

Notes: Before utilizing REPAIR_ALLOW_DATA_LOSS, perform the accompanying:

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 accompanying commands

Proclaim @COUNT INT

Proclaim @SQL VARCHAR(2000)

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

Proclaim TINFO_CUR CURSOR FOR

SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES

WHERE TABLE_TYPE=’BASE TABLE’

OPEN TINFO_CUR

Get NEXT FROM TINFO_CUR INTO @T_Name

WHILE @@FETCH_STATUS =0

Start

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

EXECUTE (@SQL)

Get NEXT FROM TINFO_CUR INTO @T_Name

END

CLOSE TINFO_CUR

DEALLOCATE TINFO_CUR

SELECT * FROM #T_Info ORDER BY T_NAME

The accompanying advances help to fix SQL database and resolve coherent consistency-based I/O error:

Run the command:

DBCC CHECKDB (DB_NAME, REPAIR_ALLOW_DATA_LOSS)

Once the SQL database is fixed, rehash the procedure of ‘Table Record Count’ and contrast with old record count.

There must not be any distinction between the initial and last Table record count.

Constraints:

REPAIR_ALLOW_DATA_LOSS may fix Database logical consistency-based IO error yet there is a significant issue of data loss, where a connection may encounter loss of basic data.

Elective Solution

If the above techniques won’t work, at that point, check out to a SysTools SQL File Repair tool . This tool fixes corrupt MDF and NDF files and Restore all the database objects. Moreover, this tool helps to recover all database objects such as Tables, Triggers, Indexes, Keys, Rules, deleted files, and Stored Procedures. It is of incredible assistance in the midst of an emergency, as the product gives an unmistakable repair solution and supports all SQL database versions.

Conclusion

Also Read: How to check if SQL Database Is Corrupted 

In this post, we have featured the reason for SQL Server Detected A Logical Consistency-Based I/O Error and the techniques to resolve this issue.

In view of the error, we have attempted to locate the right technique. On the off chance that hardware or framework is liable for the error, it is prescribed to determine the hardware related issues, and in the event that DBCC CHECKDB reports consistency error, at that point attempt to Restore SQL database by utilizing a refreshed backup.

If the issue isn’t fixed with hardware and backup, at that point attempt to fix the database with the assistance of REPAIR_ALLOW_DATA_LOSS. This is the base degree of fix to determine all errors from CHECKDB, yet this doesn’t imply that it will really fix the error. Besides, it might bring about data loss.

 

About Jackson Andrew

I am SQL Server database administrator having 9+ years of Experience in SQL Database management. I love to share my knowledge of SQL server related problems.

Leave a Reply

Your email address will not be published. Required fields are marked *