How to Repair SQL Database Page Level Corruption?

If you work as an SQL server administrator, your life won’t be bug-free. While the problems you encounter might be few and far-flung, the worst and most worrisome problem of all is the SQL database page-level corruption. Page corruption in SQL Server is akin to a nightmare, threatening the integrity of all vital data stored on the server. Even administrators with full privileges find the data inaccessible, causing a significant disruption to the organisation’s entire workflow. Finding out as much as you can about corruption, its causes, and solutions is the only way out of it.

Reasons Behind Page-Level Corruption in SQL

The basic unit of storage in an SQL database is a page; all database information is stored in the form of pages. SQL Server database files have the MDF and LDF extensions. Essentially, all LDF and MDF files are logically divided into several hundred pages, with each page having its location on the server. In the event of page corruption in SQL Server, each of these pages becomes infected. Because it is easier to fix small chunks of data than the whole file, it is generally recommended to fix individual pages before you try to fix the file.

However, before attempting to fix a problem, identifying its root cause is essential. So why does SQL page-level corruption happen?

1. The number one reason for page-level corruption in SQL is hardware malfunction. If there is a problem with the hard disk, it is the most likely reason for the corruption. Therefore, you must regularly monitor your hard disk, the server box, and other hardware in the system to avoid facing a problem like this.
2. You must ensure that the power supply to the server is regulated. Power supply problems when the database is running can also lead to page-level corruption.
3. Take care while downloading new software to the server. If a virus or malware infects the server, it too could lead to damage.
4. While we know that one of the best practices of maintaining a healthy server is to keep it updated with the latest patches, installing the wrong updates could also, unfortunately, cause corruption.
5. Sometimes, a problem in the server itself that cannot be diagnosed can potentially lead to corruption. There is, unfortunately, nothing that you can do about this, except perhaps keep a regular check on the functioning of the database.

If, due to any of these reasons, you start noticing glitches within the server and suspect corruption, you should take a backup of all data on the database as it is and initiate remedial procedures.

Fixing SQL Database Page-Level Corruption

Again, before starting, take a backup of the original MDF and LDF files. This will give you a reference point from which you can restore the database. Also, download a text comparison tool and a data comparison tool from the Internet. These tools will help you compare the corrupted data side by side with the original data.

Follow the steps:

1. Use a text comparison tool to check the differences between the original file and the corrupted file.
2. Execute the DBCC CHECKDB command on the corrupted file. This command will check your database file, display the location of the problem areas, and suggest the minimum requirements for data repair.
3. Use the DBCC PAGE command to inspect the contents of the corrupted page. Switch on the trace flag 3604 first.

DBCC TRACEON (3604)

DBCC PAGE ( ‘DB_Name’ or DB_ID, File_ID, Page_ID [, Output_Mode = 0 | 1 | 2 | 3 ] )

The filenum and pagenum correspond to page IDs, which are associated with different system tables.

The printopt parameters are 0,1,2, and 3

0 – It will print the page header.

1 – Page header accompanied by per-row hex dumps

2 – page header with complete page hex dump

3 – Page header with detailed per-row interpretation

• To confirm the page number and the physical offset, force a logical consistency error and attempt to read the table by running the following command:

SELECT * from dbo. tablename

• Now that the location of the corruption is confirmed, use the text comparison tool to compare the corrupted file with the latest backup of the original file.
• For ease of understanding, copy the corrupted pages from the infected file and paste them into the text comparison tool.
• Use a data comparison tool to compare the corrupted page with the latest version of the original page. You will be able to view a side-by-side comparison of the data, with anomalies highlighted.
• Fix the pages and run the DBCC CHECKDB command on your restored file.
  If the corrupted data has been properly restored and repaired, the recovered file should be free of any anomalies.

Drawbacks of this Method

• If more than one location or a large area is corrupted, other types of pages may be affected, which will cause SQL Server to be unable to open the file.
• Direct editing of the file is a method that is prone to errors. Checksums are calculated on a page-by-page basis, and if you fail to copy/paste correctly, I/O errors will occur, or you may be unable to open the database.
• This solution will require time, and you’ll need to practice it beforehand to ensure its success.

A Better Approach

To address these challenges, many database administrators (DBAs) opt to use advanced third-party SQL recovery software. One of the most widely used tools for the purpose is Stellar Repair for MS SQL. Using advanced algorithms, this tool has proven to successfully fix all corruptions of SQL Server databases and recover inaccessible objects from MDF and NDF database files. Users have reported that the software carries out the highest level of non-destructive repair algorithm to preserve database integrity. If you’re looking for a dependable and fast solution, this is your best choice.