Recover the currupted table data from database

 Hello Friends,

 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.

Start receive error while run select query on some specific record

SQL Server detected a logical consistency-based I/O error: incorrect pageid (expected 1:19473258; actual 1:5603466).

It occurred during a read of page (1:19473258) in database ID 7 at offset 0x000025246d4000 in file 'F:\Data\xxxxx_nav2016_Live_Data.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.

 

While in NAV you will get this error for table have issue

There are few page corrupt in database for table

To solve this issue we can run below steps

https://blog.sandro-pereira.com/2017/07/25/sql-server-detected-a-logical-consistency-based-biztalkmsgboxdb-database/

 Set the database to Single User

You can do using query OR manual from database property

 

ALTER DATABASE [DB_NAME]

SET SINGLE_USER;

GO

 

Or we can do it from UI

Right Click On Database à Properties à Options à Restrict Access à Select Single User

Run the below Query for Table where Record is corrupted

DBCC CHECKTABLE([Table Name], REPAIR_ALLOW_DATA_LOSS)

GO

 

Note: Above command run with data loss, so make sure that you take backup before run this command and system will delete the records which page is corrupted and system can’t able to read that data

Once this command run you can able to run query properly but few records deleted which is  corrupted – so you need to get it back from old database backup

 

We found the missing record and insert into new table (using insert into [new table name])

After we generate the insert record query from temp table using Generate Script Open from new table (Only Data)

Run this query to create record

 Run the below Query to set database as multiple user

 

ALTER DATABASE BizTalkMsgBoxDb

SET MULTI_USER;

GO

 

Or same way you can do it from UI

======

SELECT * FROM [msdb].[dbo].[suspect_pages]

DBCC TRACEON (3604);

DBCC PAGE (7, 1, 23773535, 0);

DBCC TRACEOFF (3604);

GO

SELECT OBJECT_NAME (808167912);

=============

Run Check bd command to found error in database page

DBCC CHECKDB

This will take 1 or 2 hours for around 200 GB database

So if any error found at last then repair that page or table

==============

Done....

System will recover the old data or it might possible that curreupted raw can be deleted from database

Thank you for reading...

I hope this help to someone in future...

Keep Sharing....Keep Growing....