At Stedman Solutions, LLC the team and I do many database repairs from simple things that take just a few minutes to larger repairs that take many days to repair. Since 2015 we have been doing database corruption repair and have seen many different decisions made by the business owners with the corrupt database.
Often times when investigating a corrupt database with a client it comes down to a choice of repairing a corrupt database or not.
Some of the reasons that I have heard for not repairing a corrupt database are the following:
- The business won’t allow us to take a downtime maintenance window to do the repair.
- Nobody is complaining about the issue, all we see are the errors from CheckDB that say there is a problem. Our system runs fine.
- It is too expensive.
Some things to consider if you decide to not repair your corrupt database.
- Without repairing your database you risk loss of the entire database.
- The corruption is not going to get better without doing something to fix it.
- Restarting your SQL Server may put you in a position where the database does not start, gets stuck in Recovery Pending, or ends up with a message about your database being Suspect. All of which will prevent the use of the database.
- Delaying on the SQL Server database repair may eliminate recovery options if the issues get worse.
Before making the decision to repair a database or not, it is good to understand, what corruption means in your database.
- Pages in the database that are incorrectly formatted. This could be as simple as a single bit, or as huge as the entire file.
- Sometimes prevents the database from starting.
- Sometimes prevents queries from running.
- Sometimes presents as missing or incorrect data.
The causes of database corruption can be:
- Drive / Storage Failure – example drive replacement in RAID array.
- Power Outage – While database pages are being written.
- Network issues for network attached storage.
- Most of the time it is problems with I/O.
Sometimes the reasons to not repair a database are valid, but often times they are just a way to justify not doing the repair. Lets look into those one at a time.
The business won’t allow us to take a downtime maintenance window to do the repair. This tells me that the database must be important to the business, otherwise nobody would be concerned with a downtime even if it takes 12 hours do do the repair. What I would ask you to consider is the following: if the corruption gets worse and it causes a week long downtime, or a permanent downtime of this database, will the business be okay with that? If your answer is “yes, we will be fine with a permanent downtime”, then no worries, you are good to go, don’t worry about the corrruption.
Nobody is complaining about the issue, all we see are the errors from CheckDB that say there is a problem. Our system runs fine. This is sometimes true, but unless you have a comprehensive test suite that hits every application that uses the database then how do you know for sure. It may be true to nobody is complaining now, but how about when things get worse and you have lost the easier options to repair the database. How about when the corrupt shows in your billing process, or for an annual report that is needed for tax purposes? Even worse how about when the corruption just throws off data and causes your application or reporting to just be wrong. If you are making business or financial decisions based on that system the corruption could impact those decisions and the business not even know it.
It is too expensive. This can be a valid point. The price of corruption repair varies depending on how corrupt the database is. If you have a single table that has a few thousand rows to be repaired that can be less expensive than if you have hundreds of tables with billions of rows that need repair. What you really need to look at is the value of that database to your business. If that database is mission critical and is required for your business to function then it is a different case than some small ancillary database that was someones pet project. A valid example of this was a client that had an in house IT Helpdesk system, to handle trouble tickets for their staff. They made the decision to just throw out the data and start over with a new help desk system. That is a different case than if your database contains medical data on patients that you are legally required to keep for some time period.
With the experience from the number of corruption repairs that we do we can reduce your risks, help get your corruption fixed without data loss and get you back online in a timely manner. Please if you have a corrupt database give us a call and we can help get it fixed.
Related links:
- REPAIR_ALLOW_DATA_LOSS example
- More on database corruption at SteveStedman.com/corruption
- Database Corruption Presentation from PASS Summit 2017.
- My blog post on On Call DBA for Database Corruption.
- Calgary SQL Server Users Group presentation on Database Corruption.
- Database Corruption Presentation from PASS Summit 2016.
- Blog post: Database Corruption Overview for Beginners.
- Pass HA/DR chapter presentation on Database Corruption with Video.
- More about Database Corruption Repair
More from Stedman Solutions:
Steve and the team at Stedman Solutions are here for all your SQL Server needs.
Contact us today for your free 30 minute consultation..
We are ready to help!