Repair SQL Database Using DBCC CHECKDB Command

As an SQL Server Database Administrator, you need to ensure the flawless working of your organizational databases. Your job is to make them available, recoverable, optimized, and secure from intrusion threats. Imagine, one day you get a call from your manager about the malfunctioning of one of the organization’s databases. 

After reaching the office to check the issue and found various discrepancies. The order management system was not responding, customers were unable to place orders, and the finance department failed to access transaction files.

This is a common scenario in almost any organization, irrespective of the size or domain of the business. Being an administrator, you have to bring things under control anyhow. What will be your modus operandi? How will you get the database back on track? This blog is about troubleshooting similar issues, and our focus will be on the DBCC CHECKDB Command to fix the error.

How does a SQL database become corrupted?

A database corruption takes place in instances where the disk or memory data pages become logically inconsistent or physically damaged. Here are a few reasons that can trigger this situation:

• Hardware crashes resulting from several issues, such as:
  

• Faulty RAM, corrupting pages during writes
• Disk errors
• RAID controller cache issues
• Failure of physical storage mediums during write-ahead logging  
  

• I/O Subsystem Issues, caused by the following reasons:
  

• Outdated drivers
• Torn writes due virtualization misconfigurations
• SAN/NAS instability
• Unstable atomic page writes
  

• Improper shutdowns due to power failures
  

• Power outages in the middle of write operations can result in partially written pages, hampering atomicity
• Mitigating this issue is possible through torn-page detection with enabled checksums

• File System Misconfiguration
  

• In edge cases, instances of mismatches in compressed or encrypted volumes, or sector size, can cause allocation maps corruptio
  

• Malware intrusion
  

• An attack by malicious scripts or ransomware can modify system tables, obstructing referential integrity
  

• Human errors
  

• Manual errors, like incorrect restore sequences or accidental deletion of MDF/LDF files, can lead to orphaned pages in the database.

Let’s move on to know the specific errors you may encounter because of any of these data corruption issues.

Which Error messages result from database corruption?

Corruption in the MS SQL database corruption can give way several issues. You can identify these bottlenecks by the respective error messages, as shown in the table below.

To detect these errors and proceed with their repair, admins prefer using the DBCC CHECKDB Command, as it protects database integrity by strictly adhering to the ACID principles.

How can DBCC CHECKDB help in protecting database integrity?

Data corruption causes several severe issues that may affect the entire SQL instance, besides leading to failed SQL queries and incorrect data results. DBCC CHECKDB warns against such data corruption issues and helps to fix them before the situation aggravates. 

DBAs use this SQL command mainly to validate database consistency and durability. This further ensures the physical and logical structures adhere to ACID principles. Let’s understand this better with a table, consisting of the ACID properties and the challenges that may hamper them:

In its pursuit to maintain database integrity by following the ACID principles, the DBC CHECKDB command uses several crucial options. While some help to find the issue, others assist in its repair.

How does DBCC check the physical and logical integrity of a database?

DBCC checks all the items in a particular database for logical and physical integrity by executing several options:

• DBCC CHECKDB: Checks the logical and physical consistency of indexes, system catalogs, tables, and allocation structures of the specific database.

• DBCC CHECKALLOC: Checks the database structures for disk space allocation consistency

• DBCC CHECKTABLE: Checks all the views and tables of a database for the integrity of the contained structures and pages  

• DBCC CHECKCATALOG: Checks the database for catalog consistency
• DBCC CHECKFILEGROUP: Checks a respective filegroup within a database for consistency in the table and allocation
• DBCC CHECKCONSTRAINTS: Scans the database for several table constraints for violations, such as CHECK and FOREIGN KEY

All these DBCC options help to detect the issues within the database tables, views, and the allocation structures. Among these options, the DBCC CHECKDB Command detects the issues and uses several repair options to fix those database issues. The next section describes these options in a better way

How does DBCC CHECKDB repair the corrupted SQL database?

After finding the error, the DBCC CHECKDB Command can help to troubleshoot the issue when used with several repair options. Before trying these options, you must have an adequate backup of the database.  

1. REPAIR_FAST

Command: DBCC CHECKDB (‘Name of your Database’, REPAIR_FAST);

Function: The REPAIR_FAST option performs minor maintenance jobs and is suitable only for backward compatibility. It does carry out any repair task in a corrupted database.

2. REPAIR_REBUILD

Command: DBCC CHECKDB (‘Name of your Database’, REPAIR_REBUILD)

Function: Performs quick repairs that are free from any scope of data loss, such as repairing missing rows non-clustered indexes. It can also carry out time-consuming repairs, such as index rebuilding.

3. REPAIR_ALLOW_DATA_LOSS  

Command: DBCC CHECKDB (‘Name of your Database’, REPAIR_ALLOW_DATA_LOSS)

Function: Using this repair option with the DBCC CHECKDB command will bring the corrupted database back to its consistent state. During the process, it can delete a few pages and the contained items. An advisable move would be to use the REPAIR_ALLOW_DATA_LOSS option as the last option. For example if there is no backup, or if the database restore is impossible due to some other reason. Even after rebuilding the log, this repair option does not ensure to retain the ACID principles.

Using an automated alternative for SQL database repair

Despite being successful, these repair options are prone to data loss and consume considerable time. You must also have detailed technical knowledge to execute these SQL Server database commands. To avoid such hassles, DBAs often use third-party SQL recovery tools as alternatives. This is where Stellar Repair for MS SQL can help you out. You can benefit from both desktop software and an online SQL repair service from Stellar. If your corrupt database is less than 5GB, its online SQL repair service allows you to upload your file to repair the database online without hassles.

Conclusion

As the Database Administrator of your organization, the proper functioning of the databases ought to be your prime duty. However, several moderate to severe issues, such as hardware failure, improper system shutdown, malware attacks, human errors, and so on, are likely to disturb the ACID properties of the database, making it corrupt. Executing the DBCC CHECKDB Command on the corrupted database with several options will help detect the exact error message. This will enable you decide the appropriate solution. 

For example, you may face Error 823, 924, 2533, 8905, 8928, and so on. Before proceeding with the adequate repair option, make sure to create a proper backup of the database. To repair the corrupted SQL database, options like REPAIR_FAST, REPAIR_REBUILD, and REPAIR_ALLOW_DATA_LOSS are possible options. 

The first two options are safe but unsuitable for severely corrupted databases. The last option will lead to data loss, as it deletes the corrupted pages from the database entirely. Therefore, be careful before using it. To avoid the fear if losing data, you may also use SQL recovery tools, such as Stellar Repair for MS SQL. The online SQL repair service of this software allows for repairing the database quickly and efficiently.