SQL page-level corruption is one of the worst nightmares for any SQL server professional as this can harm all the crucial data stored on the server. And consequently, important data can become inaccessible. It is important to act immediately to detect and fix page level corruption at an early stage.
Causes of SQL Database Page Level Corruption
How does SQL database get corrupted? Let us first understand the major causes of SQL database page level corruption and later move on to the diagnosis or resolution process.
A page is the basic storage unit of SQL server database. The database files (MDF and NDF) carries all the saved information in the form of pages. When SQL server corruption takes place, it starts at the page level first. Therefore, it becomes an easy task to solve it manually by fixing each page one by one. There are many reasons due to which this problem occurs. Below listed are some issues that cause SQL database page level corruption:
- 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.
Here will discuss the method for correcting SQL page-level corruption. With the help of DBCC CHECKDB command, you can try to fix page level SQL corruption. Before starting the process take a full backup of the database files to keep yourself on a safer side if anything goes wrong. Then follow the instructions below:
- Check the difference between an original and the corrupted file using a text comparison software.
- Execute DBCC CHECKDB command on the damaged file. This command performs a systematic data integrity check and identifies the issues.
- 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
- Now, run the command ‘SELECT * from dbo.tablename’. This command is for confirming the page number and.
- Now, you know the location where corruption has taken place. Use the text comparison software to compare it with original page.
- Finally, fix the corrupted page after detecting flaws from the previous step and then, execute the DBCC CHECKDB command on the restored file.
The DBCC PAGE method is challenging as it requires experience and in-depth knowledge of the SQL Server to execute it efficiently. When some pages of the SQL server database are highly damaged, making direct changes to files may lead to further issues as well. 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.
