How to Use DBCC CHECKDB to Detect and Repair SQL Server Database Corruption

Has your SQL database stopped functioning for an underlying SQL Server issue? If yes, it can cause significant loss to data operations and the reputation of your organization. To avoid such issues and get the database back to working condition, admins use several methods; DBCC CHECKDB is one of them.

This command detects the corruption and performs the repair option on the database. Once the database is free from corruption, you are all set to reuse it. In case manual resolution is not enough, the use of SQL recovery software can be a suitable alternative as well. Here is a detailed insight into this topic.

What are the reasons behind SQL Server database corruption?

Multiple factors can contribute to database corruption issues, leading to its non-functioning. 

Hardware failures

In most of the cases, defective storage disks form the leading cause of the issue. Other subsystem issues, such as outdated drivers, RAID controller failure, or unstable NAS connections, can also corrupt the database.

Dirty Shutdown   

Improper system shutdown due to sudden power outages or hardware crashes can leave the database in a dirty shutdown state. Any such event prevents the database from writing all “dirty” pages from memory to disk.  

Faulty RAM

Defective server memory can harm data pages even before you write them to the physical storage device, when the pages still exist in the memory.

Malware intrusion

A virus attack or a malware infection can result into a corrupt and inaccessible database.

Human Errors

These include accidental deletion of the database or log files, incorrect file configuration, wrong antivirus settings, improper database management, and several other administrative errors. 

File system issues

Multiple file-related issues can lead to structural errors. Common problems include NTFS file system corruption insufficient system resources, and storing database files in compressed folders. 

How to resolve the database corruption issues in SQL?

Corruption can affect the ACID properties of a database, which include its Atomicity, Consistency, Isolation, and Durability. Specifically, to validate the durability and consistency of the database, the DBCC CHECKDB command is the ideal way. It ensures the logical and physical structures of the database to adhere to the ACID properties.

Doing a database restore from a healthy and recent backup a reliable method to resolve the database corruption. But, if you have an outdated or no backup, the built-in DBCC CHECKDB command will be a recommended move to repair the database. As a downside, some specific repair options used with this command lead to data loss.

Find the corruption

Before dealing with the database corruption issues, it is better to detect the level of damage caused to the database. The command below will help to serve this purpose. Here, we have named our database as SQLDummyDB. Replace it with your own database name when running the command.

Check database integrity

Use the following command to check the consistency errors, objects, and reports allocation:   

DBCC CHECKDB (‘SQLDummyDB’)

Review suspect pages

Use the following query to check suspect pages:  

SELECT * FROM msdb.dbo.suspect_pagesThe resulting table will show the pages identified as suspect because of corruption or I/O issues

Check specific tables

Use the following command:

 DBCC CHECKTABLE (DatabaseCorruptionReport)Here, DatabaseCorruptionReport is database table named SQLDummyDB. For large databases, this command helps to prevent corruption by validating the integrity of a specific table and the associated indexes.

Restore from a backup file

Once you are clear about the reason behind the corruption, the restore operation becomes easier. The safest restore method would be to use a healthy database backup by using the following T-SQL command:  

USE [master]

RESTORE DATABASE [SQLDummyDB] FROM  DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL16.SQLEXPRESS\MSSQL\Backup\ SQLDummyDB.bak' WITH RECOVERY;

GO

Use the DBCC CHECKDB command for database repair

You can deal with the corrupt database issue even in the absence of a backup file. This will need you to use the DBCC CHECKDB command with several repair options.

1. Set the database to Single_User mode:

ALTER DATABASE SQLDummyDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE

2. Use the repair options below one after another, if the previous ones do not work.

REPAIR_FAST

This option maintains backward compatibility with the legacy SQL Server versions, but it does not repair the database.DBCC CHECKDB ('SQLDummyDB', REPAIR_FAST);REPAIR_REBUILD

This function fixes database corruption errors without causing data loss. It is suitable to resolve minor data integrity and structural issues.  DBCC CHECKDB ('SQLDummyDB', REPAIR_REBUILD)REPAIR_ALLOW_DATA_LOSS

This DBCC CHECKDB repair option of SQL database performs forced repair of a damaged and corrupt database by deleting broken or unreadable data. Use this option only if the REPAIR_FAST and REPAIR_REBUILD options fail to serve the intend purpose. To be on the safe side, it is advisable to set the database to EMERGENCY mode and then to SINGLE_USER mode before using this repair option.ALTER DATABASE SQLDummyDB SET EMERGENCYALTER DATABASE SQLDummyDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE

Use the REPAIR_ALLOW_DATA_LOSS to repair the database:

DBCC CHECKDB ('SQLDummyDB', REPAIR_ALLOW_DATA_LOSS)

After performing the database repair, change the SINGLE_MODE of the database back to MULTI_USER mode.

     ALTER DATABASE SQLDummyDB SET MULTI_USER;

Using Stellar Repair for MS SQL to repair SQL Server Database corruption

The manual method of DBCC CHECKDB repairs the database, but with the threat of significant data loss. If you do not want to lose the data, it is better to use a suitable automated alternative. The SQL recovery tool, such as Stellar Repair for MS SQL, can be a feasible option in this regard.The software can resolve several database corruption issues without using the DBCC CHECKDB command:

· DBCC CHECKDB returns errors

· Database marked as SUSPECT

· Corrupted or missing backup file

How to repair the database using Stellar Repair for MS SQL?

The entire process completes in three easy steps:

· Upload the corrupt MDF file

· Analyze and repair the file without any human intervention

· Preview the repaired MDF file and download it

The software allows you to upload and repair a corrupt MDF file of up to 5GB. The Desktop Edition of Stellar Repair for MS SQL allows you to repair the damaged files irrespective of size and file limits.

Conclusion

Your SQL Server database may become corrupt due to multiple reasons. To identify the real reason behind corruption and resolve it, admins preferably use the DBCC CHECKDB command with various options. For instance, to find the state of the database, you can use DBCC CHECKDB (‘Database name’).

Options like REPAIR_FAST, REPAIR_REBUILD, and REPAIR_ALLOW_DATA_LOSS can repair the underlying errors. However, this command and the associated repair options, such as REPAIR_ALLOW_DATA_LOSS, can cause considerable data loss. Compared to an automated SQL recovery tool, such as Stellar Repair for MS SQL, they are less efficient and slower.

Hence, this SQL database repair tool would be ideal to replace the use of the DBCC CHECKDB command. This method is easy, quick, and allows you to repair MDF files of up to 5 GB. You can download this SQL recovery application from the respective official webpage of Stellar.