Fixing Page-Level Corruption in SQL Server Databases: Tools and Techniques

MS SQL users, when opening a database file, often report about receiving error messages suggesting corruption in the pages. Some such error messages are:

2015-08-05 16:51:18.90 spid17 Error: 823, Severity: 24, State: 2 

2015-08-05 16:51:18.90 spid17 I/O error (bad page ID) detected during read at offset 0x00000094004000 in file 'c:\Program Files\Microsoft SQL Server\MSSQL\data\tempdb.mdf'

Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID -8573858375060684800 (type Unknown), page (0:13887752). Test (IS_OFF (BUF_IOERR, pBUF->bstat)) failed. Values are 12716041 and -14.

Page-level corruption in SQL database can occur due to various reasons, such as sudden server shutdown, software bugs, virus/malware infection on the system hosting the database file, hardware failure, and others. In this article, we'll learn how to check and fix page-level corruption in MS SQL database.

How to Identify Page-Level Corruption in MS SQL Database?

Let's first see how to detect corruption in pages in SQL database.

Verify the Suspect Pages Table

In MS SQL Server, all suspect page logs are maintained in the suspect_pages_table in the msdb database. This table contains all the corrupt pages (up to a limit of 1,000 rows) in the database that are reported by SQL queries. You can run the below command to check any corrupted pages in the database:

SELECT * FROM msdb..suspect_pages

Use DBCC CHECKDB Command

You can also use the DBCC CHECKDB command to check for corruption in pages, tables, and indexes in the database. If the command finds any inconsistencies, it will return errors, along with description. Here's how to run this command:

DBCC CHECKDB 'database_name';

Use DBCC PAGE Command

There is an undocumented command - DBCC PAGE that you can also use to detect corrupt pages in a database. To run this command, first you need to set the TRACEON flag 3604. Here's how to do so:

DBCC TRACEON (3604)
DBCC PAGE('Recovery_test',1,368,2) WITH TABLERESULTS

Methods to Fix Page-level Corruption in SQL Server Database

If you find any page-level corruption in MS SQL database, then follow the below methods to fix the corruption and recover the database.

Method 1 - Perform Page-Level Restore

If you want to restore only individual damaged/corrupt pages instead of restoring the complete database, then you can use the Page Restore option. This applies to the databases created with full or bulk-logged recovery system. Here are the steps to restore pages:

1. Open SQL Server Management Studio (SSMS) and then connect to your SQL Server instance.
2. In the Object Explorer, expand the Databases node, right-click on the corrupted database, and select Tasks > Restore > Page. This will display the Page Restore window.
   
3. In the Page Restore window, check the selected database, backup file, backup set, and other required details.
4. To detect the corrupt pages, click the option labeled - Check Database pages. When the process is completed, it will display the pages grid. Here, you can identify the pages to be restored.
5. Use the Add and Remove button to add or delete the pages.
6. Then, click OK. It will restore the pages listed in the grid of the page.

Note: This method is ideal for restoring a few individual pages. If you have multiple pages to restore, then it is a time-consuming process. Also, it may not restore the transactional log, Page 0 of all databases, and Allocation pages.

Method 2 - Use DBCC CHECKDB Command

If multiple pages are corrupted, then you can use the DBCC CHECKDB command to repair the database. It can restore Allocation pages, including Allocation maps (like Page Free Space, Global Allocation Map, Shared Global Allocation Map, etc.). Here is how to use this command:

1. First, you need to set the database in single-user mode. For this, use the following command:
   ALTER DATABASE Dbtesting SET SINGLE_USER
2. Then, execute the below command with the REPAIR_ALLOW_DATA_LOSS option to repair the database:
   DBCC CHECKDB (N 'Dbtesting', REPAIR_ALLOW_DATA_LOSS) WITH ALL_ERRORMSGS, NO_INFOMSGS; GO
3. Now, reset the database to multi-user mode by using the below command:
   ALTER DATABASE Dbtesting SET MULTI_USER

Method 3 - Use a Professional MS SQL Repair Tool

To repair corrupt database quickly and with complete integrity, you can use a professional SQL repair tool, like Stellar Repair for MS SQL. This repair tool can efficiently repair both MDF and NDF files without any file size limitations. It can restore all the database objects, including pages, allocation pages, and allocation gaps, with complete precision. It also helps you preview the recovered data and save it in a new database file or other file formats.

Conclusion

Above, we've explained the methods to fix page-level corruption in MS SQL Server database. You can use the PAGE RESTORE command or DBCC CHECKDB command to restore pages in the database. However, these methods have some limitations. To overcome these limitations, you can use an advanced MS SQL repair tool, like Stellar Repair for MS SQL. The tool can restore all the data from the damaged database file while preserving the actual formatting.