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:
- It is preposterous to expect to Restore from SQL database backup when the accessible backup is out of date.
- 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.
- 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.