Sql-server – How to correct SQL Server Error 8928

corruptiondbcc-checkdbsql server

I received the following error message in an application running against one of my databases:

SQL Server detected a logical consistency-based I/O error: incorrect checksum 
(expected: 0xfdff74c9; actual: 0xfdff74cb). It occurred during a read of page (1:69965) in 
database ID 5 at offset 0x0000002229a000 in file 'e:\SQL Server\MSSQL.1\MSSQL
\[DB_Name].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.

That sounded bad so I ran the following:

DBCC CHECKDB ([DB_Name]) WITH NO_INFOMSGS, ALL_ERRORMSGS

And received the following report:

Msg 8928, Level 16, State 1, Line 1
Object ID 1326627769, index ID 1, partition ID 72057594048872448, alloc unit ID 
    72057594055557120 (type LOB data): Page (1:69965) could not be processed. See other 
    errors for details.
Msg 8939, Level 16, State 98, Line 1
Table error: Object ID 1326627769, index ID 1, partition ID 72057594048872448, alloc 
    unit ID 72057594055557120 (type LOB data), page (1:69965). Test (IS_OFF (BUF_IOERR, 
    pBUF->bstat)) failed. Values are 12716041 and -4.
Msg 8965, Level 16, State 1, Line 1
Table error: Object ID 1326627769, index ID 1, partition ID 72057594048872448, alloc 
    unit ID 72057594055557120 (type LOB data). The off-row data node at page (1:69965), 
    slot 0, text ID 89622642688 is referenced by page (1:69968), slot 1, but was not 
    seen in the scan.
Msg 8929, Level 16, State 1, Line 1
Object ID 1326627769, index ID 1, partition ID 72057594048872448, alloc unit ID 
    72057594055622656 (type In-row data): Errors found in off-row data with ID 
    89622642688 owned by data record identified by RID = (1:77754:1)
CHECKDB found 0 allocation errors and 4 consistency errors in table '[Table_Name]' 
    (object ID 1326627769).
CHECKDB found 0 allocation errors and 4 consistency errors in database '[DB_Name]'.
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB 
    ([DB_Name]).

I looked through my old images, and it appears the error has existed for quite a while. I don't have a clean image. I'm not much of a DBA so I don't know the best way to proceed; I don't even understand how the error messages can point me to the data corruption.

Can anyone tell me what these messages indicate and how to proceed?

Best Answer

Msg 8928, Level 16, State 1, Line 1 Object ID 1326627769, index ID 1, partition ID 72057594048872448, alloc unit ID 72057594055557120 (type LOB data): Page (1:69965)

There is corruption in your SQL Server database column is LOB and may have VRCHAR datatype. Index ID 1 indicated problem with Clustered index so I guess minimum repiar options repair allow data loss might delete data to recover database. Dropping and recreating index will not help.Please dont run repair first. Do you have latest valid backup of SQL server database ? Best possible solution here is to restore from valid backup.

How big is your database. Please run restore verifyonly before restoring the backup to check that backup you are restoring is consistent. Although restore only will guarantee that backup is complete and valid in all format.

Please read SQL Server errorlog and windows event viewer to find out reason why corruption happened and then take steps to fix it.

if you dont have valid backup you might as well try to repair the database using below command

ALTER DATABASE DB_NAME SET SINGLE_USER; 
GO 
DBCC CHECKDB (N'DB_NAME', REPAIR_ALLOW_DATA_LOSS) WITH ALL_ERRORMSGS, NO_INFOMSGS;
GO
Related Question