Page-level corruption in SQL is a dreaded scenario for SQL server experts, posing a significant threat to the essential data housed on the server. Subsequently, vital information may become unreachable. Swift detection and remediation of page-level corruption at its inception are imperative.

Causes of SQL Database Page Level Corruption

What are the common causes of SQL database corruption, particularly at the page level? Let's start by exploring these causes before delving into the diagnosis and resolution procedures.

In an SQL Server database, the fundamental storage unit is the page. All the stored data within the database, found in MDF and NDF files, is organized into pages. When SQL Server experiences corruption, it typically begins at the page level. Consequently, addressing the issue often involves a manual approach, requiring the resolution of individual pages. Multiple factors can contribute to this problem, with the following issues being notable culprits for page-level corruption in SQL databases:

  • Hardware malfunction: Monitoring server, hard disk, and PC components regularly could help in avoiding this issue.
  • Sudden power failure: Power supply to the database should be timely checked when the database is in running state. The unexpected power cut could be a reason for corruption.
  • The infected setup files: Be alert while downloading a new software and check whether the setup files are adequately scanned and are free from virus or malware.
  • Irrelevant update installation: Avoid installing updates that seem trivial as they might cause corruption of SQL database at page level.
Resolving SQL Database Page Level Corruption

In this discussion, we will explore the procedure for rectifying SQL page-level corruption. By utilizing the DBCC CHECKDB command, you can attempt to address SQL corruption at the page level. Prior to commencing the process, it is advisable to create a comprehensive backup of the database files to ensure your safety in the event of any unforeseen issues. Subsequently, follow the instructions provided below:

  1. Check the difference between an original and the corrupted file using a text comparison software.
  2. Execute DBCC CHECKDB command on the damaged file. This command performs a systematic data integrity check and identifies the issues.
  3. Now, execute DBCC PAGE command after switching on the trace flag 3604 (for this run DBCC TRACEON 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:

    0 - the header part of the page will be printed

    1 - SQL page header with per row hex dumps

    2 - Detailed page header with a full hex dump of the page

    3 - Description of per row interpretation of page header
  1. Now, run the command ‘SELECT * from dbo.tablename’. This command is for confirming the page number and.
  2. You now have the information about the site where corruption occurred. Utilize text comparison software to assess it against the original page.
  3. After identifying any issues in the previous step, proceed to rectify the corrupted page, and subsequently, run the DBCC CHECKDB command on the recovered file.
Do you find the DBCC PAGE method challenging?

Executing the DBCC PAGE method can be quite demanding, as it necessitates both experience and a profound understanding of SQL Server. Furthermore, when dealing with severely damaged pages within the SQL Server database, directly altering files could potentially exacerbate existing problems. So, it is recommended to use a third-party tool to repair corrupt MDF/NDF files. The best software advised by IT professionals and experts to resolve SQL page-level corruption is Kernel for SQL Server Recovery. It allows painless SQL Recovery from corrupt MDF/NDF files. This tool restores lost database objects back to a running SQL server. It repairs database files of any version of SQL server.

Concluding Lines

Hopefully, this post will have helped you in resolving page-level corruption issues with SQL server database. Follow any of the methods given here depending on your knowledge and experience. Write us your feedback in the comments section to serve you better.