Recently, we encountered an issue with a restored database stating the below error message:
Msg 824, Level 24, State 2, Line 28
SQL Server detected a logical consistency-based I/O error: incorrect checksum (expected: 0xafbb455a; actual: 0xaf9a455a).
It occurred during a read of page (1:310732) in database ID 161 at offset 0x00000097b98000 in file ‘F:\DATA1\database.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.
As the error message is pretty straight forward, a clear issue with some consistency (I/O error). And this has been even confirmed by running a DBCC CHECKDB. To resolve the issue, we decided to go with REPAIR_ALLOW_DATA_LOSS option as this is a lower environment issue. I personally do not recommend to use this option for production databases as there is a chance of data loss.
alter database <database> SET single_user with rollback immediate dbcc checkdb('<database>',REPAIR_ALLOW_DATA_LOSS) alter database <database> SET multi_user with rollback immediate
Before we do this option, just curious to understand the object being corrupted from the error message.
The below code will give you information on the objects. Please note the below is supported only from SQL 2012 and later versions. For earlier version, you need to use DBCC PAGE commands.
SELECT DB_NAME(susp.database_id) DatabaseName, OBJECT_SCHEMA_NAME(ind.object_id, ind.database_id) ObjectSchemaName, OBJECT_NAME(ind.object_id, ind.database_id) ObjectName FROM msdb.dbo.suspect_pages susp CROSS APPLY SYS.DM_DB_DATABASE_PAGE_ALLOCATIONS(susp.database_id,null,null,null,null) ind WHERE allocated_page_file_id = susp.file_id AND allocated_page_page_id = susp.page_id
Later, we identified the issue is associated with backup and we took a fresh backup of the database and restored. It worked like a treat!!!