Guide on How to Repair SQL Server Page Level Corruption

admin | October 11th, 2018 | SQL Server

It is quite obvious that if an individual is a SQL server administrator then, his / her life is not going to be bug-free. There are many situations where administrators face several challenges while working on the server. One such nightmare condition is SQL Server page level corruption. This situation is equivalent to a scary dream because this corruption in the sever jeopardizes all essential data stored on the server. When corruption occurs, the data goes into an inaccessible state and leads to the obstruction of ongoing workflow.

At any instant, a situation might occur where users find corruption in the SQL database. Well, the most important part is detection of this corruption at its early stage and fixing it immediately. So, here we are going to discuss causes and measures on how to repair SQL Server database page level corruption.

SQL Server Page Level Corruption

Causes of SQL Server Page Level Corruption

The basic storage unit in the SQL server database is page where all the information is saved in form of pages. Users might be knowing that there are 2 types of the SQL server database files i.e., MDF and NDF. Basically, LDF and MDF files are separated into bundled of pages and every single page is having its own server location. Whenever the SQL server corruption occurs, each page starts getting tainted. Instead of dealing with entire file at once, it is easy to solve the problem manually by fixing pages one-by-one.

Now a question arises how this problem occurs? Well, there is not one answer to this question but, many. Below-listed is all those points that cause need to repair SQL Server page level corruption:

  1. Hardware malfunction can be the cause of problem. One should regularly monitor the server box, hard disk, and other PC components for avoiding this issue.
  2. Verify that the server power supply is regulated. When database is in running state and suddenly power supply issue occurs, at this instant all this leads to SQL Server database page level corruption.
  3. Whenever you are downloading a new software, consciously check that setup file is not infected with any kind of virus or malware.
  4. Installation of irrelevant updates or applications leads to corruption in the SQL database page level.
  5. There are few chances of a situation where the server itself will be unable to diagnose itself. Unfortunately, there is nothing to do with this still it is advisable to administrators that they should periodically check database functionality.

Resolving SQL Server Database Page Level Corruption

Before starting with anything, take backup of the server database files. This will be keeping you safe if in case something went wrong. Also, download products i.e., Text and data comparison tool from the internet market. These will be helping users from comparing contents between corrupted and original data. Now, follow below-mentioned instructions to repair SQL Server page level corruption:

1. Open text comparison software for checking the differences in between original and the corrupted file

2. After encountering the corrupted file, execute DBCC CHECKDB command. This will be helping in checking the database file with location where problem occurs and utilize minimum sources for data repairing

1

3. Execute DBCC PAGE command for examining infected page contents. Firstly, switch on the trace flag 3604

  • DBCC TRACEON (3604)
  • DBCC PAGE ({‘dbname’ | dbid}, filenum, pagenum [,printopt={0|1|2|3}])

2

The term ‘filenum’ and ‘pagenum’ are associated with page ids and various machine tables. The parameters of printopt parameters are explained below:

  • 0 – It will be printing the header part of the page
  • 1 – Header of the SQL page with per row hex dumps
  • 2 – Page header detail with entire hex dump of the page
  • 3 – Header of the page with description of per row interpretation

4. Now execute ‘SELECT * from dbo.tablename’ command for confirming the page number and physical offset

3

5. Till now, you are aware of the location where corruption is committed. Utilize text comparison software for comparing corrupted file with the backed up file

6. For more ease, you can copy the corrupted server page with infected one and then, paste it in the software

7. Open data comparison product for comparing the corrupted SQL database page with updated edition of the original page. Here, it will be possible for you to perform data comparison with highlighted anomalies

8. Fix the corrupted page and then, execute the DBCC CHECKDB command on the restored file.

4

If each and every step is executed in a correct way then, there will be no anomalies in the regained file.

Is Manual Method Challenging?

  • If several paths or a huge area is damaged then, other pages might get infected. This will restrict the server from opening the file.
  • Making changes directly in the file is fault-prone. This may lead to errors related to input/output at the time of opening database.
  • Highly certified knowledge about the SQL server is needed to execute procedure without any complications.

A Better Approach to Resolve SQL Server Page Level Corruption

For overcoming all the aforementioned limitations, several administrators prefer utilizing third-party products. One of the popularly known product for the same is SQL Recovery Software. The application smoothly repair SQL Server page level corruption without execution of any commands. There is no highly qualified knowledge needed to work with this utility. The products restore inaccessible items from MDF as well as NDF files and provide option to save them. Here are the some features:

  • Quick & Advanced Scan Option To Repair Primary & Secondary SQL Database
  • Best Software Feature to Recover Deleted SQL Server Database Table’s Data
  • Option to Scan & Recover Multiple SQL Server Database NDF Files
  • Feature to Scan & Recover Triggers, Rules, Functions, Tables, Stored Procedures
  • Recover SQL Server 2017, 2016, 2014, 2012, 2008, 2008 R2, 2005, 2000 MDF Files

Last Few Statements

We hope that we resolved your query i.e., How to repair SQL Server page level corruption? Talking about a relevant approach then, it is dependent upon the knowledge of the server and the person who is resolving issue.