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

by Sandro Pereira | Jul 25, 2017 | BizTalk Community Blogs via Syndication

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.

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