February 9, 2018
DBCC CHECKDB Repair Options - Which is the best
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:
- Corruption on page 1: 94299
- Index ID =0, the database corruption is in “clustered index”, and the data should be repaired using an appropriate “Repair method”
- Error Types – consistency errors in Table and consistency errors in database
- Suggested Repair Level: Repair_Allow_Data_Loss
- The corruption was found in Table as well as database
- Corruption is in clustered index
- Database. 1 and row no. 94299 shows corruption
- Repair possible by running the command - Repair_Allow_Data_Loss
- Repairing of corrupt database
- No data loss during repair process
- Database accessibility after repair
Let’s take an in-depth look at the methods needed for database repair, and what if these pose limitations?
Command to repair the corruption in complete database, with the following formula: Delete broken data and fix all Structural Linkages
Retains syntax for backward compatibility and, does not perform any repair actions.
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:
- Backup to be performed with automatic full backup
- 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.
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.