Summary: In this post, we will talk about the root cause behind page level corruption in SQL Server. We’ll also discuss the ways to fix the corruption and restore the database. Also, you’ll find about a SQL database repair software that can easily repair a corrupt database and restore all its objects.
Contents
Databases can get corrupted at any time and it is a worrisome problem for database administrators as it puts all the crucial data on the SQL server at risk. Also, the data becomes inaccessible, impacting the workflow of the organization. Thereby, fixing the corruption immediately is important. However, it is equally important to understand the causes of corruption, otherwise, it will reoccur. This blog covers essential information on how to fix page corruption in SQL server.
Causes Behind SQL Server Page Level Corruption
Here are some major reasons that can lead to SQL Server database corruption.
- Software Bug within SQL Server Program: In this case, the data is badly written to the disk. Look at these Microsoft documents:
We can avoid this type of problem by installing the latest cumulative update (CU) of the SQL Server version.
- Memory Problems: We are talking about hardware problems that can affect the entire system and not just the MS SQL Server. There are programs to verify the correctness of the data in memory.
- I/O Subsystem Problems: In this case, understanding the cause of the problem is not easy because the storage system may have a complicated architecture. For example, before we write to the physical disk, we may have to cross a network or we may have a disk controller and its drivers. Any of these components may fail.
- Sudden Server Shutdown: 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 lack of electricity.
How to Detect Page Level Corruption in SQL Server?
MS SQL Server databases consist of 8 KB pages of data. Eight physically contiguous pages make up an extension, which is 64 KB in size. Page corruption means that we have one or more pages that contain incorrect values.
If we have a corrupted database, we can check this in three ways:
1. Executing SELECT statement on the corrupted table shows an error
In this case, the error message shows:
- The type of error (here, we have a logical consistency based I/O error, caused by an incorrect checksum).
- The page where the error is found (page 1:368).
- The offset where the error is found (0x00000002e0000).
- The ID of the corrupted database (ID=31).
2. Execute the DBCC CHECKDB statement
This command checks the physical and logical integrity of database objects, index relationships, and other structural checks. Just type DBCC CHECK with the name of the database in round brackets.
In this case, the error shows:
- The number of corrupted pages (page 1:368).
- The name of corrupted table (person).
3. Execute SELECT on the [msdb].[dbo].[suspect_pages] table
![Execute SELECT on the [msdb].[dbo].[suspect_pages] table](https://www.stellarinfo.com/blog/wp-content/uploads/2022/06/SQL3.png)
The SELECT command will return a grid with a row of each corrupted page. In this case, we can see that on the database with ID = 31 the page 1:368 is corrupted.
How to Fix Page Level Corruption in SQL Server?
Here we have mentioned two different ways to repair and recover the database.
1. Manual Recovery via T-SQL Commands
Note: Make a backup of the databases and/or copy of the physical files before proceeding. The manual process is risky.
To proceed with the recovery manually via T-SQL commands to fix page corruption in SQL server, follow the below instructions.
Use the undocumented DBCC IND command to make sure that page 368 is a data page.
DBCC IND('Recovery_test',Person,-1)This command shows all the pages relating to the table, indicated in the second parameter, with the information relating to the type of page. In below example,
- The first row has page type 10. So, it is the Index Allocation Map (IAM) page.
- The second row has page type 1. It indicates page 368 contains data.
To display the data contained in page 1:368, we can open the recovery_test.mdf with a Hex editor and go to the 2e0000 offset.
The red character ?b? (see below image) look suspicious since my name is ?Luca? and not ?Lucb?.
2. Use the command DBCC PAGE:
To get the result from the DBCC PAGE command, Trace flag 3604 must first be set.
DBCC TRACEON (3604)DBCC PAGE('Recovery_test',1,368,2) WITH TABLERESULTSThe required parameters are:
- Name of the database
- Number of the file
- Number of the page
- PRINTOPT
Values of the printopt parameter 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 details with entire hex dump of the page.
- 3 ? Header of the page with description of per row interpretation.
The data shown is the same.
We need to change the character value from the Hex value 62 (b) to the Hex value 61 (a).
We can use another undocumented command, which is the DBCC WRITEPAGE command. This is a powerful command that allows to write directly on a page.
To use the DBCC WRITEPAGE command, we need the following information:
- The first parameter is the database name.
- The second and third are the fileId and the PageId that I must change (1: 368).
- The fourth parameter is the offset (hex.74 -> 116 dec.) and the fifth is the number of characters we are going to write (1).
- The sixth parameter is the value that I must write. In our case, it is 0x000000.
- The seventh parameter specifies whether to write the value through the buffer pool. We set the value 1 to write directly to the physical mdf file.
First, put the database in SINGLE_USER mode:
ALTER DATABASE recovery_test SET SINGLE_USER WITH ROLLBACK IMMEDIATEThen, execute the WRITEPAGE command:
DBCC WRITEPAGE ('recovery_test', 1, 368, 116, 1, 0x61,1)Now, run it again to see if the data has changed:
DBCC TRACEON (3604)DBCC PAGE('Recovery_test',1,368,2) WITH TABLERESULTSYes, the data has changed.
Set the database in MULTI_USER mode:
ALTER DATABASE recovery_test SET MULTI_USER WITH ROLLBACK IMMEDIATECheck the health state of the database:
DBCC CHECKDB ('recovery_test')From the log, we can see that there are no errors. It means that the database is no longer corrupt.
Repair the Database by using Stellar Repair for MS SQL
Let?s consider, that the problem we?ve solved manually is a remarkably simple case. But repairing complex corruption cases requires advanced knowledge and involves risk of more data damage. However, a dedicated application such as Stellar Repair for MS SQL can solve the same problem in a safe and quick way. Just a few simple steps are required to recover a corrupt database. These are:
- Launch the Stellar Repair for MS SQL application.
- At startup, the application asks you to select which database to recover. Indicate a file with the .mdf extension.
- Then you can choose to recover deleted records as well.
- Press the Repair button.
- There are two modes of recovery: Standard and Advanced.
The database structure will be scanned for repair. A progress bar shows the progress of the job.
- The result of this scanning phase is saved. So, you can reuse it. From the graphics interface, we can see all database objects.
- The last step is to save the recovered data. We can save the data into a new database, an existing database, or in another format (CSV, XLS, or HTML).
- When you select the New Database option, you will be asked for the name of the database to create. The data will be copied into the generated database.
To Conclude
The SQL database gets corrupted due to a variety of reasons. In this post, we have discussed the causes behind page-level corruption in SQL Server. We have also seen how to detect corruption in the database and how to repair the corrupted database manually via T-SQL commands and using SQL repair software. The software can do a commendable job of resolving the issue and getting the database back to a perfect running state.
