This post was originally published here
Nice way to start the day: “SQL Server detected a logical consistency-based I/O error’s“… For some reason, maybe due to sudden computer shutdown/crash or a forced shutdown, one of my client BizTalk DEV virtual machines presented strange behaviors this morning. The Host Instances were always restarting for no apparent reason. When I started to diagnose the problem, and inspect the machine Event Viewer I found the following error:
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 ServerMSSQL13.MSSQLSERVERMSSQLDATABizTalkMsgBoxDb.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.
These type of error is usually related to the IO/Hardware issues and as the error mention you should check and run the DBCC CHECKDB in SQL Server:
DBCC CHECKDB (BizTalkMsgBoxDb) WITH NO_INFOMSGS, ALL_ERRORMSGS
When I execute the above command, I got more detail of the problems that were happening:
Msg 8909, 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:1856) contains an incorrect page ID in its page header. The PageId in the page header = (0:0).
Msg 8909, 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:1848) contains an incorrect page ID in its page header. The PageId in the page header = (0:0).
CHECKDB found 0 allocation errors and 2 consistency errors not associated with any single object.
Msg 8928, Level 16, State 1, Line 1
Object ID 544720993, index ID 1, partition ID 72057594059227136, alloc unit ID 72057594069385216 (type In-row data): Page (1:1856) could not be processed. See other errors for details.
Msg 8980, Level 16, State 1, Line 1
Table error: Object ID 544720993, index ID 1, partition ID 72057594059227136, alloc unit ID 72057594069385216 (type In-row data). Index node page (0:0), slot 0 refers to child page (1:1856) and previous child (0:0), but they were not encountered.
CHECKDB found 0 allocation errors and 2 consistency errors in table ‘BizTalkServerSendHost_DequeueBatches’ (object ID 544720993).
Msg 8928, Level 16, State 1, Line 1
Object ID 1437248175, index ID 1, partition ID 72057594061717504, alloc unit ID 72057594072137728 (type In-row data): Page (1:1848) could not be processed. See other errors for details.
Msg 8980, Level 16, State 1, Line 1
Table error: Object ID 1437248175, index ID 1, partition ID 72057594061717504, alloc unit ID 72057594072137728 (type In-row data). Index node page (0:0), slot 0 refers to child page (1:1848) and previous child (0:0), but they were not encountered.
CHECKDB found 0 allocation errors and 2 consistency errors in table ‘BizTalkServerTrackingHost_DequeueBatches’ (object ID 1437248175).
CHECKDB found 0 allocation errors and 6 consistency errors in database ‘BizTalkMsgBoxDb’.
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (BizTalkMsgBoxDb).
CAUSE
Again, these type of error is usually related to the IO/Hardware issues and it may occur due to sudden computer shutdown/crash or a forced shutdown of the machine that for some reason corrupted the files,
Solution
Because I already had almost all the settings/configurations/optimizations of my developer environment done and did not want to re-install them again, like SQL Server optimizations, Jobs, host and host instances and so on, to solve the SQL Server detected a logical consistency-based I/O error I had to:
- Set the ‘BizTalkMsgBoxDb’ database to be in single user mode.
ALTER DATABASE BizTalkMsgBoxDb SET SINGLE_USER; GO
- Try to repair the errors that were found in both tables: ‘BizTalkServerSendHost_DequeueBatches’ and ‘BizTalkServerTrackingHost_DequeueBatches’
USE BizTalkMsgBoxDb;
GO
DBCC CHECKTABLE('BizTalkServerSendHost_DequeueBatches', REPAIR_ALLOW_DATA_LOSS)
GO
DBCC CHECKTABLE('BizTalkServerTrackingHost_DequeueBatches', REPAIR_ALLOW_DATA_LOSS)
GO
ALTER DATABASE BizTalkMsgBoxDb
SET MULTI_USER;
GO
- Try to repair the errors that were found in both tables: ‘BizTalkServerSendHost_DequeueBatches’ and ‘BizTalkServerTrackingHost_DequeueBatches’
Because you change ‘BizTalkMsgBoxDb’ database to be in single user mode and then back to multi user if we don’t force a full backup the Backup job will start to fail with the message:
- [SQLSTATE 01000] (Message 4035) BACKUP LOG cannot be performed because there is no current database backup. [SQLSTATE 42000] (Error 4214)
So, to avoid this we need to force a BizTalk full backup by calling the “BizTalkMgmtDb.dbo.sp_ForceFullBackup” stored procedure
This way may not be the correct or perfect solution because If successful, the REPAIR_ALLOW_DATA_LOSS option may result in some data loss. In fact, it may result in more data lost than if a user were to restore the database from the last known good backup. The problem was that I didn’t have a last known good backup and in fact, this was a dev environment, so losing data was not really important.
The good news was that after I run all these steps, all the SQL Server detected a logical consistency-based I/O error’s stop appearing in Event Viewer the environment became stable and working properly again.
