How to Fix SQL Server Database Page Level Corruption?

SQL Server Database is the storage unit of SQL which contains information in the form of pages. The database contains MDF (primary), NDF (secondary) and LDF (log) files. These files have information within

the pages and pages are fixed at some location. Being an SQL Server administrator, you need to be careful of all types of corruption in SQL database. But, it is not possible always. There is a constant risk to your SQL Database and you may encounter a SQL page level corruption error like this –

SQL Server Database Page Level Corruption

This is basically a SQL Server page level corruption error which needs to be rectified in order to access the SQL Server data. The problem is to be solved at page level i.e. one by one and not the entire file. So, it becomes easier to perform the solution process manually. Before going for any particular solution for the corruption errors, let us look at some causes for this page level corruption error.

Reasons for SQL Server database Page Level Corruptions

  • Fault in Hardware – It is the prominent cause for this SQL Server page level corruption. The hardware malfunction leads to the corruption in SQL easily.
  • Irregulation in Power Supply – If while the SQL database is running and the power supply is interrupted due to some faults, it will causes corruption or errors in SQL pages.
  • Wrong updates installation – It is when while you are trying to update the SQL Server with the current version but fails to choose the correct one. In place of updating the SQL Server, it damages the pages with bugs.
  • Virus attack or infections with new software – Upon installing any new software on the system, there is vulnerability that it could carry bugs, viruses with it. Such viruses can infect the SQL Server even while corrupting the SQL Database pages.

These important reasons could be prevented taking care of hardware by periodic checkups and maintenance. Checking and correcting power supply from time to time. Using authentic and licensed update for the SQL Server will help you avoid the corruption issues. Finally, having updated anti-virus software will not let other software harm your SQL Server. So, these are the preventive measures to avoid SQL database page level corruption. But, what to do once you get an error like that mentioned above in the image? The answer is using in-built DBCC CHECKDB command.

Note: Before jumping to any method for solution, you must have a backup copy of your SQL Server database saved at some other location.

Fixing SQL Database Page Level Corruption manually

The manual way which you can use to fix SQL Server database page level corruption is using DBCC CHECKDB command. For the effective solution, you must have the following arrangements:

Step 1. Have a text comparison tool allowing to check the differences between original and corrupted file
Step 2. Run DBCC CHECKDB command on the corrupted file. It will check the database file location where problem actually originates. Also, it uses minimum sources for data repairing.
Step 3. Switch on the trace flag 3604 – run DBCC TRACEON command. Now run DBCC PAGE command. It will examine infected page content.

  • DBCC TRACEON (3604)
  • DBCC PAGE ({ ‘dbname’ | dbid}, filenum, pagenum [, printopt={0|1|2|3}])
  • Here, filenum and pagenum are page ids.

Other parameters are defined below:

  1. the header part of the page will be printed
  2. SQL page header with per row hex dumps
  3. Detailed page header with a full hex dump of the page
  4. Description of per row interpretation of page header

Step 4. Confirm the page number using command ‘SELECT * from dbo.tablename’.
Step 5. With the known location of where you got the corruption, compare it with original page using text comparison tool.
Step 6. This is the final step. Fix the pages once again using above steps and run DBCC CHECKDB command on your restored file. If correctly executed and level of corruption is less, you have overcome SQL Server page level corruption.

Challenges in manual method:

Need enough technical knowledge of SQL Server to run SQL commands without any flaw.
I/O errors will occur because direct editing makes the file vulnerable to faults. Failure in correct copy/paste of checksums on pages leads to failure in opening the database too.
If the area and level of corruption is more, it will automatically damage other pages as well.

Try Professional Solution

Through above limitations or challenges in manual way, it is confirmed that this way is completely unreliable for page level corruption in SQL Server. So, the best recommendation is using third-party SQL recovery tool. The tool is all about fixing SQL based corruptions and errors at a one go flawlessly.

Concluding lines

The article has discussed about the SQL Server database page level corruption, its reasons, manual solution & its limitations, a better approach to fix the error. All these sum up and test the intelligence level of the user. It is definitely up to him that what method he would like to go with. Experience with your decision!

Leave a Reply