Sql-server – SQL Server 2008 can’t repair consistency

sql serversql-server-2008

I have a problem with a SQL Server 2008 database.

Launching

DBCC CHECKDB

I get this error:

SQL Server detected a logical consistency-based I/O error: incorrect
checksum (expected: 0xd2e00940; actual: 0x925ef494). It occurred
during a read of page (1:15215) in database ID 22 at offset
0x000000076de000 in file 'C:\Program Files\Microsoft SQL
Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\file.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.

I found the table causing the problem:

DBCC CHECKTABLE

Msg 824, Level 24, State 2, Line 8
SQL Server detected a logical
consistency-based I/O error: incorrect checksum (expected: 0xd2e00940;
actual: 0x925ef494). It occurred during a read of page (1:15215) in
database ID 22 at offset 0x000000076de000 in file 'C:\Program
Files\Microsoft SQL
Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\file.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.

So I tried with the repair operations:

DBCC CHECKTABLE (table_name, REPAIR_ALLOW_DATA_LOSS)

but I get the same error:

Msg 824, Level 24, State 2, Line 8
SQL Server detected a logical
consistency-based I/O error: incorrect checksum (expected: 0xd2e00940;
actual: 0x925ef494). It occurred during a read of page (1:15215) in
database ID 22 at offset 0x000000076de000 in file 'C:\Program
Files\Microsoft SQL
Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\file.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.

I've also tried setting the DB in SINGLE_USER mode, but with no result.

I am not able to delete nor truncate the table. As I always get the same error.

The table does not have any constraints. It has one PK and one Index, but I can't drop any of them.

Best Answer

You need to restore the page from a full backup, then applying the logs forward to current or you need to do a full restore.

I'd also run chkdsk as it appears you might be having a disk problem as the same page is reporting problems when attempting to repair it. Also run any disk management tools and see if they report problems with the disk.

Related Question