Ready to Start Your Career?

DBCC CHECKDB Repair Options - Which is the best

robinshanab 's profile image

By: robinshanab

February 9, 2018

Today, the IT-requirement is experiencing a significant overhaul in its approach. Most SQL Administrators are shifting from standard SQL management to intelligent SQL management, as present circumstances demand to act fast, proactive and strategic. Running DBCC CHECKDB command is one such proactive measure taken by SQL DB Administrators to check for any inconsistency and building a maintaining the backup of the database with a regular backup-health-check is a strategic approach to combat corruption issues.

This article highlights the steps to take to repair the corrupt database, but don’t overlook to investigate the cause of corruption. Thankfully, the proactive approach of running DBCC CHECKDB command ensures thorough check of the disks and SAN, but keeping an eye on Windows Event Logs is equally essential as it may also display an error message to help in investigations.

Investigate the cause

Run DBCC CHECKDB command and analyze the output 

The investigations reveal the following points:

  1.  Corruption on page 1: 94299
  2. Index ID =0, the database corruption is in “clustered index”, and the data should be repaired using an appropriate “Repair method”
  3. Error Types – consistency errors in Table and consistency errors in database
  4. Suggested Repair Level: Repair_Allow_Data_Loss
Error InferenceAn error message was displayed on running the DB-Check command. Let us investigate the pages which are affected, the type of data which is modified, index entries of changed data, and much more
  1. The corruption was found in Table as well as database
  2. Corruption is in clustered index
  3. Database. 1 and row no. 94299 shows corruption
  4. Repair possible by running the command - Repair_Allow_Data_Loss
As long as the database is available in the repairable form, the last line of the output of DBCC CHECKDB command always indicates the repair option to be used. The most prevalent repair options include: REPAIR_ALLOW_DATA_LOSS | REPAIR_FAST | REPAIR_REBUILDWhen considering a repair solution for your corrupt SQL database, look for a solution that addresses the three principal challenges of database repair –
  1. Repairing of corrupt database
  2. No data loss during repair process
  3. Database accessibility after repair

Let’s take an in-depth look at the methods needed for database repair, and what if these pose limitations?

REPAIR_ALLOW_DATA_LOSS

Command to repair the corruption in complete database, with the following formula: Delete broken data and fix all Structural Linkages

REPAIR_FAST

Retains syntax for backward compatibility and, does not perform any repair actions.

REPAIR_REBUILD

Repairs the database where data loss is not possible. The quick repair mechanisms include repair the missing rows in non-clustered index, and the time-consuming mechanism includes rebuilding an index.

Limitations of REPAIR-Commands

The limitation of this command are:

  • In REPAIR_ALLOW_DATA_LOSS, the Database strings which are attached to the SQL Database are repaired and the strings which are not-attached are deleted. This may lead to loss of data in the repaired MDF file.  By allowing the Repair_Allow_Data_Loss feature, you are also allowing DBCC CHECKDB repair option to delete a part of SQL database to accomplish the repair.
  • The REPAIR-FAST option does not perform repair functions
  • REPAIR_REBUILD option is unable to perform the repair in case of clustered index.

 Store SQL Database backup

When a corruption issue is detected by DBCC_CHECKDB command, the best course of action is to restore the database from last full backup.

Analyze the state of backup before conducting the Restore process, and take care to perform backup considering the following pre-conditions:

  1. Backup to be performed with automatic full backup
  2. Understand the level of damages if a database backup is not complete. Restore from database backup is helpful only when the last backup is recent and up-to-date

SQL repair options are never simple, but these are essential to retrieve data from the corrupt mode and let the database be up and running within no time.

Power the intelligent SQL Database Repair Software

MS SQL Database should not be in inaccessible state at any point in time, hence; it is advisable to remove the corruption in database and take help of SQL Repair software. One of the most performing and result-oriented software is Stellar Phoenix SQL Database Repair Software with a proven track record of repairing the corrupt database without any data loss.

The 4-step SQL Database repair-process of Select à Scanà Repair à Save lets the user perform the database-repair function with efficiency and recover the repaired database.

Epilogue

As the organizations are accumulating huge databases and accentuating its accessibility to more and more users, it has become increasingly evident that there is a need for more endpoints to manage, and more data to monitor. In that case, regular backup of database plays an essential part. If the database management results in some error and its backup is not up-to-the-mark, then there is only one option i.e., to repair the database with Stellar Phoenix SQL Database Repair Software. 

Schedule Demo