Database Corruption Overview for Beginners

Database Corruption Overview: Database corruption is one of those things that you can only plan for by practicing your response plan. Out of all of the things that can happen to your SQL Server this is the one that you are most likely going to want to ask for help when you encounter it.

What is Corruption?

Database corruption refers to corrupt 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 this type of corruption prevents the database from starting, other times it may prevents queries from running. Sometimes it may go undetected for some time, and may present as missing or incorrect data.

Causes of Corruption

The most common causes of database corruption are the following:

Drive / Storage Failure – example drive replacement in RAID array.
Power Outage – While database pages are being written to.
Network issues for network attached storage.

Most of the time it is comes down to problems with I/O, specifically problems with the I/O when SQL Server is attempting to write to your database file.

Detecting Corruption

Ways that you may discover database corruption include jobs that run DBCC CheckDB, DBCC CheckTable or other DBCC Checks. It is recommended to run DBCC CheckDB daily if the load on your system allows for that frequency. The table msdb..suspect_pages may contain information about corrupt pages. This is rare, and most likely will contain information when you restore a corrupt database.

Sometimes just running a query may show corruption, instead of getting the expected results, you may get no results other than an error message, or you may get a subset of the expected results with an error message.

A database that after a reboot comes back in Recovery Pending mode, and says in that mode may be an indication of database corruption.

If you discover database corruption in any of these areas, there are many options available to recover the data, however if you run one of the DBCC checks with the REPAIR_ALLOW_DATA_LOSS option you will end up dropping data that may have been able to be saved if other options were used.

If you get stuck with Database Corruption and don’t know what to do, contact me and I can arrange a short term emergency response consulting engagement to help get your corruption resolved.

Leave a Reply Cancel reply

Login