How to Find Corrupt Page In SQL Server and Encounter It?

Summary: Managing a database is a bit challenging when the admin and regular users face errors and interruptions frequently. It is always better to take precautionary measures so that unnecessary interruption can be avoided. But, even after taking measures, some errors are not avoidable, one of them is page-level corruption. As the SQL database is prone to corruption, so let’s discuss How to Find Corrupt Page In SQL Server and fix it efficiently using manual methods and professional SQL Database Recovery software.
Download Now Purchase Now

Root causes of SQL Database Page Level Corruption

All the information in SQL server database is stored in pages and database files LDF and MDF are divided into several hundred pages and each page in the SQL server has its location. And, if there is any kind of issue occurs in pages, it leads to page level corruption.

• Whenever there is a problem with hardware, the issue in SQL database occurs.
• Also, sudden power cut can create a problem.
• Virus/Malware attack affects the whole server if there is no updated database.
• Any kind of problem in the server if not diagnosed in time, then it leads to SQL corruption.

It is always better to have a backup whenever you see any kind of problem and take immediate steps to fix it.

Also Read: How to Fix SQL Database in Recovery Mode?

How to Find Corrupt Page In SQL Server  and Fix Manually?

To fix SQL server database you need to give DBCC CHECKDB command and have a full backup of the database before taking these measures. So, that you have your backup in case any kind of problem occurs in between.

Steps to be taken to fix SQL Server Page Level Corruption

• First, check differences between original and corrupted file by using a text comparison tool.
• Now, run the DBCC CHECKDB command on the corrupted file.
• This command will present problematic areas in your file.
• Now, run DBCC PAGE command immediately after switching on the trace flag 304.

It will display the affected content which needs to be correct.

Filenum and pagenum are page ids here and other print parameters are as follows:

0-This will print page header.

1-It is page header with per row hex dumps

2-Page header with complete page hex dump.

3-Page header with detailed per row interpretation.

• Now, run the command ‘SELECT * FROM dbo.tablename’. This will confirm the page number.
• After the location of the corruption is confirmed, use the text comparison tool to compare the latest backup of the original file.
• At last, fix the corrupted page and keep going on with the command ‘DBCC CHECKDB’ on the restored file.

Limitations of this method

• Many times other pages are also affected which will cause SQL database to be unable to open that file.
• Any mistake in copy/paste will cause I/O errors which will lead to inaccessibility of the database.
• Not easy to follow by novice users.

An alternative approach to Fix SQL Page Level Corruption

You need to be really good at working in the SQL Server database, to execute these commands. So, to fix your MS SQL database I will recommend you to use SQL Database Recovery software. This will allow you to fix SQL Page Level corruption in the more efficient way and repair all database files MDF, LDF easily without any alteration of data.

Conclusion

This is all about How to Find Corrupt Page In SQL Server?. As per your skills on SQL database you can opt for any approach to fix corruption in SQL database. It totally depends on whether you are able to properly execute commands and level of corruption.

Related Post