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’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 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 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:

#1 Azure Monitoring Platform
 DBCC CHECKDB (BizTalkMsgBoxDb) WITH NO_INFOMSGS, ALL_ERRORMSGS
 

When I execute the above command, I got more detail 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, these type of error is usually related to the IO/Hardware issues and it may occur due to sudden computer shutdown/crash or 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.

#1 Azure Monitoring Platform
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.

8 thoughts on “SQL Server detected a logical consistency-based I/O error: incorrect pageid in BizTalkMsgBoxDb database.”

  1. thanks for this tip mate. I was having trouble running basic queries on 1 of our client’s tables.

    We’ll take whatever data we can and move forward!

Leave a Reply

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

turbo360

Back to Top