SQL Server Repair – The Most Common Causes of Database Corruption and How to Fix Them

Repairing databases is a frequent task that we all must do. Whether it’s dealing with corruption due to an update that went wrong, media failure on the disk drive where the data file is stored, or just trying to recover from a crash – most of us have had to repair at least one database in our careers as SQL Server Developers and Administrators. In this article, I am going to discuss some of the more common issues and their resolutions.

Many times when designing applications we forget about how fragile our data can be and end up creating structures that don’t scale very well or aren’t very flexible. There is a reason that best practices recommend avoiding using Triggers when possible because when they do fail it can be difficult to fix the problem. I know many of you are thinking “But I’ve never had any problems with my database!”. To which I say congratulations on your good fortune 

The Most Common Causes Of Database Corruption Are:

1) Transaction Log Corruption – 

Due to media failure or lack of disk space.

2) Index Corruption – 

Caused by incorrect application design, hardware failure, or power loss in the middle of index updates.

3) Data File Corruption – 

When data files have become too large for the file system to handle properly, either due to excessive growth or complete fragmentation. This also happens when the server crashes during a bulk or copy operation.

4) File Group Corruption – 

Due to hardware failure, power loss, or file size issues similar to data file corruption.

5) Heap Corruption – 

When an application has improperly used dynamic SQL causing the heap to get out of sync with the database schema.

6) User Database Corruption – 

This one is rare and usually means that someone was poking around in places they don’t belong and made some changes that cause problems (or somebody with physical access deleted some objects).

7) Tempdb Corruption – 

I personally don’t deal with TempDB very often but I understand it can be corrupted under certain circumstances. There are also occasions where corruption can be introduced into the physical database structure if there is a bug in one of the application’s stored procedures.

I hope that by now you are starting to see that repairing databases is not as simple as it might at first seem. For example, say an application developer has included a trigger that makes updates to a table on some sort of schedule – and they forgot to include code to check for errors, which ends up corrupting the index on the target table. You go ahead and try repairing the database with no luck – because SQL server repair doesn’t know what caused the problem or how to fix it – so it quits without actually resolving anything.

Next, you try rebuilding the indexes – but this time your luck runs out and you are unable to complete the operation because of insufficient disk space. Finally, you resort to restoring from your last good backup – but this time SQL Server fails in the middle of the restore operation with an error saying “Server: Msg 5120, Level 16, State 1, Line 0  RESTORE DATABASE is terminating abnormally”. I have seen this happen more than once!

So how does one go about correcting these types of problems? The long and short answer is that it depends on what exactly has gone wrong. However, there are some tools that can be used in most instances (although each problem will require a slightly different approach). A great place to start researching any issues is https://support.microsoft.com/en-us/kb 

Transaction Log Corruption – 

TLog Restore is used to restore the transaction log up to a given point in time so any transactions which have not been committed will be lost, but this should allow you to at least get your database working again while you fix the other issues (you might consider using DBCC CheckDB() and DBCC CHECKTABLE() commands if possible because they can help catch some issues before they get out of hand).

Unfortunately, there isn’t much you can do unless you suspect that there is a problem with I/O or fragmentation on your hard drives (you can try running the DBCC FIX FILE () command) – plus if the corruption has gone unnoticed long enough, this really isn’t going to help. I do recommend checking with Microsoft though because they may provide you with some additional options.

Index Corruption – 

Index rebuild is the tool of choice for repairing corrupt indexes. I have used it many times and would say it works about 50% of the time if your luck holds out.

Conclusion: 

So, there you have it – this should give you a pretty good idea of what goes on behind the scenes when repairing databases. As I stated in the beginning this is just meant to be an introduction and if you want to know more about these SQL Server Repair or other related information then I suggest that you take some time and learn as much as possible.