How to Fix Page Corruption in SQL Server?

Page is the primary storage unit in SQL Server. All the information in SQL Server database (MDF/NDF) files is stored in pages of 8 KB size. Each page starts with a 96-byte header that stores information, like page number, page type, free space etc. If a page gets corrupted, then it can impact the entire database. In this case, you may receive different errors when opening the database file or even not able to access the database. Therefore, fixing page-level corruption immediately is important. In this article, we will see how to fix page corruption in SQL Server database.

How to Detect Corruption in SQL Server Database?

Before proceeding to the solutions to fix corruption, you need to check the SQL database for corruption. There are various ways to check page-level corruption in SQL Server database. Some of them are explained below:

1. Run the SELECT Statement

SQL Server keeps a log of all the suspect pages in the suspect_pages table in msdb (system) database, containing all the corrupt pages that are reported by SQL queries. You can check the suspect_pages table to see the corrupted pages in the database. For this, you can run the SELECT statement as given below:

SELECT * FROM msdb..suspect_pages

The above command will return a grid with a row of each corrupted page. In our case (see the above image), you can see that the page, with id 368, is corrupted.

2. Execute DBCC CHECKDB Command

The DBCC CHECKDB command checks physical and logical integrity of database objects and index relationships. You can use the DBCC CHECKDB command, as given below, to check for corruption in the SQL database.

DBCC CHECKDB ‘database_name’;

If the above command finds corruption in the database, it will display consistency errors, along with the possible option to repair the database.

3. Use CHECKSUM Option

You can also use the CHECKSUM option to detect page-level corruption in SQL database, if the PAGE_VERIFY option is set to CHECKSUM. CHECKSUM is a database option that defines the process in SQL Server to check page consistency when it is written to the disk and read again from the disk. To check page-level corruption in the SQL database, run the following command:

SELECT name, page_verify_option_desc FROM sys.databases

4. Run DBCC PAGE Command

The DBCC PAGE command is an undocumented command that is used to detect corrupt pages in a database. To get the result from the DBCC PAGE command, you need to set the Trace flag 3604. Here is the command:

DBCC TRACEON (3604)

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

The command output will help you understand what kind of corruption has occurred and what type of data is affected.

Solutions to Fix Page Corruption in SQL Server Database

After checking the database by using any of the above methods, if you find any page-level errors or corruption in SQL database, then follow the below methods to repair the database and recover the data.

1 – Perform Page Level Restore

You can perform “Page Restore” to restore only the corrupt/damaged pages, without restoring the entire database. This is a faster way when you need to restore only a few individual pages. The page restore applies to databases that use full or bulk-logged recovery system. You can restore pages by using the SQL Server Management Studio or T-SQL commands. To restore pages using SSMS, follow the below steps:

• Open SSMS and connect to your SQL Server instance.
• In the Object Explorer, expand the Databases node, right-click on the affected database, and select Tasks > Restore > Page. This will open the Page Restore window.

• In the Page Restore window, check the selected database, backup file, backup set, and other required details.

• To identify the corrupt pages, click Check Database pages. This may take some time.
• When the process is finished, it will display the pages grid. Here, you can identify the pages to be restored.
• Use the Add and Remove button to add or delete the pages.
• Next, click OK to restore the pages listed in the pages grid.

Alternatively, you can use the T-SQL command – RESTORE DATABASE (see the below example) to restore the page

Note: You must know the File ID where the page is located and Page ID of the corrupted page.

RESTORE DATABASE <database_name> 

 PAGE = '<file: page> [ ,... n ] ' [ ,... n ]

 FROM <backup_device> [ ,... n ]

 WITH NORECOVERY

Limitations of the above method:

• Restoring multiple pages using Page Restore is a time-consuming process.
• It can restore only database pages. It does not restore Transaction log, Allocation pages, Page 0 of all data files (the file boot page), Page 1:9 (the database boot page), and Full-text catalog.
• It only supports databases that use the full or bulk-logged recovery model. 

2 – Use DBCC CHECKDB Command

If multiple pages are corrupted, then you can use the DBCC CHECKDB command to repair the corrupt SQL database. You can use the REPAIR_ALLOW_DATA_LOSS option with the command.

First, you need to set the database in single-user mode by running the below command:

ALTER DATABASE Dbtesting SET SINGLE_USER

Then, run the below command to repair the database:

DBCC CHECKDB (N ’Dbtesting’, REPAIR_ALLOW_DATA_LOSS) WITH ALL_ERRORMSGS, NO_INFOMSGS;

GO

After repairing the database, set it to multi-user mode by using the below command:

ALTER DATABASE Dbtesting SET MULTI_USER

Limitations of  the above method:

• It may deallocate rows, pages, or series of pages, when repairing the database. This may result in some data loss.
• You may require to run this command several times to resolve the issues, which is a time-consuming process.

3 – Use a Professional SQL Repair Tool

If the above methods fail, you can use a professional SQL database repair tool to repair the corrupt database. Stellar Repair for MS SQL is one such tool that can quickly repair the corrupt SQL database without any data loss. It can repair and restore all the database objects, including tables, pages, views, keys, and indexes, with complete precision.  

Some key features of Stellar Repair for MS SQL:

• Supports PAGE compressed data.
• Repairs both MDF and NDF files.
• Restores all the data from damaged SQL database with complete integrity.
• Supports repairing SQL databases on both Windows and Linux systems.
• Supports MS SQL Server 2022 and lower versions.
• Allows to save the repaired file in multiple formats – live database, new database, CSV, and XLS.
• Helps to resolve complex corruption-related errors.

Watch the below video to know how the software works:

To Conclude

There are various reasons that can cause page-level corruption in SQL Server databases. Above, we have discussed how to detect page-level corruption in database. If there is corruption in a few pages, you can use the page restore method to restore the corrupted pages. If multiple pages are corrupted, you can use the DBCC CHECKDB command to repair the corrupt database. However, both these methods have some limitations. Alternatively, you can use a professional SQL repair tool, such as Stellar Repair for MS SQL. It can easily repair highly damaged SQL database files and restore all the data, including pages, with complete precision.

Was this article helpful?

YES0

NO

About The Author

Priyanka Chauhan

Priyanka is a technology expert working for key technology domains that revolve around Data Recovery and related software's. She got expertise on related subjects like SQL Database, Access Database, QuickBooks, and Microsoft Excel. Loves to write on different technology and data recovery subjects on regular basis. Technology freak who always found exploring neo-tech subjects, when not writing, research is something that keeps her going in life.