SQL Server database corruption, detection, and simple fixes

Brief introduction

In an ideal world, there will be no damage to the database, just as we will not include some serious surprises in our daily lives, and once this happens, it will have a very significant impact on our lives, as in SQL Server, perhaps within a few years you have not met This kind of situation occurs in the database, and once you encounter this kind of situation, it is often accompanied by the loss of data, downtime, serious even your own career will be affected. So for this kind of situation, we need to understand the knowledge of database corruption so that we can prepare beforehand and be able to deal with it afterwards. This article will discuss the causes of database corruption, phenomena, prior and subsequent treatment methods and simple repair methods.

Why is the database corrupted?

Before you know about database corruption, let's first understand how SQL Server saves data to data files (MDF, NDF, and so on). Whether you update or insert data, the data needs to reside in the in-memory buffer pool first, and then persist the data in memory to disk through processes such as checkpoint and lazy writer. In this process, data-dirty pages are written by memory to the persisted IO subsystem, during which data may pass through these layers, depending on the IO subsystem:

Windows (write data must call the Windows API)

Middle layer of Windows (antivirus, disk encryption system)

network adapters, routers, switches, optical brazing, wire, etc. (if IO subsystem is not straight)

San Controller (if a SAN is used)

RAID controller (IO subsystem made RAID)

Persistent storage such as disk or SSD

As a result, a data page is written to a persistent store and may pass through several items in the preceding list. In the course of the above, the hardware environment will be affected by many aspects, such as voltage stability, power loss, temperature too high or too low, humidity, etc., and software, because the software is written by people, so there may be bugs, which may cause the data page in the transmission process errors.

In addition, factors affecting the disk include voltage stability, dust and other factors, which may also cause disk bad or overall damage.

All of the factors mentioned above can be attributed to the IO subsystem. Therefore, most of the data corruption caused by the IO subsystem, and a very very small probability memory chip can also cause the data page damage, but this part of the situation is negligible, so it is not discussed in this article.

The above mentioned causes of data corruption are natural disasters, there are some man-made disasters. For example, by manually editing data files through an editor, and when there are transactions in the database that require redo and undo (that is, no clean Shutdown), the log files are deleted (often resulting in database queries).

Database corruption found

After we know what might be causing the damage to the database, let's look at how SQL Server monitors database page corruption.

At the database level of SQL Server, you can set the page protection type, with three options: None,checksum,torn_page_detection, as shown in Figure 1:

Figure 1. Three options for page protection