Menu Hide

DBCC CHECKDB reporting consistency errors, queries failing with high severity errors, and a backup job failing are some tell-tale signs of corruption in a SQL Server database. Also, SQL Server reporting errors, like 823, 824, 5172, etc. indicates database corruption. You cannot access the database and its data, unless the database is repaired. But before repairing a corrupted SQL Server database, it is important to understand the reasons that may cause corruption in SQL Server database. Also, you must know about the practices you should avoid when you detect corruption in the database.

Free Download Free Download 100% Secure


What Causes Corruption in SQL Database?

The most prominent causes of database corruption are as follows:

  • Power Failure: Often, corruption happens when there is a power failure and the server crashes, instead of shutting down gracefully, while processing the data.
  • I/O Subsystem Failure: As per online sources, in 99 percent of cases, corruption in a SQL database is caused due to problems with the I/O subsystem. Faulty disk drives, disk controllers, or SAN controllers can result in recurring corruption. 
  • SQL Server Bug: Oftentimes, bugs found in Microsoft SQL Server are known to cause corruption. For instance, online index rebuild in SQL Server 2014 and 2012 causes data corruption in clustered indexes. For more information, see KB2969896.
  • Malicious Attacks: Attacks on SQL Server by malicious agents or programs can infect database files and turn the database inaccessible or corrupt. 
Other reasons that could turn a SQL database corrupt include abrupt system termination, changes done in a SQL Server account, etc.
SQL Repair Services- Stellar


What Not to Do When You Encounter Corruption?

When you detect corruption in a SQL Server database, make sure to avoid the following:

  • Shutting Down the SQL Server: A database goes into recovery mode when it becomes corrupt. Shutting down the server may put the database in an inconsistent state while recovery is in progress. 
  • Restarting SQL Server: Do not restart SQL Server to try to fix database corruption. Rather you may end up with other problems, further damaging the database, without knowing what is going on behind the scenes.
  • Detaching the Database: Detaching a corrupt SQL database while performing database recovery can further delay the recovery process and re-attaching the database may fail. Particularly, when you cannot run crash recovery on a database (i.e. in SUSPECT or RECOVERY_PENDING mode), SQL Server will fail to attach that database until it is repaired. You can try to hack-attach a damaged database, but it can be a time-consuming and painful experience.  
  • Repairing Database with ‘REPAIR_ALLOW_DATA_LOSS’ before Restore: Restoring a database from the most recent backup is the first thing you should do before trying to repair the database with ‘REPAIR_ALLOW_DATA_LOSS’. This is because the repair option can result in data loss. 
     


Steps to Repair Corrupted SQL Server Database

You can try to restore the database from a healthy backup. If the backup is not updated or is corrupted, run DBCC CHECKDB with repair options to fix corruption in a SQL database. 

  • REPAIR_FAST: This options helps fix backward compatibility issues in a database.
  • REPAIR_REBUILD: It helps rebuild an index and repair any missing rows in a table with nonclustered index – without any data loss. 
  • REPAIR_ALLOW_DATA_LOSS: It can fix all types of corruption errors. However, Microsoft recommends using this repair option as the last resort to repair database corruption, as it can cause data loss. 

Follow these steps to repair a corrupted SQL Server database using DBCC CHECKDB:

Step 1: Put Database in Emergency Mode
In most cases, you may have problem accessing a corrupt database. However, you need access to the database to run the repair operation. Setting the database to EMERGENCY mode helps provide read-only access to the database. 

Note: If you access your database, but suspect corruption, skip to Step 2. Also, replace ‘Test_Database’ with the name of your database you want to repair. 

Open SQL Server Management Studio (SSMS) and run the following query to put the database in EMERGENCY mode:

ALTER DATABASE (Test_Database) SET EMERGENCY

Step 2: Get all Corruption Errors
Once you’re able to access the database, check for corruption errors in the database by executing the below DBCC CHECKDB command:

DBCC CHECKDB (Test_Database) WITH NO_INFOMSGS

Note: Using ‘NO_INFOMSGS’ argument helps suppress all the informational messages and shows only the relevant error messages. 

Running the above DBCC CHECKDB query will recommend minimum level of repair option to fix consistency errors (if any).

Step 3: Put the Database to SINGLE_USER Mode
Before using the repair option, set the database status to SINGLE_USER. Doing so will prevent other users from making any changes to the database during the repair process. 
To change the database status to SINGLE_USER, run this query:

ALTER DATABASE Test_Database SET SINGLE_USER WITH ROLLBACK IMMEDIATE

Step 4: Repair the SQL DatabaseNow run the DBCC CHECKDB with the recommended repair option. For instance, the below query helps repair the database using REPAIR_ALLOW_DATA_LOSS:

DBCC CHECKDB (Test_Database, REPAIR_ALLOW_DATA_LOSS) WITH ALL_ERRORMSGS, NO_INFOMSGS;

Step 5: Put the Database Back to MULTI_USER Mode
Once the database is repaired, put it in MULTI_USER mode:

ALTER DATABASE Test_Database SET MULTI_USER

Important: As discussed earlier, using the REPAIR_ALLOW_DATA_LOSS option involves data loss risk. However, you can avoid data loss and recover all the database objects by using SQL database repair tool, such as Stellar Repair for MS SQL.

Use Stellar Repair for MS SQL to Restore Your Database without Data Loss

Stellar Repair for MS SQL can repair a large-sized, severely corrupt database while keeping its data intact. The software repairs the database (MDF and NDF) files and restores the database with all its objects, in just a few clicks. It can fix any type of corruption affecting the database files, like index level or page level corruption, file header corruption, or commonly encountered SQL errors.

Also, the software helps recover deleted records from a corrupted database. Further, it provides multiple options to save the repaired database. You can save it to a New or Live (existing) database. Or, you can choose CSV, XLS, or HTML file format for saving the repaired database.


Wrapping Up

A SQL database may get corrupt due to several reasons. To avoid database downtime and data loss in the event of corruption, you must repair the database or restore it to its original state. You can try running DBCC CHECKDB with the ‘REPAIR_ALLOW_DATA_LOSS’ option to repair corruption in a SQL database.

However, it can lead to data loss. Also, it may not work if the database is severely damaged. A better alternative is to use Stellar Repair for MS SQL software. Download the software’s free demo version now to ascertain its effectiveness. 

76% of people found this article helpful


Most Popular

Recent Knowledge Base