How to repair SQL Database DBCC CHECKDB command for MDF & NDF Files

SQL Database Recovery /December 26, 2024

If you are experiencing the below symptoms in your SQL Server database files, then it is obvious that they have become corrupted or damaged and need immediate attention.

  • Frequent database crash
  • Constant downtime
  • Performance issues with the database
  • Noticeable lags
  • Major data loss or inconsistencies
  • Failure in the data backup task
  • Software errors and glitches
  • Random errors while opening or accessing the database
  • The database moves to the suspect mode

If you are looking for a solution to repair the corrupt or damaged SQL database DBCC CHECKDB command for MDF and NDF files, then this blog will really prove helpful.

What is an MDF and NDF File?

MDF stands for Master Database File, and NDF stands for Secondary Database File. Both MDF and NDF files store data and objects of the SQL Server database.
The MDF file is nothing but a primary data file containing the database’s schema and data, including tables, views, stored procedures, triggers, and other objects. It also contains metadata that describes the database’s structure.
NDF files are secondary data files that can be added to the SQL Server database to store additional data. They are used when the MDF file is not sufficient to hold all the data.NDF files help improve performance and storage management.
An SQL Server database has only one MDF file but can have multiple NDF files. SQL Server can operate with just an MDF file, but NDF files help manage larger databases and thus optimize performance.

Few SQL Server Error Messages

  • Msg 823 error in SQL Server
  • Msg 824 (I/O error) in SQL Server.
  • Msg 825 (read retry) in SQL Server.
  • SQL Server Page-Level Corruption
  • SQL Server Table Corruption Error.
  • Corruption on non-clustered indexes.
  • Database consistency errors reported by DBCC CHECKDB.
  • Corruption on data pages.
  • Metadata Corruption Error.
  • Error 9004 in SQL Server
  • Msg 7105 in SQL Server

Common reasons that lead to corruption or damage of SQL database

  • Human errors like accidental deletion
  • Sudden shutdown
  • Improper system termination
  • Abrupt power cuts or power outages
  • Virus or malware attacks
  • Hardware issues like bad sectors in the storage device, physical damage to the disks, etc.
  • Software bugs or glitches
  • Network issues
  • Changes in SQL account
  • Incompatible software or outdated installed software
  • Inappropriate isolation level
  • Insufficient storage space on the hard disk

DBCC CHECKDB Command

Using the DBCC CHECKDB command is a manual method for repairing corrupt or damaged Microsoft SQL Server database MDF and NDF files.

Syntax:

DBCC CHECKDB (Database_Name)

Pre-requisites of DBCC CHECKDB command

  • With an active license, users must have SQL Server Management Studio (SSMS) installed on their system.
  • Users must have Admin rights and privileges to perform this manual command.

Three Repair Modes with DBCC CHECKDB Command

REPAIR_FAST

Use this repair mode to resolve the backward compatibility issue, which means you can run SQL Server 2005 database files in SQL Server 2008.
While using this repair mode, ensure the database is in SINGLE_USER mode.

Syntax:

DBCC CHECKDB (N ‘Database_Name’, REPAIR_FAST) WITH ALL_ERRORMSGS, NO_INFOMSGS;
GO

REPAIR_REBUILD

This repair mode is preferred as it has a very low risk of data loss. It performs similarly to REPAIR_FAST, except that it rebuilds the indexes, which is a time-consuming repair process.
While using this repair mode, ensure the database is in SINGLE_USER mode.

Syntax:

DBCC CHECKDB (N ‘database name’, REPAIR_REBUILD) WITH ALL_ERRORMSGS, NO_INFOMSGS;
GO

REPAIR_ALLOW_DATA_LOSS

This repair mode, along with repairing the SQL Server, performs the following:

  • Allocates and deallocates the pages
  • Deletes the corrupt objects
  • Fixes page errors, structural errors in the database

Syntax:

DBCC CHECKDB (N ‘Database_Name’, REPAIR_ALLOW_DATA_LOSS) WITH ALL_ERRORMSGS, NO_INFOMSGS;
GO

Limitations of DBCC CHECKDB command

  • Unreliable
  • Complex
  • Requires experienced professionals to perform this manual method.
  • It doesn’t repair deleted objects from the database.
  • Users often skip pre-requisites of the DBCC CHECKDB command.

3rd-party SQL Database Recovery Tool

SQL Database Recovery Tool is a third-party professional tool that efficiently recovers corrupt or damaged SQL Servers. The SQL Database recovery tool repairs minor and significant levels of corruption in SQL Server database files. It recovers all SQL database components, including Rules, Functions, Tables, Triggers, Views, Stored Procedures, Indexes, etc.SQL. The Database recovery tool supports recovering database files of all SQL Server versions, including 2019, 2017, 2016, 2014, 2012, 2008, 2208 R2, 2005, 2000. The tool displays a preview of recovered data.

Conclusion

The moment the SQL Server database gets corrupted or damaged, users look for solutions to restore database files with complete accuracy. Initially, they try the manual method DBCC CHECKDB command, but it has its own limitations. To overcome those limitations, we suggest our users use an automated SQL Database recovery tool to efficiently repair and recover Microsoft SQL Server database files. The advanced solution repairs and recovers all SQL Server components without fail.

sql-database-recovery