5 Don’ts When Corruption is Detected in SQL Server Database

The biggest nightmare for a DBA is to come across a corrupted database. Often database corruption happens due to power failure, causing the server and databases to shut down abruptly in the middle of processing data. Also, I/O subsystem failure or a software bug can turn the database corrupt.

Database corruption if not handled carefully and on time can incur much more downtime and data loss. So, it's easy to freak out and make some chaotic decisions which could do more harm than good. For instance, attempting to repair a corrupt database using DBCC CHECKDB with REPAIR_ALLOW_DATA_LOSS command without understanding what it actually does, you can end up losing massive data.

This article discusses what you should not do when database corruption is detected.

5 Don’ts When Database Corruption is Detected

1. Find the Root Cause

We instinctively panic when we find that there are issues in a database. But it’s important to stay calm and carefully plan the course of action to resolve the problem. The first step should be to check SQL Server Error Log and Windows Event Log for more details on the error. If it occurred due to hardware malfunction, you can reach out to the hardware manufacturer or vendor to get it fixed.?

2. Don’t Restart the Server

Another common instinct is to restart something when it doesn’t work - be it our PC, mobile phone, or network. But applying this hack won’t work on a corrupted SQL database. Restarting your server cannot repair corruption in the database. In fact, it will only cause delay in database crash recovery.

Also, in some systems, you may not be able to restart the server straight away which can cause further delay in troubleshooting the problem.

3. Don’t Detach the Database

SQL Server often reports corruption when there are damaged pages in the database. However, the server won’t be able to see those damaged pages if the corrupted database is detached. Detaching the database will only cause you more problems, as you won’t be able to attach it again on the secondary server. In this case, you can try to hack-attach the damaged database as explained in this blog by Paul Randal. In the blog, Paul recommends a set of steps following which can trick SQL Server into thinking that the database is attached.

4. Don’t Upgrade the Server

Are you among those users who think that database corruption may go away by upgrading to the latest version of SQL Server? Then you are wrong.

Upgrading your SQL Server version can provide better features but it can’t help you get rid of the corruption problem. However, applying SQL Server Cumulative Updates can often fix bugs that might have caused database corruption.?

5. Don’t Run DBCC CHECKDB First

For the consistency errors found in the database, you may be required to run repair_allow_data_loss as the minimum level of repair to resolve all the errors. But remember, running DBCC CHECKDB with a repair option doesn’t guarantee to fix the database corruption errors. Typically, executing the CHECKDB command with repair_allow_data_loss can lead to data loss.

So, ensure you have tried other alternatives to repair corruption first. You can try using a specialized SQL recovery tool, like Stellar Repair for MS SQL, to fix the corrupted database. It is purpose-built to repair a severely corrupted SQL database and helps recover all the database objects, preserving data integrity.

Click here to read the MVP review of Stellar Repair for MS SQL software.

Wrapping Up

When corruption is detected in a database, it’s essential to keep calm and avoid anything that makes the matter worse. This includes avoiding server restart, detaching the database, running CHECKDB, etc.

Once you know what will not help resolve the database corruption issue, ensure that you are prepared to handle the issue. For this, you should schedule routine maintenance tasks and automatic backups, and test the database backups to ensure they can be used to restore the database when needed. ?

Even if you are able to resolve the database corruption issue, your work isn’t finished. Remember what happens once can happen again. And so, it’s important to take the measures that prevent the issue from occurring again. The preventive measures are as follows:

• Perform root cause analysis – A database can again get damaged if the root cause behind the problem cannot be found. If you suspect hardware failure to be reasons behind corruption, you can run memory and disk diagnostics to ensure there are no issues with the underlying hardware. Also, you can contact your vendors to see if they had found any problem with the firmware.
• Open a ticket with Microsoft – Reach out to the Microsoft support team. Open a ticket with Microsoft to get expert assistance to solve the problem.?