Remove SQL Server Page Level Corruption Easily
This article deals with techniques to assist users for solving the query about ‘How to recover corrupted SQL server pages’ in the event of SQL server page level corruption.
Reasons For Page Level Corruption in SQL Server
As we know the smallest unit of data storage in a SQL server database is a page. All the MDF and NDF files are logically separated into hundreds of pages each having a specific location on the server. If corruption occurs all these pages in the data files get infected.
Let us first know about the reasons for SQL Server page corruption:
- Hardware Malfunction: This is the most common reason for SQL server page-level corruption. If there are defects in the hard disk sectors, then it can most likely lead to the corruption of pages.
- Power Supply Issues: A regulated power supply is needed to ensure that no corruption issues occur while using the server database. This is also an important factor that leads to problems of page corruption.
- Virus Infection: Downloading and installing the wrong software or hidden malicious applications can lead to risk and eventually SQL Server page corruption.
- Incompatible patches: Installing patches is a good policy until you’re not trying to opt for the wrong patches. In this way, incompatible updates or patches can also be a major cause of SQL page corruption
- Server problems: At times there may be faults in the server itself can be leading to corruption in pages of MDF or NDF files. In such cases, an external factor does not contribute to the problem of page level corruption in SQL server.
Expert Tip: To get rid of the problem of corrupt pages in SQL server, use an efficient software solution such as SysTools SQL Recovery tool for effectively performing SQL server page level recovery.
How to Resolve SQL Server Page Level Corruption Manually
In order to fix page level corruption in MS SQL Server using manual approach follow the below-mentioned steps:
- Make use of a comparison tool for checking the differences between the corrupted file and original file
- Run DBCC CHECKDB command on the corrupted file. It will check the database file, show the location of the problematic areas, and will provide minimum requirements for repair of database
- For checking the contents of the page which is infected, run the DBCC PAGE command
Each of these steps given above contain several sub-steps as well which need to be executed carefully by the user.
Disadvantages of the Manual Method & Why You Should Avoid It!
There are a number of problems associated with manual technique, some of these are discussed briefly in the following section:
- The manual approach requires a lot of time to implement due to a large number of commands to eliminate SQL Server page corruption.
- It is highly complex due to a number of detailed procedures and commands which are unsuitable for non-technical users.
- Editing files that are corrupted in a direct way can cause further errors because of mistakes. Checksums get calculated on the basis of individual pages and I/O can occur leading to inability in opening the database.
- If a large area constituting several pages has been corrupted, then using this method may cause SQL server to become unable to open other healthy files.
Page level corruption can be a major source of worry for most DBAs and there are various reasons that contribute to cause this problem. A manual approach can be useful in order to SQL Server page level corruption but has several shortcomings of its own. Ultimately, the wise decision would be to make use of a third party, automated tool for this purpose.