In the world of SQL Server management, page-level corruption is an issue that can strike fear into the hearts of even the most experienced database administrators. When an 8 KB unit of data storage, known as a page, becomes corrupt, it can lead to data loss, hindered performance, and disruptions to your database operations.
This article delves into the intricacies of page-level corruption in SQL Server, its underlying causes, and the most effective solutions to resolve it with ease. Whether you're a seasoned DBA or just starting out, understanding and tackling page-level corruption is an essential skill.
Unveiling Page-Level Corruption:
A page in SQL Server is a unit, usually of 8 KB size, used to store data, such as tables, indexes, log files, etc. You can save the pages on hard drives. However, sometimes, the pages get corrupted or damaged due to different reasons. The following are some typical reasons for page corruption in SQL Server:
- Problems in hardware: If a hardware fails, the pages can be damaged. For example, a power outage can damage the hard drive.
- Software problems: Sometimes, a problem in software can damage the database. It can be a bug in the operating system or SQL Server.
- PEBCAC problem: PEBCAC stands for Problem Exists between Chair and Computer. It means it is related to user. For example, the user has deleted the data accidentally, resulting in the database page corruption.
- Viruses and malware: A virus or malware can corrupt SQL Server pages.
Efficient Solutions to Combat Page-Level Corruption:
Now that you're acquainted with the origins of page-level corruption in SQL Server, let's explore the strategies to efficiently rectify these issues within your SQL Server database.
1. Restoring from Backup:
If the database is small or only a few pages are damaged, you can restore the database from the last backup. Here’s how:
- Open SQL Server Management Studio (SSMS).
- In the Object Explorer, right-click the Databases node and select Restore Database.
- Select the appropriate options and press OK to restore the database.
2. Targeted Page Restoration:
If your database is big, you can only restore the specific damaged page. Here are the steps to do so.
- In the Object Explorer, right-click the database and select Task > Restore > Page.
- Press the Add button and specify the File ID and the Page ID. You also need to select the backup file.
3. The Power of DBCC WRITEPAGE:
Another way to resolve the problem is by using the DBCC WRITEPAGE command. If you detect any error in the page, you can manually write on the page.
To visualize the data, you can enable the Trace flag 3604. To enable the flag, run the following command:
DBCC TRACEON (3604)
To check the values of the page, use the DBCC PAGE command as given below:
DBCC PAGE('stellardb',1,1001,2) WITH TABLERESULTS
The command checks the page 1001 of the stellardb database with the file number 1 and shows the page header details (2).
To manually modify the file, run these commands:
ALTER DATABASE stellardb SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DBCC WRITEPAGE ('stellardb', 1, 1001, 116, 1, 0x61,1)
ALTER DATABASE stellardb SET MULTI_USER WITH ROLLBACK IMMEDIATE
You need to first set the database in single-user mode. Then, write the database stellardb in the file ID equal to 1. The Page ID is 1001. The offset in bytes on the page to be written is 116. The length of the number of bytes to write is 1. 0x61 is the data to write. The last number 1 means to write in the database directly.
Once modified, set the database to multi-user mode.
4. Leveraging DBCC CHECKDB:
The following sentences allow you to repair the database in case of page level corruption.
ALTER DATABASE stellardb SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
DBCC CHECKDB (stellardb, REPAIR_ALLOW_DATA_LOSS);
ALTER DATABASE stellardb SET MULTI_USER;
You need to set the database to single user mode, repair the data, and then set the database to multi-user mode again. It is to be noted that DBCC CHECKDB may result in loss of data.
5. Employ Third-Party SQL Repair Software:
One of the best and easiest options to repair the corrupt database is by using a third-party SQL repair software, such as Stellar Repair for MS SQL. This software can easily repair the corrupt database and recover all its objects. Here’s how to use the software:
- Download the software and install it.
- Once installed, take the database offline via SSMS (Go to the Object Explorer, right-click the database, and select Tasks > Take Offline).
- Launch Stellar Repair for MS SQL.
- Press the Browse button and select the .mdf file. If you do not know where the file is located, use the Find button.
- You will be prompted to select the Standard Scan or the Advanced Scan. Select the Standard Scan. The Advanced option is used if you have problems with the Standard option.
- Once the scan process is finished, you can save the data in a New Database, Live Database, or other formats like CSV, Excel, or HTML.
Conclusion:
Page level corruption in SQL Server database can occur due to different reasons. Above, we have discussed different ways to fix page level corruption in SQL database. However, the manual methods are quite tedious and time-consuming, and may also result in data loss. Alternatively, you can use Stellar Repair for MS SQL to repair the database and restore all the data with complete integrity.
