SQL Server Error 823 alert

Question

We started getting below SQL Server 823 Error alerts on 3 databases.

SQL Server Alert System: 'Error825' occurred on

A read of the file 'c:\mdf\SAmpleDB.mdf' at offset 0x00000728018000 succeeded after failing 1 time(s) with error: 23(Data error (cyclic redundancy check).). Additional messages in the SQL Server error log and operating system error log may provide more detail. This error condition threatens database integrity and must be corrected. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.

Our Full Backups & Diff backups are also failing with 'Msg 3203, Level 16, State 1, Line 1. Read on 'D:\mdf\SAmpleDB.mdf failed: 23(Data error (cyclic redundancy check)

Msg 2013, level 16, state 1. BACKUP Database is terminating abnormally

We can see The device, \Device\Harddisk0\DR0, has a bad block error in eventvwr

However, DBCC CheckDB is succeeding.

These 3 databases are huge & are actually Publication databases in Transaction Replication setup.

Is there any way to fix this issue & keep DBs healthy without impacting Transaction Replication setup

Answer 1

Apparently, there are issues with your I/O subsystem. You need to resolve these before you start thinking about Transactional replication. Since DBCC comes out clean, there is some hope. But it is bad that backups are failing, since you most likely need to move the database to new hardware. Then again, that depends on what those disk actually are. Locally attached disk? Disks on a SAN? Virtual disks on a VM host.

The best is if you have a clean backup of this database before this started to fail, and then can apply log backups. Do this on a different server. Take the applications that are running against these databases offline, as keeping them running could be a gamble. You could lose data, if the database becomes corrupt.