DBCC CHECKDB reporting consistency issues, queries failing with high-severity errors and a failed backup job are all signs of corruption in a SQL Server Database. Also, SQL Server reporting errors, like 823, 824, 5172, etc., indicates database corruption. The database must be repaired before you can access it and its data. Before you can repair a corrupt SQL Server database, you need to know the causes of corruption. You should also know the best practices to avoid detecting corruption in the SQL Server database.

What Causes Corruption in Sql Databases?

These are the most common causes of corruption in databases:

  • Power Failure: Sometimes, corruption occurs when the server crashes or goes down due to power failure. The server crashes instead of closing down gracefully while it processes the data.
  • I/O Subsystem Failure: According to online sources, 99 percent of corruptions in SQL databases are caused by problems with the I/O system. Systematic corruption can be caused by failing disk drives, disk controllers or SAN controllers.
  • SQL Server Bug: Many bugs in Microsoft SQL Server can cause corruption. Online index rebuild in SQL Server 2014 and 2012 can cause data corruption in clustered tables. See KB2969896 for more information.
  • Malicious attacks: Malicious programs or agents can infect SQL Server files, rendering the database inaccessible or corrupt.

Another reason a SQL Database could become corrupt is abrupt system termination or changes made in a SQL Server Account.

What to do when you encounter corruption?

If you find corruption in a SQL Server Database, be sure to avoid these:

  • Shutting down the SQL Server: When a database becomes corrupt, it goes into recovery mode. The server is shut down can cause the database to become inconsistent while recovering.
  • Restarting SQL Server: You should not restart SQL Server to correct database corruption. You may have other issues that further damage the database.
  • Re-attach the Database: Performing database recovery while detaching a corrupt SQL table can delay the recovery process. Crash recovery cannot be performed on a database, not in use (e.g. in SUSPECT or RECOVERY_ PENDING mode). Attach a database until it has been repaired or is in SUSPECT mode. Hack-attaching a damaged database can be done, but this can be time-consuming and painful.  
  • Repairing the database with ‘REPAIR_ALLOW_DATA_LOSS’ before Restore: Restoring a database from the most recent backup is the first thing you should do before repairing the database with ‘REPAIR_ALLOW_DATA_LOSS’. This is because data loss can occur if you choose to repair the database.

How to Fix a Corrupted SQL Database Server

It is possible to restore the database using a healthy backup. To fix corruption in a SQL Database, you can run DBCC CHECKDB if the backup has not been updated.

  • REPAIR_FAST: This option fixes backward compatibility problems in a database.
  • REPAIR_REBUILD: it is a tool that rebuilds an index and repairs any missing rows in tables with non clustered indexes – without data loss.
  • REPAIR_ALLOW_DATA_LOSS: It is able to fix all types of corruption errors. Microsoft recommends that this option be used as a last resort for database corruption repair because it can lead to data loss.

These steps will help you repair a corrupted SQL server database using DBCC CHECKDB:

Step 1: Set up an emergency mode for your database

In most cases, you will have difficulty accessing corrupt databases. However, to repair the database, you will need to have access to it. You can gain read-only access by setting the database to EMERGENCY mode.

To put the database into EMERGENCY mode, open SQL Server Management Studio (SSMS).

ALTER DATABASE (Test_Database) SET EMERGENCY

Step 2: Correct all corruption errors

Once you can access the database, verify for corruption by running the below DBCC CHECKDB command.

DBCC CHECKDB (Test_Database) WITH NO_INFOMSGS

Note: The ‘NO_INFOMSGS argument suppresses all informational messages and displays only relevant error messages

The above DBCC CHECKDB query will suggest a minimum level to repair consistency errors.

Step 3: Change the database to SINGLE_USER mode

Set the database status to SINGLE_USER before you use the repair option. This will prevent other users from modifying the database during the repair process.

This query will change the database status in SINGLE_USER.

ALTER DATABASE Test_Database SET SINGLE_USER WITH ROLLBACK IMMEDIATE

Step 4: Repair the SQL Database

Now run DBCC CHECKDB using the recommended repair option. For instance, the below query helps to repair the database using REPAIR_ALLOW_DATA_LOSS:

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

Step 5: Return the database to MULTI_USER mode

After the database has been repaired, you can put it into MULTI_USER mode.

ALTER DATABASE Test_Database SET MULTI_USER

Important: Using the REPAIR_ALLOW_DATA_LOSS option involves data loss risk. However, you can avoid data loss by using SQL database repair tool such as Stellar Repair MS SQL.

Stellar Repair for MS SQL is a tool that can restore your database without data loss.

Stellar Repair for MS SQL can repair large, corrupt databases while keeping all data intact. It repairs and restores all objects in the database, including MDF and PDF files. The software can repair any corruption in the database files, such as page or index level corruption, file header corruption or common SQL errors.

The software can also recover deleted records from corrupted databases. It offers multiple ways to save the database. It can be saved to an existing or new database. You can also save the database in CSV, XLS or HTML format.

Wrapping up

There are many reasons why a SQL database can become corrupt. You can repair SQL database or restore the database to its original state to avoid downtime and data loss. To repair corruption in a SQL database, you can try running DBCC CHECKDB with the ‘REPAIR_ALLOW_DATA_LOSS’ option to repair corruption in a SQL database.

It can also lead to data loss. It may also not work if your database is badly damaged. Stellar Repair for MS SQL is a better option. To test its effectiveness, download the free demo version of Stellar Repair for MS SQL software now.

RELATED ARTICLESMORE FROM AUTHOR