In this post, we will see the different types of database corruption that can occur in the MS SQL Server database and the possible ways to resolve them.
Types of Database Corruption in SQL
There are several types of corruption that can occur in an MS SQL Server database.
- Boot Page Corruption
- Page-Level Corruption
- Page Header Corruption
- Index Corruption
Let us discuss them in detail.
Boot Page Corruption
The boot page is a special page as it contains essential information about the database, such as the compatibility level, database ID, etc. Only one boot page exists for a database and is stored in page 9 in the first file in the primary filegroup.
SQL Server reads the boot page of every database when the instance starts and then logs an entry in the error logs writing when the last CHECKDB is run.
SQL Server also accesses the boot page during the “redo” phase of the recovery process. In this case, the boot page is read to get the minLSN value. This value represents the oldest uncommitted transaction at the point of a crash. Starting from this point, SQL Server writes the changes to the data files.
If SQL Server cannot read the boot page (or the minLSN value is not correct), the crash recovery cannot be made and the database cannot come online.
How to Solve this Problem?
There are no ways to fix the boot page corruption. Even DBCC CHECKDB does not fix boot page issues. However, if you have a recent backup, you can use that.
If you have a backup that you cannot restore because the data is too old, then you can compare the boot page of the database with the boot page of the corrupt one.
If you are lucky that some bytes contain “strange” values, you can try a manual procedure through T-SQL commands.
The main idea is to replace the wrong values inside the database with the corrupted boot page with the value read from the database without problems.
Here’s the procedure:
Put (a copy of) the corrupted database in single-user mode.
ALTER DATABASE corrupted_db SET SINGLE_USER WITH ROLLBACK IMMEDIATE
Read the right value from the uncorrupted database and write it through the WRITEPAGE command:
DBCC WRITEPAGE (‘corrupted_db‘, 1, 9, 116, 1, 0x61,1)
In this example, we are putting on page 9, the value 0x61, and the offset 116.
Now, set the database in MULTI_USER mode:
ALTER DATABASE corrupted_db SET MULTI_USER WITH ROLLBACK IMMEDIATE
Maybe you need to run the DBCC CHECKDB command with the repair_allow_data_loss option. Alternatively, you can take the database offline so you can open the MDF file in a hex editor and edit values.
If you are not lucky and unable to fix the corruption, then you can use dedicated software, like Stellar Repair for MS SQL. This software knows the format used to store data in MDF files. It can parse the MDF files and save the data in a new database. Since the data is not corrupted, it will be perfectly copied to a new database.
Page-Level Corruption
The term page-level corruption refers to the fact that a page in the database is corrupted. The main reasons that can lead to such corruption are:
- Presence of a software bug within the SQL Server program. Refer to the following Microsoft documents:
This type of problem can usually be avoided by installing the latest CU of the SQL Server version.
- Corruption may also occur due to memory problems.
- The database may get corrupt due to the I/O subsystem problems.
- There is a substantial risk of database corruption if for any reason the server suddenly goes down. This can happen due to physical failure of the server or due to a lack of electricity.
You can detect page corruption by running the DBCC CHECKDB command. This command checks the physical and logical integrity of database objects, index relationships, etc.
In case of errors, the log will show:
- The number of corrupted pages (page 1:368).
- The name of the corrupted table (person).
How to Fix this Problem?
You can perform manual recovery via T-SQL commands. Remember that this procedure is risky and does not guarantee that the problem will be resolved.
In any case, you can proceed manually by writing directly into the database page using the undocumented DBCC WRITEPAGE command.
DBCC PAGE(‘Recovery_test‘,1,368,2) WITH TABLERESULTS
Required parameters are:
- Name of the database
- File number
- Page number
- Print option
Values of the print option parameters are:
- 0 – It will print the header part of the page.
- 1 – Header of the SQL page with per row hex dumps.
- 2 – Page header detail with the entire hex dump of the page.
- 3 – Header of the page with a description of per-row interpretation.
To get the result from the DBCC page, trace flag 3604 must first be set.
Alternatively, you can use specialized software, such as Stellar Repair for MS SQL.
Page Header Corruption
The term page header corruption refers to the fact that the header of a page is corrupted. MS SQL Server databases consist of 8 KB (or 8192 Bytes) data pages. Each page has a 96-byte long page header.
Look at the page header with the DBCC PAGE command. The data contained in it is essential.
For example, you can see the m_pageId and the m_type fields.
- The M_pageId represents the id value of the page.
- The M_type represents the page type.
Values for this field are many. In this case, 1 means that the page contains data. It is a page that contains the index, an IAM page, etc.
All this to say if data inside a page header is corrupted, it is a problem.
In this case, you can use specialized software, like Stellar Repair for MS SQL, to fix the header corruption.
Index Corruption
The term index corruption refers to the fact that a non-clustered index is corrupted. A non-clustered index gets corrupted due to the same problems that can afflict all the other objects stored in the MDF files. Additionally, a software bug can cause this problem.
You can refer to the Microsoft knowledge base:
This could happen in Microsoft SQL Server 2008, SQL Server 2012, SQL Server 2008 R2, or SQL Server 2014. In this case, you need to update your SQL Server to the latest cumulative update.
You can notice a corrupt index when you make a selection that uses it and returns an error. The good thing is that a corrupted non-clustered index can be rebuilt.
Let us see how.
Using T-SQL
You can run the ALTER INDEX REBUILD command to rebuild the non-clustered index:
ALTER INDEX <INDEX_NAME> ON <TABLE_NAME> REBUILD
The index will be dropped and recreated.
From the SSMS
Right-click on the index name and click on the rebuild item.
You can also rebuild all the indexes of a table by right-clicking on the Indexes voice and then Rebuilding all.
Then, execute the DBCC CHECKDB command to make sure the index problem is resolved.
It is always possible to use Stellar Repair for MS SQL as mentioned earlier.
To Conclude
In this post, we have discussed the types of corruption that can occur in an MS SQL Server database. In addition, we’ve provided possible solutions to fix corruption in the SQL database. In case manual methods do not work, you can use an advanced SQL Recovery Tool, such as Stellar Repair for MS SQL to repair the database.
