SQL Server detected a logical consistency-based I/O error: incorrect pageid in BizTalkMsgBoxDb database.

Nice way to start the day: “SQL Server detected a logical consistency-based I/O error.” For some reason, maybe due to a sudden computer shutdown/crash or a forced shutdown, one of my client’s 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 Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\BizTalkMsgBoxDb.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 errors 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 executed the above command, I got more details about 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, this type of error is usually related to I/O/Hardware issues, and it may occur due to a 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 wasn’t really important.

The good news was that after I ran all these steps, the SQL Server detected a logical consistency-based I/O error that stopped appearing in Event Viewer, and the environment became stable and working properly again.

Hope you find this helpful! If you liked the content or found it useful and would like to support me in writing more, consider buying (or helping to buy) a Star Wars Lego set for my son. 

Author: Sandro Pereira

Sandro Pereira lives in Portugal and works as a consultant at DevScope. In the past years, he has been working on implementing Integration scenarios both on-premises and cloud for various clients, each with different scenarios from a technical point of view, size, and criticality, using Microsoft Azure, Microsoft BizTalk Server and different technologies like AS2, EDI, RosettaNet, SAP, TIBCO etc. He is a regular blogger, international speaker, and technical reviewer of several BizTalk books all focused on Integration. He is also the author of the book “BizTalk Mapping Patterns & Best Practices”. He has been awarded MVP since 2011 for his contributions to the integration community. View all posts by Sandro Pereira