{"id":100165,"date":"2022-06-07T11:04:22","date_gmt":"2022-06-07T11:04:22","guid":{"rendered":"https:\/\/www.stellarinfo.com\/blog\/?p=100165"},"modified":"2024-11-04T12:27:24","modified_gmt":"2024-11-04T12:27:24","slug":"how-to-fix-page-corruption-in-sql-server","status":"publish","type":"post","link":"https:\/\/www.stellarinfo.com\/blog\/how-to-fix-page-corruption-in-sql-server\/","title":{"rendered":"How to Fix Page Corruption in SQL Server?"},"content":{"rendered":"

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.<\/p>

How to Detect Corruption in SQL Server Database?<\/h2>

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:<\/p>

1. Run the SELECT Statement<\/strong><\/h3>

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:<\/p>

SELECT * FROM msdb..suspect_pages<\/code><\/pre>
\"result<\/figure>
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.<\/p>
2. Execute DBCC CHECKDB Command<\/strong><\/h3>
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.<\/p>
DBCC CHECKDB ‘database_name’;<\/code><\/pre>
\"check<\/figure>
If the above command finds corruption in the database, it will display consistency errors, along with the possible option to repair the database.<\/p>
3. Use CHECKSUM Option<\/strong><\/h3>
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:<\/p>
SELECT name, page_verify_option_desc FROM sys.databases<\/code><\/pre>
4. Run DBCC PAGE Command<\/strong><\/h3>
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:<\/p>
DBCC TRACEON (3604)<\/code><\/pre>
DBCC PAGE('Recovery_test',1,368,2) WITH TABLERESULTS<\/code><\/pre>
\"result<\/figure>
The command output will help you understand what kind of corruption has occurred and what type of data is affected.<\/p>
Solutions to Fix Page Corruption in SQL Server Database<\/h2>
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.<\/p>
1 – Perform Page Level Restore<\/strong><\/h3>
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:<\/p>