{"id":17549,"date":"2020-07-27T11:30:14","date_gmt":"2020-07-27T11:30:14","guid":{"rendered":"https:\/\/ittutorial.org\/?p=17549"},"modified":"2020-07-27T11:35:47","modified_gmt":"2020-07-27T11:35:47","slug":"fix-sql-server-detected-a-logical-consistency-based-i-o-error","status":"publish","type":"post","link":"https:\/\/ittutorial.org\/fix-sql-server-detected-a-logical-consistency-based-i-o-error\/","title":{"rendered":"Ways to Fix SQL Server Detected A Logical Consistency-Based I\/O Error"},"content":{"rendered":"<p><strong>Summary:<\/strong> 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 <a href=\"https:\/\/www.emaildoctor.org\/recover\/sql-database.html\">Recover SQL Server Database<\/a> Tool.<\/p>\n<p><a href=\"https:\/\/systoolskart.com\/download\/SYS1S2Q7L\/10\"><img loading=\"lazy\" decoding=\"async\" class=\"size-medium wp-image-16668 aligncenter\" src=\"https:\/\/ittutorial.org\/wp-content\/uploads\/2020\/06\/download-300x104.jpg\" alt=\"\" width=\"300\" height=\"104\" srcset=\"https:\/\/ittutorial.org\/wp-content\/uploads\/2020\/06\/download-300x104.jpg 300w, https:\/\/ittutorial.org\/wp-content\/uploads\/2020\/06\/download.jpg 381w\" sizes=\"auto, (max-width: 300px) 100vw, 300px\" \/><\/a><\/p>\n<p>&nbsp;<\/p>\n<h2><strong>Reasons Behind the Database Logical Consistency based I\/O Error:<\/strong><\/h2>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter wp-image-17552 size-full\" src=\"https:\/\/ittutorial.org\/wp-content\/uploads\/2020\/07\/logical-consistency-based-io-error-torn-page.jpg\" alt=\"\" width=\"500\" height=\"253\" srcset=\"https:\/\/ittutorial.org\/wp-content\/uploads\/2020\/07\/logical-consistency-based-io-error-torn-page.jpg 500w, https:\/\/ittutorial.org\/wp-content\/uploads\/2020\/07\/logical-consistency-based-io-error-torn-page-300x152.jpg 300w\" sizes=\"auto, (max-width: 500px) 100vw, 500px\" \/><\/p>\n<ul>\n<li>Sudden system shutdown\/crash or constrained shutdown<\/li>\n<li>SQL administrator attempts to query or change SQL data<\/li>\n<\/ul>\n<p>At the point when a SQL Server database encounters a logical consistency-based error, the initial step is to detect the error.<\/p>\n<p>The accompanying strategies can help in detecting the error:<\/p>\n<ul>\n<li>Use Best Practice Analyzer (BPA) tool \u2013 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.<\/li>\n<li>Check Windows System at Event Log framework level, driver or disk-related errors<\/li>\n<li>Check file framework respectability by running chkdsk order<\/li>\n<li>Run the diagnostics suggested by hardware producers for the framework or disk<\/li>\n<li>For SQL Server 2008, run the utility SQLIOSim on the drive of the database that has announced I\/O error.<\/li>\n<li>Contact hardware seller or device manufacturer to check that hardware prerequisites affirm with I\/O necessities of the SQL server.<\/li>\n<\/ul>\n<h2><strong>Manual Ways to Tackle SQL Server Detected A Logical Consistency-Based I\/O Error<\/strong><\/h2>\n<p><strong>Technique 1 \u2013 Check hardware and application network<\/strong><\/p>\n<p>Database irregularity error can be fixed by building up an appropriate network between the database and the SQL application.<\/p>\n<p><strong>Technique 2 &#8211; Restore from SQL backup <\/strong><\/p>\n<p>The most attainable solution is to utilize backup for SQL database reclamation. Before restoring from backup, ensure that:<\/p>\n<ul>\n<li>You have an ongoing full backup<\/li>\n<li>Backup is updated, only before corruption, and not very old in order to maintain a strategic distance from basic data loss.<\/li>\n<li>Corruption is at the page level, as page-level corruption issues can be settled with the assistance of page-level restoration.<\/li>\n<\/ul>\n<p><strong>Step 1:<\/strong> Use the accompanying order for restore SQL database from a full backup<\/p>\n<p>&nbsp;<\/p>\n<p><strong><em>Backup the transaction log <\/em><\/strong><\/p>\n<p><strong><em>Backup LOG PageLevelRestores TO <\/em><\/strong><\/p>\n<p><strong><em>Plate = &#8216;g:PageLevelRestores_LOG1.bak&#8217; <\/em><\/strong><\/p>\n<p><strong><em>WITH INIT <\/em><\/strong><\/p>\n<p><strong><em>GO <\/em><\/strong><\/p>\n<p><strong>Step 2:<\/strong> Perform the restoration change to mirror the progressions online.<\/p>\n<p><strong><em>Backup the tail of the log&#8230; <\/em><\/strong><\/p>\n<p><strong><em>Backup LOG PageLevelRestores TO <\/em><\/strong><\/p>\n<p><strong><em>Circle = &#8216;g:PageLevelRestores_LOG_TAIL.bak&#8217; <\/em><\/strong><\/p>\n<p><strong><em>WITH INIT <\/em><\/strong><\/p>\n<p><strong><em>GO <\/em><\/strong><\/p>\n<p>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:<\/p>\n<p><strong><em>Restore all accessible log backups in the right request <\/em><\/strong><\/p>\n<p><strong><em>Restore LOG PageLevelRestores FROM <\/em><\/strong><\/p>\n<p><strong><em>Circle = &#8216;g:PageLevelRestores_LOG1.bak&#8217; <\/em><\/strong><\/p>\n<p><strong><em>WITH NORECOVERY <\/em><\/strong><\/p>\n<p><strong><em>GO <\/em><\/strong><\/p>\n<p>Finally, Restore the tail log backup<\/p>\n<p>&nbsp;<\/p>\n<p><strong><em>Restore LOG PageLevelRestores FROM <\/em><\/strong><\/p>\n<p><strong><em>Circle = &#8216;g:PageLevelRestores_LOG_TAIL.bak&#8217; <\/em><\/strong><\/p>\n<p><strong><em>WITH NORECOVERY <\/em><\/strong><\/p>\n<p><strong><em>GO <\/em><\/strong><\/p>\n<p>Finally, get done with the Restore sequence<\/p>\n<p><strong><em>Restore DATABASE PageLevelRestores WITH RECOVERY <\/em><\/strong><\/p>\n<p><strong><em>GO <\/em><\/strong><\/p>\n<p>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.<\/p>\n<p><strong>Limitations of SQL database backup: <\/strong><\/p>\n<p>&nbsp;<\/p>\n<ol>\n<li>It is preposterous to expect to Restore from SQL database backup when the accessible backup is out of date.<\/li>\n<li>On the off chance that Logical consistency-based I\/O error is spread over the SQL server database, at that point this strategy won&#8217;t be legitimate.<\/li>\n<li>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.<\/li>\n<\/ol>\n<p><strong>Technique 3: Repair corrupt SQL database with REPAIR_ALLOW_DATA_LOSS<\/strong><\/p>\n<p>REPAIR_ALLOW_DATA_LOSS is the base fixed level for the analyzed errors.<\/p>\n<p>Notes: Before utilizing REPAIR_ALLOW_DATA_LOSS, perform the accompanying:<\/p>\n<p>Take a backup of SQL server database and save it with another name<\/p>\n<p>Set SQL database in Single user mode<\/p>\n<p>Get all Tables record count by using the accompanying commands<\/p>\n<p><strong><em>Proclaim @COUNT INT <\/em><\/strong><\/p>\n<p><strong><em>Proclaim @SQL VARCHAR(2000) <\/em><\/strong><\/p>\n<p><strong><em>Make TABLE #T_Info(ID INT IDENTITY(1,1),T_Name VARCHAR(200),D_Count INT) <\/em><\/strong><\/p>\n<p><strong><em>Proclaim TINFO_CUR CURSOR FOR <\/em><\/strong><\/p>\n<p><strong><em>SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES <\/em><\/strong><\/p>\n<p><strong><em>WHERE TABLE_TYPE=&#8217;BASE TABLE&#8217; <\/em><\/strong><\/p>\n<p><strong><em>OPEN TINFO_CUR <\/em><\/strong><\/p>\n<p><strong><em>Get NEXT FROM TINFO_CUR INTO @T_Name <\/em><\/strong><\/p>\n<p><strong><em>WHILE @@FETCH_STATUS =0 <\/em><\/strong><\/p>\n<p><strong><em>Start <\/em><\/strong><\/p>\n<p><strong><em>SET @SQL=&#8217;INSERT INTO #T_Info(T_Name,D_Count) SELECT &#8221;&#8217;+@T_Name+&#8221;&#8217;,COUNT(*) FROM &#8216;+@T_Name+&#8221; <\/em><\/strong><\/p>\n<p><strong><em>EXECUTE (@SQL) <\/em><\/strong><\/p>\n<p><strong><em>Get NEXT FROM TINFO_CUR INTO @T_Name <\/em><\/strong><\/p>\n<p><strong><em>END <\/em><\/strong><\/p>\n<p><strong><em>CLOSE TINFO_CUR <\/em><\/strong><\/p>\n<p><strong><em>DEALLOCATE TINFO_CUR <\/em><\/strong><\/p>\n<p><strong><em>SELECT * FROM #T_Info ORDER BY T_NAME <\/em><\/strong><\/p>\n<p>The accompanying advances help to fix SQL database and resolve coherent consistency-based I\/O error:<\/p>\n<p>Run the command:<\/p>\n<p><strong>DBCC CHECKDB (DB_NAME, REPAIR_ALLOW_DATA_LOSS) <\/strong><\/p>\n<p>Once the SQL database is fixed, rehash the procedure of &#8216;Table Record Count&#8217; and contrast with old record count.<\/p>\n<p>There must not be any distinction between the initial and last Table record count.<\/p>\n<p><strong>Constraints<\/strong>:<\/p>\n<p>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.<\/p>\n<h3><strong>Elective Solution <\/strong><\/h3>\n<p>If the above techniques won&#8217;t work, at that point, check out to a <strong>SysTools <a href=\"https:\/\/www.systoolsgroup.com\/sql-recovery.html\">SQL File Repair tool<\/a> .<\/strong> 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.<\/p>\n<p><a href=\"https:\/\/systoolskart.com\/download\/SYS1S2Q7L\/10\"><img loading=\"lazy\" decoding=\"async\" class=\"size-medium wp-image-15468 aligncenter\" src=\"https:\/\/ittutorial.org\/wp-content\/uploads\/2020\/05\/download-300x104.jpg\" alt=\"\" width=\"300\" height=\"104\" srcset=\"https:\/\/ittutorial.org\/wp-content\/uploads\/2020\/05\/download-300x104.jpg 300w, https:\/\/ittutorial.org\/wp-content\/uploads\/2020\/05\/download.jpg 381w\" sizes=\"auto, (max-width: 300px) 100vw, 300px\" \/><\/a><\/p>\n<h3><strong>Conclusion<\/strong><\/h3>\n<p><strong>Also Read:<\/strong> How to check if <a href=\"https:\/\/ittutorial.org\/how-to-check-if-sql-database-is-corrupted\/\">SQL Database Is Corrupted\u00a0<\/a><\/p>\n<p>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.<\/p>\n<p>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.<\/p>\n<p>If the issue isn&#8217;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&#8217;t imply that it will really fix the error. Besides, it might bring about data loss.<\/p>\n<p>&nbsp;<\/p>\n","protected":false},"excerpt":{"rendered":"<p>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 &hellip;<\/p>\n","protected":false},"author":10094,"featured_media":16903,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"om_disable_all_campaigns":false,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"_uf_show_specific_survey":0,"_uf_disable_surveys":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[2227,3827],"tags":[12175],"class_list":["post-17549","post","type-post","status-publish","format-standard","has-post-thumbnail","","category-sql-server-2","category-sql-server-dba-scripts","tag-sql-server-detected-a-logical-consistency-based-i-o-error"],"aioseo_notices":[],"jetpack_featured_media_url":"https:\/\/ittutorial.org\/wp-content\/uploads\/2020\/07\/sql-database-180x120-1.png","jetpack_sharing_enabled":true,"amp_enabled":true,"_links":{"self":[{"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/posts\/17549","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/users\/10094"}],"replies":[{"embeddable":true,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/comments?post=17549"}],"version-history":[{"count":3,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/posts\/17549\/revisions"}],"predecessor-version":[{"id":17553,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/posts\/17549\/revisions\/17553"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/media\/16903"}],"wp:attachment":[{"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/media?parent=17549"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/categories?post=17549"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/tags?post=17549"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}