Summary: As we know the SQL Server Database is vulnerable to corruption and as per job requirement, every administrator does have the recovery plan for every scenario if persists. In this situation administrators as default trust on their skill to make the database corruption free, so in this article, we will check if DBCC CHECKDB Repair Options Ensure Complete Recovery. Also, we will discuss about DBCC CHECKDB alternative solution to fix SQL database corruption errors.
Most of the time you are going to face corruption in your database and you need to repair it because you do not have the backups necessary to be able to restore with no data loss. It is unlikely unless your service level agreements say that downtime is more important than data loss that you are going to choose repair as a first choice.
Now you have to be very careful when you are using repair or when you are manually fixing corruption as it may involve data loss, and you do not want to make things worse. That means that you are going to practice, using repair to make sure you know what it is going to do, or definitely practice when you are manually fixing corruptions before doing it for real in production. In this article then, we are going to cover how repair works and everything about different things about repair, REPAIR_ALLOW_DATA_LOSS, and possibilities of exploring more when we say repairing of the corrupt database.
How Do DBCC CHECKDB Repair Options Work?
- What is the purpose of the repair?
- The purpose of repair is not to try and save all your data. That does not mean that it goes out of its way to delete data, it just does not go out of its way to try and save data in the face of corruption.
- The purpose of repair is to make the database structurally consistent so that the storage engine can process the database without running into corruptions. It tries to do this as fast as possible, which is why repair options in REPAIR_ALLOW_DATA_LOSS is trying to make the database consistent and some data are deleted, even it deletes those data which has to be repaired.
- How does it know what to repair?
- Well at each stage of running DBCC CHECKDB, it has a list of corruptions that has just been found and it processes those corruptions.
- How does it choose what to repair first?
- Well, Every corruption has a ranking for how intrusive the repair is going to be and it processes the most intrusive repairs first because quite a lot of the time processing one of those intrusive repairs means that some of the other more minor problems can be crossed off the list.
- Does it repair everything?
- Well, check the output. At the bottom of the output, it is going to found so many errors and fixed so many errors. If the two numbers match, then it thinks it fixed everything, but be careful because sometimes a corruption can mask other corruptions.
- A corruption can prevent CHECKDB from looking into a section of the database and fixing that corruption. The next time CHECKDB runs, it could mean that it is able to look into another portion of the database where it finds other corruptions that it could not find and fix the first time when come across.
- Why can’t it be done online?
- Well, to be honest, it is really just not possible to do them online coping with data changing at the same time as trying to fix up corruptions just because of the nature of how repairs work and the physical changes that are being made to the database by those repair options.
So if you want to know which repair option to use, you run DBCC CHECKDB and it is going to tell you what the repair option is necessary, and here are the various repair options:
- Does nothing and exists for backward compatibility purposes
- Performs repairs that will not cause data loss
- Eg. rebuilding damaged non-clustered indexes
- Usually better to manually rebuild indexes
- Performs repairs that are likely to cause data loss
Beware of REPAIR_ALLOW_DATA_LOSS
This option was very deliberately named, and it was named such because the way it usually fixes any problems is by de-allocating what is broken and fixing up all the links, either from the broken thing or to the broken thing. The reason it does this is that this is the fastest and most probably correct in 100% of cases way to affect the repairs.
- It does not take into account:
- Foreign-key constraints
- Inherent business logic and data relationships
- Before running repair, protect yourself (Take a backup and check replication topologies involved)
- After running repair, check the data
- Run DBCC CHECKDB again to make sure all corruptions were repaired
- Run DBCC CHECKCONSTRAINTS if necessary
- Reinitialize any replication topologies involved
The Repairable & Unrepeatable Errors
- Example of repair and what it fixed?
- A missing non-clustered index row
- Just insert the missing record
- A corrupt data record
- Maybe delete the record, maybe delete the whole page
- An extent allocated to multiple objects
- Performs deeper examination of the pages to the extent
- A missing non-clustered index row
- Remember there are some un-repairable errors
- System table clustered index data pages
- PFS pages
- Data purity errors
Misconceptions about Repair
- The repair will not cause data loss (It depends)
- Repair should be run as the default (No)
- You can run repair without running DBCC CHECKDB (No)
- As soon as you’ve run repair, continue as normal (No)
- Repair can always fix everything (No)
- Repair is safe on system databases (No)
- You can run repairs online (No)
- REPAIR_REBUILD will fix everything (No)
- Repair fixes up constraints (No)
- Repairs are propagated to replication subscribers (No)
Try Stellar Repair for MS SQL Software
This SQL database repair software is a hassle-free solution for all SQL database corruption errors, which can be used to repair the corrupt MDF and NDF files. This software ensures recovery of all the database objects thus leaving no chance of data loss. So I encourage you to start downloading the trial version of Stellar Repair for MS SQL software to be prepared for the most critical situation you might face and to get the most optimal solution for your corruption.
As I mentioned before, almost every DBA is going to see corruption at some point in their career and dealing with it they rely on DBCC CHECKDB Repair Options or trusted third party tool, so henceforth it is really useful to understand what types of solutions you have and what it can do depends on the criticality of the scenario . I hope this article has been informative for you.