Summary: Corruption in a SQL database can occur at any time. What is important is detecting it early and resolving it as soon as possible. To identify page-level corruption in the SQL Server database, you must know the reasons that can trigger it. Read the blog to learn about the root cause behind page-level corruption. Also, check out how you can fix SQL database page-level corruption manually or by using Stellar Repair for MS SQL software. The software can do a commendable job of resolving the issue and getting the database back to a perfect running state.
Contents
If you work as an SQL server administrator, your life won?t exactly be bug-free. While the problems you encounter might be few and far-flung, the worst and the most worrisome problem of all is the SQL database page level corruption. Equivalent to a nightmare, page corruption in SQL server jeopardizes all the crucial data saved on the server. The data becomes inaccessible even to administrators (with full privileges), thereby creating an obstruction in the workflow of the entire organization. Finding out as much as you can about corruption, its causes, and solutions is the only way out of it.
Reasons Behind Page Level Corruption in SQL
The basic unit of storage in an SQL database is a page; all database information is stored in the form of pages. SQL Server database files have the MDF and LDF extensions. So basically, all LDF and MDF files are logically divided into several hundred pages and each individual page has its own location on the server. In case of page corruption in SQL server, each of these pages starts getting infected. Because it is easier to fix small chunks of data, than the whole file, it is generally recommended to fix individual pages before you try to fix the file.
But before starting to fix a problem, getting to its root is essential. So why does SQL page-level corruption happen?
- The number one reason for page level corruption in SQL is hardware malfunction. If there is a problem in the hard disk, it is the most likely reason for the corruption. Therefore, you must regularly monitor your hard disk, the server box, and other hardware in the system to avoid facing a problem like this.
- You must ensure that the power supply to the server is regulated. Power supply problems when the database is running can also lead to page-level corruption.
- Take care while downloading new software to the server. If the server is infected by a virus or a malware, it too could lead to damage.
- While we know that one of the best practices of maintaining a healthy server is to keep it updated with the latest patches. However, installing the wrong updates could also, unfortunately, cause corruption.
- Sometimes, a problem in the server itself that cannot be diagnosed can potentially lead to corruption. There is, unfortunately, nothing that you can do about this, except perhaps keeping a regular check on the functioning of the database.
If due to any of these reasons you start noticing glitches within the server and suspect corruption, you should take a backup of all data on the database as it is and initiate remedial procedures.
Fixing SQL Database Page-Level Corruption
Again, before starting take a backup of the original MDF and LDF files. This will give you a reference point from which you can restore the database. Also, download a text comparison tool and a data comparison tool from the Internet. These tools will help you see a side-by-side comparison of the corrupted data and the original data.
Follow the below-mentioned steps:
- Use a text comparison tool to check the differences between the original file and the corrupted file.
- On the corrupted file, run the DBCC CHECKDB command. This command will check your database file, display the location of the problem areas, and suggest the minimum requirements for data repair.
- To examine the contents of the infected page, run the DBCC PAGE command. Switch on the trace flag 3604 first.
DBCC TRACEON (3604)
DBCC PAGE ( {?dbname? | dbid}, filenum, pagenum [, printopt={0|1|2|3} ])
The filenum and pagenum are related to page IDs and they are related to various system tables.
The printopt parameters are 0,1,2 and,3
0 – It will print the page header.
1 – Page header with per row hex dumps
2 – page header with complete page hex dump
3 – Page header with detailed per row interpretation
- To confirm the page number and the physical offset, force a logical consistency error and attempt to read the table by running the following command:
SELECT * from dbo.tablename
- Now that the location of the corruption is confirmed, use the text comparison tool to compare the corrupted file with the latest backup of the original file.
- For ease of understanding, copy the corrupted pages from the infected file and paste them in the text comparison tool.
- Use a data comparison tool to compare the corrupted page with the latest version of the original page. You will be able to see a side-by-side comparison of the data with the anomalies highlighted.
- Fix the pages and run the DBCC CHECKDB command on your restored file.
If you have restored and fixed the corrupted data correctly, there should be no anomalies in the recovered file.
Drawbacks of this Method
- If more than one location or a large area is corrupted, other types of pages may be affected which will cause SQL Server to be unable to open the file.
- Direct editing of the file is a method that is fault-prone. Checksums are calculated on a page-by-page basis and if you fail to copy/paste correctly, I/O errors will occur or you may be unable to open the database.
- This solution will need time and you?ll need to practice it beforehand for it to be successful.
A Better Approach
To overcome the aforementioned drawbacks, many DBAs prefer using a third-party SQL repair tool. One of the most widely used tools for the purpose is Stellar Repair for MS SQL. Using advanced algorithms, this tool has proven to successfully fix all corruptions of SQL Server database and recover inaccessible objects from MDF and NDF database files. Users have reported that the software carries out the highest level of non-destructive repair algorithm to preserve database integrity. If you?re looking for a reliable and quick solution, this is your best bet.
Read: How to use Stellar Repair for MS SQL Software?
You can also check the reviews of the Stellar Repair for MS SQL software done by Microsoft MVPs.
Last Few Lines
Hope our tips can help you to resolve the issue. If you?re still stuck, contact Microsoft customer support for further help.
