SQL Server database corruption issue

Question

a production database is corrupted which is confirmed by running the "dbcc checkdb" command. This started last Wed night which required me to restore the database on sunday night from the most recent clean .bak file and susequent .trn log files. I ran a manual backup after the restoration successfully as well as index rebuild and backup using normal SQL maintenance plan commands. During monday the restored database was used as normal and then the evening maintenance plan had corruption.

How do I figured out the core reason why the corruption is happening?

Answer 1

Corruption is always due to issues outside SQL Server. That is, hardware errors or errors somewhere in the I/O subsystem. The root cause could be a bad memory stick, but that often manifests itself in unexpected crashes as well. The most likely culprit is the I/O subsystem. Which in today's virtualised world is very complex with many components both in hardware and in software.

Tracking down the exact component can be very tedious, since you basically have to replace them one by one. And then run long enough to feel sure that corruption is not occurring anymore.

The alternative is to move to new hardware directly. It will be a discussion you will have to make with your infrastructure folks.

Staying in production with what you have now is not really an option.

Answer 2

Hi @Lincoln Brill

Welcome to Microsoft Q&A Forum.

The recurring corruption indicates an underlying issue with the I/O subsystem or hardware. To identify the root cause, start by running DBCC CHECKDB to determine which pages are affected. Then, review the SQL Server Error Logs and Windows Event Logs for I/O-related errors specifically error codes 823, 824, or 825, which signal problems with reading or writing to disk.  

Next, run comprehensive hardware diagnostics on the server, including memory tests and disk health checks (e.g., SMART status). To simulate SQL Server I/O patterns and stress-test the storage system, use the SQLIOSim utility, which can help reproduce potential hardware faults under load.  

If the corruption does not reoccur when the same database is restored and used on a different server or storage system, this strongly suggests that the original hardware stack is at fault. In such cases, the long-term resolution is to repair or replace the failing component whether it's a disk, controller, memory module, or virtualized storage layer or to migrate the database to a more reliable infrastructure.

I hope this information is helpful. If you have any further questions, please let us know. we can assist you further.

 

Warm Regards.